Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-864

Failure to insert FALSE into a bool column

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      I have experienced this problem with MySQL, I am not sure how it behaves with other platforms. Also, maybe this duplicates http://www.doctrine-project.org/jira/browse/DBAL-630 but since that issue is specifically about PostreSQL I am creating a separate one.

      [Doctrine\DBAL\Exception\DriverException]
      An exception occurred while executing 'INSERT INTO ACL_Authorization
      (role_id, securityIdentity_id, parentAuthorization_id, entity_class, entity_id, cascadable)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
      with params [2, 2, null, "Account\\Domain\\Account", 2, false]:
      
      SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'cascadable' at row 1
      

      I think it's related to https://bugs.php.net/bug.php?id=49255 (PDO fails to insert boolean FALSE to MySQL in prepared statement) which casts FALSE into an empty string.

        Activity

        Matthieu Napoli created issue -
        Hide
        Marco Pivetta added a comment -

        Matthieu Napoli this issue could need some additional environment information. Also, isn't there a parameter count mismatch?

        Show
        Marco Pivetta added a comment - Matthieu Napoli this issue could need some additional environment information. Also, isn't there a parameter count mismatch?
        Hide
        Steve Müller added a comment -

        Yeah the VALUES clause looks weird concerning number of parameters. Could you maybe also provide information of how you constructed the query? DBAL? ORM? A little code snipüpet would help...

        Show
        Steve Müller added a comment - Yeah the VALUES clause looks weird concerning number of parameters. Could you maybe also provide information of how you constructed the query? DBAL? ORM? A little code snipüpet would help...
        Hide
        Matthieu Napoli added a comment -

        I removed useless values to clear up the message, don't mind the excessive "?" in "VALUES".

        Here is the code that trigger this: https://github.com/myclabs/ACL/blob/master/src/Repository/AuthorizationRepository.php#L62

        More explicitly, this is: $connection->insert($tableName, $data) with $data being a simple array.

        We are talking about DBAL (else I would have opened the issue in the ORM project), probably master (my constraint is master of ORM).

        Regarding the environment, this is weird: I can't reproduce it on Ubuntu (PHP 5.5, MySQL version I don't know). The bug appears on OS X, PHP 5.5.5, MySQL 5.6.17 (just upgraded).

        Show
        Matthieu Napoli added a comment - I removed useless values to clear up the message, don't mind the excessive "?" in "VALUES". Here is the code that trigger this: https://github.com/myclabs/ACL/blob/master/src/Repository/AuthorizationRepository.php#L62 More explicitly, this is: $connection->insert($tableName, $data) with $data being a simple array. We are talking about DBAL (else I would have opened the issue in the ORM project), probably master (my constraint is master of ORM). Regarding the environment, this is weird: I can't reproduce it on Ubuntu (PHP 5.5, MySQL version I don't know). The bug appears on OS X, PHP 5.5.5, MySQL 5.6.17 (just upgraded).
        Hide
        Matthieu Napoli added a comment -

        I confirm that this is related to FALSE being casted to string, when I cast the boolean to an int it works. Example:

        $data = [
            // ...
            'cascadable' => (int) $authorization->isCascadable(),
        ];
        
        Show
        Matthieu Napoli added a comment - I confirm that this is related to FALSE being casted to string, when I cast the boolean to an int it works. Example: $data = [ // ... 'cascadable' => ( int ) $authorization->isCascadable(), ];
        Hide
        Matthieu Napoli added a comment -

        And to be extra-sure I tried casting to boolean, but I still get the error:

        $data = [
            // ...
            'cascadable' => (bool) $authorization->isCascadable(),
        ];
        
        Show
        Matthieu Napoli added a comment - And to be extra-sure I tried casting to boolean, but I still get the error: $data = [ // ... 'cascadable' => (bool) $authorization->isCascadable(), ];
        Hide
        Marco Pivetta added a comment -

        Matthieu Napoli is this due to a change in the ORM, an upgrade on your side or are were you implementing something in your codebase? I just wanted to be sure if this may be due to a breakage on your side or something you're experiencing on your code changes.

        Show
        Marco Pivetta added a comment - Matthieu Napoli is this due to a change in the ORM, an upgrade on your side or are were you implementing something in your codebase? I just wanted to be sure if this may be due to a breakage on your side or something you're experiencing on your code changes.
        Hide
        Matthieu Napoli added a comment -

        There is nothing "new" on my side except the code (I mean I didn't "upgrade" anything): this is a new project I started.

        Since I use embedded objects, I required doctrine/orm dev-master (or 2.5-BETA3 I don't know but it's roughly the same). Then I used DBAL to do a simple insert:

        $data = [
            ...
            'cascadable' => $authorization->isCascadable(),
        ];
        
        $connection->insert($tableName, $data);
        

        The tests for this "ACL" project are run using SQLite in memory, and they always pass (on every machine).

        When I use the project (as a dependency) in another one, with a MySQL backend, it works (i.e. no error) on my Ubuntu machine but not on my OS X machine.

        I will be trying to reproduce it in a test today, however I am on Ubuntu right now (work) so maybe I won't see it.

        (side note: I have no idea what's the deal between the Ubuntu and OS X machine, both have PHP 5.5 and a latest version of MySQL...)

        Show
        Matthieu Napoli added a comment - There is nothing "new" on my side except the code (I mean I didn't "upgrade" anything): this is a new project I started. Since I use embedded objects, I required doctrine/orm dev-master (or 2.5-BETA3 I don't know but it's roughly the same). Then I used DBAL to do a simple insert: $data = [ ... 'cascadable' => $authorization->isCascadable(), ]; $connection->insert($tableName, $data); The tests for this "ACL" project are run using SQLite in memory, and they always pass (on every machine). When I use the project (as a dependency) in another one, with a MySQL backend, it works (i.e. no error) on my Ubuntu machine but not on my OS X machine. I will be trying to reproduce it in a test today, however I am on Ubuntu right now (work) so maybe I won't see it. (side note: I have no idea what's the deal between the Ubuntu and OS X machine, both have PHP 5.5 and a latest version of MySQL...)
        Hide
        Matthieu Napoli added a comment -

        So as I feared, the test I wrote passed on my Ubuntu machine but fails on my Macbook.

        Here is the test: https://github.com/mnapoli/dbal/compare/DBAL-864 As you can see it's as simple as it can be.

        Exception : [Doctrine\DBAL\Exception\DriverException] An exception occurred while executing 'INSERT INTO dbal864tbl (foo) VALUES (?)' with params [false]:
        
        SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'foo' at row 1
        
        With queries:
        3. SQL: 'INSERT INTO dbal864tbl (foo) VALUES (?)' Params: ''
        2. SQL: 'CREATE TABLE dbal864tbl (foo TINYINT(1) NOT NULL) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB' Params:
        

        The test passes with SQLite however…

        Show
        Matthieu Napoli added a comment - So as I feared, the test I wrote passed on my Ubuntu machine but fails on my Macbook. Here is the test: https://github.com/mnapoli/dbal/compare/DBAL-864 As you can see it's as simple as it can be. Exception : [Doctrine\DBAL\Exception\DriverException] An exception occurred while executing 'INSERT INTO dbal864tbl (foo) VALUES (?)' with params [ false ]: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'foo' at row 1 With queries: 3. SQL: 'INSERT INTO dbal864tbl (foo) VALUES (?)' Params: '' 2. SQL: 'CREATE TABLE dbal864tbl (foo TINYINT(1) NOT NULL) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB' Params: The test passes with SQLite however…
        Hide
        Matthieu Napoli added a comment - - edited

        I am confused by the explanation given in https://bugs.php.net/bug.php?id=49255 but I tend to think it's related. When I run the test in debug and step by step, I confirm that the data passed to PDO is array(false). The casting of false to '' happens inside PDO.

        Edit: It's starting to make sense, have a look here: https://bugs.php.net/bug.php?id=33876

        The PDO documentation says that PDOStatement::execute says that "All values are treated as PDO::PARAM_STR" (http://php.net/manual/en/pdostatement.execute.php), whereas this should work:

        $res->bindValue(1, false, PDO_PARAM_BOOL);
        $res->execute();
        
        Show
        Matthieu Napoli added a comment - - edited I am confused by the explanation given in https://bugs.php.net/bug.php?id=49255 but I tend to think it's related. When I run the test in debug and step by step, I confirm that the data passed to PDO is array(false). The casting of false to '' happens inside PDO. Edit: It's starting to make sense, have a look here: https://bugs.php.net/bug.php?id=33876 The PDO documentation says that PDOStatement::execute says that "All values are treated as PDO::PARAM_STR" ( http://php.net/manual/en/pdostatement.execute.php ), whereas this should work: $res->bindValue(1, false , PDO_PARAM_BOOL); $res->execute();
        Hide
        Steve Müller added a comment -

        I think this is not a problem with DBAL but rather a usage problem (as stated in the PHP ticket). Please use the third $types argument for $connection->insert() and pass \PDO::PARAM_BOOL there or cast to integer as you did in your example.
        The Connection::insert() and related methods don't have enough context to know that the column you are inserting is of type boolean so you have to deal with it manually. This is why PDO has types...

        Show
        Steve Müller added a comment - I think this is not a problem with DBAL but rather a usage problem (as stated in the PHP ticket). Please use the third $types argument for $connection->insert() and pass \PDO::PARAM_BOOL there or cast to integer as you did in your example. The Connection::insert() and related methods don't have enough context to know that the column you are inserting is of type boolean so you have to deal with it manually. This is why PDO has types...
        Hide
        Matthieu Napoli added a comment -

        I understand your point, but a boolean is a boolean, DBAL can know what to do with it. It's an "abstraction layer", I would expect it to abstract this problem for me. That's the kind of added value I'm looking for in a DBAL.

        Show
        Matthieu Napoli added a comment - I understand your point, but a boolean is a boolean, DBAL can know what to do with it. It's an "abstraction layer", I would expect it to abstract this problem for me. That's the kind of added value I'm looking for in a DBAL.
        Hide
        Steve Müller added a comment - - edited

        I get what you mean but what you want is just to magic at that level I suppose. Connection::insert() is at a very low level and mainly just a wrapper around a prepared statement. How would you expect this method to find out the correct DBAL type? Checking each value's PHP type and evaluate the appropriate DBAL type? First off this would add a lot of performance overhead and does not ensure that the correct binding type is used in the end. Imagine that you can also have custom DBAL types with custom binding information and data conversion.
        Just do something like this:

        $connection->insert('some_table', array('some_column' => false), array('some_column' => 'boolean'));
        

        Basically in the third argument you define the DBAL type name mapping which converts the value appropriately for the underlying platform and chooses the correct PARAM binding type.

        In the end there is a reason why PDO doesn't have this kind of magic either and adding a type abstraction layer that is platform independant on top of it makes it even more difficult to do what you would expect.
        Hope this helps.

        Show
        Steve Müller added a comment - - edited I get what you mean but what you want is just to magic at that level I suppose. Connection::insert() is at a very low level and mainly just a wrapper around a prepared statement. How would you expect this method to find out the correct DBAL type? Checking each value's PHP type and evaluate the appropriate DBAL type? First off this would add a lot of performance overhead and does not ensure that the correct binding type is used in the end. Imagine that you can also have custom DBAL types with custom binding information and data conversion. Just do something like this: $connection->insert('some_table', array('some_column' => false ), array('some_column' => ' boolean ')); Basically in the third argument you define the DBAL type name mapping which converts the value appropriately for the underlying platform and chooses the correct PARAM binding type. In the end there is a reason why PDO doesn't have this kind of magic either and adding a type abstraction layer that is platform independant on top of it makes it even more difficult to do what you would expect. Hope this helps.
        Hide
        Matthieu Napoli added a comment -

        > Hope this helps.

        Yes it does, I still have mixed feelings about this but it makes sense (and I didn't think about custom types). Thanks.

        Show
        Matthieu Napoli added a comment - > Hope this helps. Yes it does, I still have mixed feelings about this but it makes sense (and I didn't think about custom types). Thanks.
        Hide
        Steve Müller added a comment -

        I share your opinion to some extent. But this task is not as trivial as it seems. Especially when it comes to provide an implementation that behaves the same on all vendors, platforms and versions.
        You might want to look at this: http://www.doctrine-project.org/jira/browse/DBAL-630 just to get an idea what a mess this is.

        Show
        Steve Müller added a comment - I share your opinion to some extent. But this task is not as trivial as it seems. Especially when it comes to provide an implementation that behaves the same on all vendors, platforms and versions. You might want to look at this: http://www.doctrine-project.org/jira/browse/DBAL-630 just to get an idea what a mess this is.
        Steve Müller made changes -
        Field Original Value New Value
        Assignee Benjamin Eberlei [ beberlei ] Steve Müller [ deeky666 ]
        Hide
        Steve Müller added a comment -

        Closing this issue for now.

        Show
        Steve Müller added a comment - Closing this issue for now.
        Steve Müller made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Invalid [ 6 ]
        Hide
        Doctrine Bot added a comment -

        A related Github Pull-Request [GH-564] was closed:
        https://github.com/doctrine/dbal/pull/564

        Show
        Doctrine Bot added a comment - A related Github Pull-Request [GH-564] was closed: https://github.com/doctrine/dbal/pull/564

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-864, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Steve Müller
            Reporter:
            Matthieu Napoli
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: