DDC-453: PDOException "SQLSTATE[HY000]: General error: 2014" when trying to prepare statement while other unbuffered queries are still active

[doctrinebot]

Jira issue originally created by user markus.woessner:

Doing a fetch join query to Entity "User" with bidirectional one-to-one association to Entity "Address", where User is owning side produces error.

Query: "SELECT u,a FROM Entities\User u JOIN u.address a"

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/DBAL/Connection.php:549

Stack trace:
#0 /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/DBAL/Connection.php(549): PDO->prepare('SELECT users.id...')
#1 /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/ORM/Persisters/StandardEntityPersister.php(438): Doctrine\DBAL\Connection->prepare('SELECT users.id...')
#2 /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/ORM/Mapping/OneToOneMapping.php(253): Doctrine\ORM\Persisters\StandardEntityPersister->load(Array, NULL, Object(Doctrine\ORM\Mapping\OneToOneMapping))
#3 /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/ORM/UnitOfWork.php(1811): Doctrine\ORM\Mapping\OneToOneMapping->load(Object(Entities\Address), NULL, Object(Doctrine\ORM\EntityManager))
#4 /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/ORM/Internal/Hydration/ObjectHydrator.php(179): Doctrine\ORM\UnitOfWork->createEntity('Entities\Addres...', Array, Array)
#5 /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/ORM/Internal/Hydration/ObjectHydrator.php(342): Doctrine\ORM\Internal\Hydration\ObjectHydrator->_getEntity(Array, 'a')
#6 /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/ORM/Internal/Hydration/ObjectHydrator.php(112): Doctrine\ORM\Internal\Hydration\ObjectHydrator->_hydrateRow(Array, Array, Array)
#7 /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/ORM/Internal/Hydration/AbstractHydrator.php(102): Doctrine\ORM\Internal\Hydration\ObjectHydrator->_hydrateAll()
#8 /usr/lib/php5.3/lib/php/Doctrine_2_checkout/lib/Doctrine/ORM/AbstractQuery.php(520): Doctrine\ORM\Internal\Hydration\AbstractHydrator->hydrateAll(Object(Doctrine\DBAL\Driver\PDOStatement), Object(Doctrine\ORM\Query\ResultSetMapping), Array)
#9 /var/www/adition/doctrine2_sandbox/debug/pdo2014.php(10): Doctrine\ORM\AbstractQuery->execute()
#10 {main}

While second advice of above PDO exception message seems to be limited to MySQL environments following patch might do it:

--- ORM/Internal/Hydration/ObjectHydrator.php   (Revision 7409)
<ins></ins><ins> ORM/Internal/Hydration/ObjectHydrator.php   (Arbeitskopie)
@@ -108,8 </ins>108,10 @@
     {
         $result = array();
         $cache = array();
-        while ($data = $this->*stmt->fetch(\Doctrine\DBAL\Connection::FETCH*ASSOC)) {
-            $this->_hydrateRow($data, $cache, $result);
<ins>        $rows = $this->*stmt->fetchAll(\Doctrine\DBAL\Connection::FETCH*ASSOC);
</ins>        $this->_stmt->closeCursor();
<ins>        foreach($rows as $row) {
</ins>            $this->_hydrateRow($row, $cache, $result);
         }

         // Take snapshots from all newly initialized collections

With this patch applied to my checkout I was able to retrieve correct User entity and Address entity in NON-proxy mode.

[doctrinebot]

Comment created by markus.woessner:

I wasn't able to run tests with applied patch yet. It's late and I will try to do it tomorrow

[doctrinebot]

Comment created by @beberlei:

@roman: I guess this could probably be caused by an instant proxy load during hydration?

[doctrinebot]

Comment created by romanb:

Not sure since our test suite runs fine against MySQL and there are plenty functional tests. -Moreover, there have been bugs in PDO drivers frequently.- OK, your versions seem quite up-to-date. We certainly need a test case that fails for everyone before doing anything.

[doctrinebot]

Comment created by romanb:

@benjamin: Thats quite possible even though I would be surprised if we have not a single test yet that causes eager loading. If this is the case it should be easy to reproduce. Just create a bidirectional one-one and then query only for the inverse side. Since the association from inverse to owning side in a to-one association can not be lazy (because it doesnt have the FK so it cant use proxying) this error should be triggered.

[doctrinebot]

Comment created by romanb:

My first tries to reproduce this have been unsuccessful. Mind you I'm using the mysqlnd client library, not libmysql, dont know whether that makes a difference here.

[doctrinebot]

Comment created by markus.woessner:

I tried to reproduce this failure today and wasn't able. Apparently I missed something yesterday. I'm sorry to have troubled you. Yet I keep wondering what was the mistake. Yesterday MySQL log looked as follows for mentioned query when I set "PDO::MYSQL_ATTR_USE_BUFFERED_QUERY" to "true":

100320 21:44:26   114 Connect   sandbox@localhost on sandbox
                  114 Prepare   SELECT u0*.id AS id0, u0_.name AS name1, a1_.id AS id2, a1_.street AS street3, u0_.address_id AS address*id4
  FROM users u0* INNER JOIN addresses a1_ ON u0_.address_id = a1_.id WHERE u0*.name = ?
                  114 Execute   SELECT u0*.id AS id0, u0_.name AS name1, a1_.id AS id2, a1_.street AS street3, u0_.address_id AS address*id4
  FROM users u0* INNER JOIN addresses a1_ ON u0_.address_id = a1_.id WHERE u0*.name = 'Nana'
                  114 Prepare   SELECT users.id, users.name, address*id FROM users WHERE address*id = ?
                  114 Execute   SELECT users.id, users.name, address*id FROM users WHERE address*id = '1'
                  114 Close stmt
                  114 Close stmt
                  114 Quit

Why would Doctrine issue another "users" SELECT within hydration loop?

Anyway, I recommend closing this issue!

[doctrinebot]

Comment created by romanb:

OK. As soon as you encounter this problem again and you have a reproducable test case, feel free to reopen this issue.

Thanks for your help.

[doctrinebot]

Comment created by romanb:

User reported that this issue reoccured, however, we're still waiting for someone who can provide a reproducable unit test. I could not get the error to occur.

[doctrinebot]

Comment created by romanb:

The reason I don't get that error is because pdo_mysql defaults to using buffered queries, and that since 2006 it seems ( http://svn.php.net/viewvc?view=revision&revision=224291 ).

Maybe the distribution you use has a customized/patched PHP/PDO version that changes this default?

[doctrinebot]

Comment created by markus.woessner:

I use PDO_MYSQL 1.0.2. Sorry for delayed response. Didn't realize that "pecl list" will reveal version information.

[doctrinebot]

Comment created by romanb:

I think all we can do here is to document that Doctrine requires buffered queries (which is the default in PDO normally).

[doctrinebot]

Comment created by @beberlei:

Configuration error, closing.

[doctrinebot]

Issue was closed with resolution "Invalid"

[doctrinebot]

Imported 1 attachments from Jira into https://gist.github.com/9e25dd073dddbe09639a

• 10510_patch.txt