subscribe

MySQL 5.6 BOOL behavior when using PDO and prepared statements

I recently updated my workstation to run MySQL 5.6.13. It didn’t take very long for things to start breaking, and since I couldn’t find any other information about this on the web, I figured this may be useful to someone else.

The main error that started popping up was:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'my_bool' at row 1' in test.php

This exception happens under the condition that you use PDO, prepared statements and booleans.

The easiest way to replicate this, is as follows:

<?php

$pdo = new PDO('mysql:host=localhost;dbname=test','root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec(<<<SQL
CREATE TEMPORARY TABLE foo (
   my_bool BOOLEAN
)
SQL
);

$stmt = $pdo->prepare('INSERT INTO foo (my_bool) VALUES (?)');
$stmt->execute([false]);

?>

What we are doing here, is inserting a new record in the foo table. The foo table has a field my_bool that’s a BOOLEAN. Note that this is also true for BOOL or TINYINT(1).

We use ->execute to supply the value for the bound parameter, but this should work just the same with bindValue.

The way prepared statements are implemented in PDO for MySQL, is that by default every value is cast to a string.

So at one point this will result in the following query:

INSERT INTO foo (my_bool) VALUES ('')

Which was valid for MySQL versions before 5.6. Now this throws an error.

There are two relatively easy solutions. You must either switch to integers (0 and 1), or use bindValue and explicitly supply a type.

<?php

// So either:
$stmt = $pdo->prepare('INSERT INTO foo (my_bool) VALUES (?)');
$stmt->execute([0]);

// Or:
$stmt = $pdo->prepare('INSERT INTO foo (my_bool) VALUES (?)');
$stmt->bindValue(1, false, PDO::PARAM_BOOL); // 1st param gets value false.
$stmt->execute();

?>

Now the second example may seem like quite a convoluted fix if you just want to insert a boolean, but if you have some kind of database abstraction layer, this may make more sense instead of converting every boolean to an integer first. Whatever floats your boat.

An aside on the LIMIT clause

Often the question comes up why it’s not possible to use bound parameters in conjuction with LIMIT, such as this:

<?php

$stmt = $pdo->prepare('SELECT from FOO LIMIT ?, ?');
$stmt->execute([15,5]);

?>

The cause for this is the same, by default PDO will treat every parameter as a string, resulting in this query:

SELECT from FOO LIMIT '15', '5'

Normally when you are inserting integers, or adding where clauses based on integers, surrounding these with quotes works without issue. This is not the case for the LIMIT clause, which is why this is happening.

This always works though:

<?php

$stmt = $pdo->prepare('SELECT from FOO LIMIT ?, ?');
$stmt->bindValue(1, 15, PDO::PARAM_INT); // 1st param gets value 15
$stmt->bindValue(2, 5, PDO::PARAM_INT); // 2nd param gets value 5
$stmt->execute();

?>

Why the choice was made in PDO to default every value to string beats me, I feel that it would be much more sensible to automatically map PHP types to MySQL types.

Update: STRICT_TRANS_TABLES

Morgan Tocker points out in the comments that this is due to a new default configuration value: STRICT_TRANS_TABLES.

Disabling this will get you the old behavior back. Dynom mentions that it may not be a bad idea regardless to keep this on, as well as a few other settings to let MySQL behave more strictly.

Web mentions

Comments

  • Peter Laursen •

    Sorry, but I do not understand.

    This works fine for me (in any client) with MySQL 5.6.14:
    CREATE TABLE `foo` (my_bool BOOLEAN);
    INSERT INTO `foo` (`my_bool`) VALUES ('1');

    Now, I realize there is no PHP/PDO and no prepared statement in this simple exampie. But it is not clear to me where 'the chain breaks'. Could you elaborate please?

    • Dynom •

      I think Evert uses the example of inserting an empty string "", not a "1".

  • Dynom •

    To help prevent errors like these, I prefer setting MySQL's sql_mode to be a bit more strict "sql_mode = STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY". I'm not sure if this would actually catch the boolean problem you're describing, but it does catch plenty other ambiguities cross MySQL versions or replacements (such as MariaDB, et all).

    I also had some troubles with PDO in the past, some of them had to do with to casting. Not limited to MySQL, but also MSSQL (http://blog.dynom.nl/archiv.... I've found that PDO is the best abstraction PHP has to offer, but it still lacks a punch here and there. Doctrine2 finishes the abstraction to a point where I've not found myself in a problem like this.

    Thanks for sharing.

  • emcomments •

    Small typo in last example?

    "// 1st param gets value 5" should be "// 2nd param gets value 5"

  • Morgan Tocker •

    MySQL 5.6 sets sql-mode="STRICT_TRANS_TABLES" in the configuration file of new installs, which is why you are seeing an error with this statement:

    INSERT INTO foo (my_bool) VALUES ('')

    You can make it work again in 5.6 by removing the sql-mode from configuration. The motivation for doing this was described on James Day's blog:

    https://blogs.oracle.com/su...

    • Evert

      Evert •

      That.. makes more sense :) Updated the post

  • Vladimir Nikolic •

    how to handle inserting a new record where auto increment primary key is supplied as empty string?

  • Ali •

    I have same problem, but with a simple select just like this: SELECT * FROM tblname , Do you know what is the problem?

    • Evert

      Evert •

      No idea. stack overflow may help with this. They'll want more detail than that though.

  • Red •

    Thanks! This helped me a lot!