PHP mysql PDO sets 0 in a non-nullable column instead of raising exception when the input is null

Question

I am unable to generate an error or exception when updating a non-nullable field with a null value using PHP PDO for mysql. Doing straight sql gives the expected error.

Everything from PDO results in the value being set to 0 for the status_id field instead of exception or error indicating the field does not allow null.

$stmt_handler = $this->db_handler->prepare(
  "UPDATE faxes SET metadata = :metadata, status_id = :status_id, 
  created = :created, updated = :updated, content = :content, 
  vendor_fax_id = :vendor_fax_id WHERE id = :id");
$stmt_handler->bindParam(':id', $fax->id);
$stmt_handler->bindParam(':metadata', $fax->metadata);
$stmt_handler->bindParam(':status_id', $fax->status_id); // tried different combinations
$stmt_handler->bindParam(':created', $fax->created);
$stmt_handler->bindParam(':updated', $fax->updated);
$stmt_handler->bindParam(':content', $fax->content);
$stmt_handler->bindParam(':vendor_fax_id', $fax->vendor_fax_id);
$stmt_handler->execute();

I have tried different combinations and setting PDO::ATTR_EMULATE_PREPARES to false (as suggested at this question PHP mysql PDO refuses to set NULL value)

My original binding:

bindParam(':status_id', $fax->status_id);

tried with

bindValue(':status_id', null, PDO::PARAM_INT);
bindValue(':status_id', null, PDO::PARAM_NULL);
bindValue(':status_id', 'NULL', PDO::PARAM_INT);
bindValue(':status_id', 'NULL', PDO::PARAM_NULL);
bindValue(':status_id', null);
bindValue(':status_id', 'NULL');

PHP Version: PHP 5.3.10-1ubuntu3

MYSQL SERVER VERSION: 5.5.28-0ubuntu0.12.10.1

EDIT per comment

mysql> show columns from faxes;
+---------------+--------------+------+-----+-------------------+-----------------------------+
| Field         | Type         | Null | Key | Default           | Extra                       |
+---------------+--------------+------+-----+-------------------+-----------------------------+
| id            | char(36)     | NO   | PRI | NULL              |                             |
| vendor_fax_id | char(36)     | YES  | UNI | NULL              |                             |
| metadata      | varchar(255) | NO   |     | NULL              |                             |
| status_id     | int(10)      | NO   | MUL | NULL              |                             |
| created       | datetime     | NO   |     | NULL              |                             |
| updated       | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| content       | mediumblob   | NO   |     | NULL              |                             |
+---------------+--------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

Answer 1

This error on update will only be produced if you enable strict mode in mysql. In your .ini file, check for:

[mysqld]
..snipped..
sql_mode="some values here"

If it doesn't exist, add it, and make sure you have the strict setting applied, such as:

[mysqld]
..snipped..
sql_mode="STRICT_TRANS_TABLES"

Restart the server, and behold your new errors :)

I replicated your issue when this was absent, as soon as I added and restarted mysql, the error began appearing.

Mysql can still function when there is already a value in the non nullable field, so doesn't produce a hard error. Only strict mode will force it to throw one.

Answer 2

I tried this small example

$host = 'localhost';
$database = 'test';
$dbuser = 'USER';
$dbpasswd = '****';
$dsn = "mysql:host=$host;dbname=$database";
$pdo = new PDO($dsn, $dbuser, $dbpasswd);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sth = $pdo->prepare('insert into test values(?, ?)');
$id = 2;
$name = null;
$sth->bindParam(1, $id);
$sth->bindParam(2, $name);
$sth->execute() or die(implode(';', $sth->errorInfo()));

this test table

create table test (
id int not null,
name text not null);

and it gives me

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'name' cannot be null' in /tmp/a.php:15
Stack trace:
#0 /tmp/a.php(15): PDOStatement->execute()
#1 {main}
  thrown in /tmp/a.php on line 15

on the command line.

Update:

Changing the statement to

update test set name = ? where id = ?

with

$id = 1
$name = null

executes without error and sets name to the empty string.

Update 2:

I finally got it. This has nothing to do with PDO, but is MySQL specific.

See UPDATE Syntax, search down for "not null"

If you update a column that has been declared NOT NULL by setting to NULL, an error occurs if strict SQL mode is enabled; otherwise, the column is set to the implicit default value for the column data type and the warning count is incremented. The implicit default value is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. See Section 11.5, “Data Type Default Values”.

So, this behaviour is documented. If you want to get an error, you have to enable strict SQL mode.