PDO exception when loading test fixtures and using Mysql init commands #8017

ivokund · 2015-04-08T08:32:33Z

I have run into a strange issue. When I add more than one PDO::MYSQL_ATTR_INIT_COMMAND commands in my configuration, like this:

'db' => [
    'class' => 'yii\db\Connection',
    'charset' => 'utf8',
    'attributes' => [
        PDO::MYSQL_ATTR_INIT_COMMAND => '
            SET SESSION sql_mode = \'STRICT_TRANS_TABLES\';
            SET SESSION time_zone = \'+0:00\';
        '
    ]
],

Then I get the following error when running codeception functional tests that try to load global fixtures.

:~/clone/tests/codeception/frontend$ codecept run --debug


  [PDOException]
  SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consid
  er using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you m
  ay enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.


Exception trace:
 () at [path]/vendor/yiisoft/yii2/db/Connection.php:597
 PDO->exec() at [path]/vendor/yiisoft/yii2/db/Connection.php:597
 yii\db\Connection->initConnection() at [path]/vendor/yiisoft/yii2/db/Connection.php:530
 yii\db\Connection->open() at [path]/vendor/yiisoft/yii2/db/Connection.php:837
 yii\db\Connection->getMasterPdo() at [path]/vendor/yiisoft/yii2/db/Connection.php:824
 yii\db\Connection->getSlavePdo() at [path]/vendor/yiisoft/yii2/db/Command.php:208
 yii\db\Command->prepare() at [path]/vendor/yiisoft/yii2/db/Command.php:816
 yii\db\Command->queryInternal() at [path]/vendor/yiisoft/yii2/db/Command.php:350
 yii\db\Command->queryAll() at [path]/vendor/yiisoft/yii2/db/mysql/Schema.php:198
 yii\db\mysql\Schema->findColumns() at [path]/vendor/yiisoft/yii2/db/mysql/Schema.php:97
 yii\db\mysql\Schema->loadTableSchema() at [path]/vendor/yiisoft/yii2/db/Schema.php:140
 yii\db\Schema->getTableSchema() at [path]/vendor/yiisoft/yii2/test/ActiveFixture.php:146
 yii\test\ActiveFixture->getTableSchema() at [path]/vendor/yiisoft/yii2/test/ActiveFixture.php:121
 yii\test\ActiveFixture->resetTable() at [path]/vendor/yiisoft/yii2/test/ActiveFixture.php:76
 yii\test\ActiveFixture->load() at [path]/vendor/yiisoft/yii2/test/FixtureTrait.php:93
 tests\codeception\common\_support\FixtureHelper->loadFixtures() at [path]/tests/codeception/common/_support/FixtureHelper.php:72
 tests\codeception\common\_support\FixtureHelper->_beforeSuite() at /home/rof/.composer/vendor/codeception/codeception/src/Codeception/Subscriber/Module.php:32
 Codeception\Subscriber\Module->beforeSuite() at n/a:n/a
 call_user_func() at /home/rof/.composer/vendor/symfony/event-dispatcher/Symfony/Component/EventDispatcher/EventDispatcher.php:164
 Symfony\Component\EventDispatcher\EventDispatcher->doDispatch() at /home/rof/.composer/vendor/symfony/event-dispatcher/Symfony/Component/EventDispatcher/EventDispatcher.php:53
 Symfony\Component\EventDispatcher\EventDispatcher->dispatch() at /home/rof/.composer/vendor/codeception/codeception/src/Codeception/SuiteManager.php:152
 Codeception\SuiteManager->run() at /home/rof/.composer/vendor/codeception/codeception/src/Codeception/Codecept.php:166
 Codeception\Codecept->runSuite() at /home/rof/.composer/vendor/codeception/codeception/src/Codeception/Codecept.php:149
 Codeception\Codecept->run() at /home/rof/.composer/vendor/codeception/codeception/src/Codeception/Command/Run.php:262
 Codeception\Command\Run->runSuites() at /home/rof/.composer/vendor/codeception/codeception/src/Codeception/Command/Run.php:191
 Codeception\Command\Run->execute() at /home/rof/.composer/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:257
 Symfony\Component\Console\Command\Command->run() at /home/rof/.composer/vendor/symfony/console/Symfony/Component/Console/Application.php:874
 Symfony\Component\Console\Application->doRunCommand() at /home/rof/.composer/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /home/rof/.composer/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /home/rof/.composer/vendor/codeception/codeception/codecept:27

Any idea what could be using the problem? The issue only happens on Codeship CI server and with any two commands in the configuration. When I remove any one, the issue dissapears.

Setting PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to true did not help. Also setting emulatePrepare to true did not help.

Yii 2.0.3
PDO::ATTR_CLIENT_VERSION: mysqlnd 5.0.11-dev - 20120503
PDO::ATTR_DRIVER_NAME: mysql
PDO::ATTR_SERVER_VERSION: 5.6.19-0ubuntu0.14.04.1

Thanks!

The text was updated successfully, but these errors were encountered:

cebe · 2015-04-08T08:39:16Z

Not sure if there is a way to solve this. Sounds like it is a problem with PDO rather than Yii.
You could use initConnection with EVENT_AFTER_OPEN event to do the same. http://www.yiiframework.com/doc-2.0/yii-db-connection.html#initConnection%28%29-detail

nineinchnick · 2015-04-08T08:40:21Z

You could run those extra queries another way:

'db' => [
    'class' => 'yii\db\Connection',
    'charset' => 'utf8',
    'on '.\yii\db\Connection::EVENT_AFTER_OPEN => function () {
        Yii::$app->db->createCommand('SET SESSION sql_mode = \'STRICT_TRANS_TABLES\'')->execute();
        Yii::$app->db->createCommand('SET SESSION time_zone = \'+0:00\'')->execute();
    },
],

nineinchnick · 2015-04-08T08:44:20Z

After a quick google I found a related SO question. Maybe you can't execute more than one query this way, try setting both values in a single SET SESSION call or use Yii events.

ivokund · 2015-04-08T08:49:03Z

Yes, you are correct, this is due to https://bugs.php.net/bug.php?id=48859
The following seems to work:

        'db' => [
            'class' => 'yii\db\Connection',
            'charset' => 'utf8',
            'attributes' => [
                PDO::MYSQL_ATTR_INIT_COMMAND => '
                    SET SESSION sql_mode = \'STRICT_TRANS_TABLES\', time_zone = \'+0:00\';
                '
            ]
        ],

Thanks for your help and sorry for the confusion.

nineinchnick · 2015-04-08T08:54:59Z

@cebe maybe it's worth adding example usage for EVENT_AFTER_OPEN somewhere in the docs. I find it quite common and it's hard to figure out for beginners.

ivokund · 2015-04-08T08:57:53Z

Isn't SET with multiple arguments the correct way for doing this?

nineinchnick · 2015-04-08T09:01:37Z

Both solutions are be correct but using PDO::MYSQL_ATTR_INIT_COMMAND is specific to MySQL.

klimov-paul · 2015-04-09T14:12:54Z

Relates #3695

DoanLich · 2015-04-21T06:40:29Z

Hi

DoanLich · 2015-04-21T06:42:03Z

hii

peerdevravi · 2015-04-24T06:23:10Z

Hello, I am facing the same problem.. any update on this??

cebe · 2015-04-24T08:44:34Z

I have added an example to the guide: a1066c5

peerdevravi · 2015-04-24T08:55:11Z

Is there any way to import database(sql file) using query ??

peerdevravi · 2015-04-24T08:56:15Z

I am getting following error while importing..
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.

cebe · 2015-04-24T08:57:06Z

see here: #3695 (comment)

peerdevravi · 2015-04-24T09:00:50Z

when I tried
\Yii::$app->db->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);

it show me setAttribute is not defined.

cebe · 2015-04-24T09:03:53Z

you have to call \Yii::$app->db->open() before, to ensure pdo instance is created.

peerdevravi · 2015-04-24T09:08:38Z

Ok.. I called it. Now it give PDO class not found..

cebe · 2015-04-24T09:13:36Z

most probably because you are in a namespace, add a backslash before it: \PDO

peerdevravi · 2015-04-24T09:17:52Z

Yes, it resolved PDO class error.. Thanks.
There is some strange issue I found in Active Record Query. Can I write here or generate new issue ??

samdark · 2015-04-24T09:42:01Z

One issue per one problem.

cebe added this to the 2.0.x milestone Apr 8, 2015

cebe added the status:to be verified Needs to be reproduced and validated. label Apr 8, 2015

cebe added type:docs Documentation and removed status:to be verified Needs to be reproduced and validated. labels Apr 11, 2015

cebe closed this as completed in a1066c5 Apr 24, 2015

cebe modified the milestones: 2.0.4, 2.0.x Apr 24, 2015

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

PDO exception when loading test fixtures and using Mysql init commands #8017

PDO exception when loading test fixtures and using Mysql init commands #8017

ivokund commented Apr 8, 2015

cebe commented Apr 8, 2015

nineinchnick commented Apr 8, 2015

nineinchnick commented Apr 8, 2015

ivokund commented Apr 8, 2015

nineinchnick commented Apr 8, 2015

ivokund commented Apr 8, 2015

nineinchnick commented Apr 8, 2015

klimov-paul commented Apr 9, 2015

DoanLich commented Apr 21, 2015

DoanLich commented Apr 21, 2015

peerdevravi commented Apr 24, 2015

cebe commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

cebe commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

cebe commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

cebe commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

samdark commented Apr 24, 2015

PDO exception when loading test fixtures and using Mysql init commands #8017

PDO exception when loading test fixtures and using Mysql init commands #8017

Comments

ivokund commented Apr 8, 2015

cebe commented Apr 8, 2015

nineinchnick commented Apr 8, 2015

nineinchnick commented Apr 8, 2015

ivokund commented Apr 8, 2015

nineinchnick commented Apr 8, 2015

ivokund commented Apr 8, 2015

nineinchnick commented Apr 8, 2015

klimov-paul commented Apr 9, 2015

DoanLich commented Apr 21, 2015

DoanLich commented Apr 21, 2015

peerdevravi commented Apr 24, 2015

cebe commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

cebe commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

cebe commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

cebe commented Apr 24, 2015

peerdevravi commented Apr 24, 2015

samdark commented Apr 24, 2015