Jira issue originally created by user mnapoli:
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](http://www.doctrine-project.org/jira/browse/DBAL-630) but since that issue is specifically about PostreSQL I am creating a separate one.
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.
Comment created by @ocramius:
[~mnapoli] this issue could need some additional environment information. Also, isn't there a parameter count mismatch?
Comment created by @deeky666:
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...
Comment created by mnapoli:
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).
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(),
And to be extra-sure I tried casting to boolean, but I still get the error:
$data = [
'cascadable' => (bool) $authorization->isCascadable(),
[~mnapoli] 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.
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(),
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...)
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](http://www.doctrine-project.org/jira/browse/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
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…
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);
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...
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.
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.
Hope this helps.
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.
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](http://www.doctrine-project.org/jira/browse/DBAL-630) just to get an idea what a mess this is.
Closing this issue for now.
Issue was closed with resolution "Invalid"
Comment created by @doctrinebot:
A related Github Pull-Request [GH-564] was closed: