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)

Are you 100% that the column hasn't got a default value of `0` applied to it? — BenOfTheNorth, Dec 21 '12 at 21:51
try this if it works `status_id = 1` in the update sql and see if it works — echo_Me, Dec 21 '12 at 21:56
Sounds like similar problem to [this question](http://stackoverflow.com/questions/13941568/debug-pdo-mysql-insert-null-into-database-instead-of-empty/13942285#13942285) from earlier in the week. — Michael Berkowski, Dec 21 '12 at 21:59
As opposed to doing the round trip to mySQL and back, why don't you check for null first, and then manually raise the exception. Doesn't directly answer the question, but is more efficient (as well as clearer to another user reading the code). — Robbie, Dec 21 '12 at 22:00
[This script](http://pastebin.com/idGMHmKN) with [this table](http://pastebin.com/kvTsB0M9) produces [this output](http://pastebin.com/1JXSYXVu) for me (it's working, in other words). PHP 5.4.9/win32, MySQL 5.5.25/win32 — DaveRandom, Dec 21 '12 at 22:22
@BenGriffiths please see the show columns from faxes I added. Thanks. — EricC, Dec 21 '12 at 23:04
@EricC Check my answer, didn't realise this was on update only — BenOfTheNorth, Dec 21 '12 at 23:42

score 2 · Accepted Answer · answered Dec 21 '12 at 23:39

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.

Olaf Dietsche · Answer 2 · 2012-12-22T00:01:07.060

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.

I have the insert statements working as well. Could you try an update statement on your example? Thanks. — EricC, Dec 21 '12 at 23:07
Thanks for your help. As @Ben Griffith mentioned, I get the expected error when I turn on strict mode. — EricC, Dec 22 '12 at 00:26

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

2 Answers2