Batch query test

[cebe]

This adds a test for batch() and each() query with large table as some issues have been reported in the past. The default table will be created with 1000 records to not stress travis too much. You can try it locally by setting the table size to 100 000 or even 10 000 000.

I was not able to reproduce the problem with this test on my machine, maybe someone else can run it and see what happens.

Q	A
Is bugfix?	no
New feature?	no
Breaks BC?	no
Tests pass?	lets wait for travis ;)
Related issues	#8420, #9269

/cc @particleflux can you try this test on your machine?

[cebe]

here is my environment:

$ dpkg -l |grep mysql
ii  libmysqlclient18:amd64                 5.5.47-0+deb8u1                      amd64        MySQL database client library
ii  mysql-client-5.5                       5.5.47-0+deb8u1                      amd64        MySQL database client binaries
ii  mysql-common                           5.5.47-0+deb8u1                      all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                           5.5.47-0+deb8u1                      all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.5                       5.5.47-0+deb8u1                      amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.5                  5.5.47-0+deb8u1                      amd64        MySQL database server binaries
ii  php5-mysql                             5.6.17+dfsg-0+deb8u1                 amd64        MySQL module for php5


$ php --version
PHP 5.6.17-0+deb8u1 (cli) (built: Jan 13 2016 09:10:12) 
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2015, by Zend Technologies
    with Xdebug v2.2.5, Copyright (c) 2002-2014, by Derick Rethans

[particleflux]

with size = 100 000:

$ phpunit --debug tests/framework/db/BatchQueryResultTest.php
PHPUnit 4.8.24-1-g1a1b632 by Sebastian Bergmann and contributors.


Starting test 'yiiunit\framework\db\BatchQueryResultTest::testQuery'.
.
Starting test 'yiiunit\framework\db\BatchQueryResultTest::testActiveQuery'.
.
Starting test 'yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #0 (array(true))'.

Starting test 'yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #1 (array(false))'.


Time: 9.01 minutes, Memory: 61.00Mb

There was 1 failure:

1) yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #0 (array(true))
Failed asserting that 55286712 is less than 36538512.

/home/stefan/yii2/tests/framework/db/BatchQueryResultTest.php:191
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:147
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:99
/home/stefan/.composer/vendor/phpunit/phpunit/phpunit:36

FAILURES!
Tests: 4, Assertions: 206039, Failures: 1.

env (mysql running remote over VPN)

# dpkg -l | grep mysql
ii  libdbd-mysql-perl                  4.021-1+b1                    amd64        Perl5 database interface to the MySQL database
ii  libmysqlclient18:amd64             5.5.44-0+deb7u1               amd64        MySQL database client library
ii  mysql-client-5.5                   5.5.44-0+deb7u1               amd64        MySQL database client binaries
ii  mysql-common                       5.5.44-0+deb7u1               all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                       5.5.44-0+deb7u1               all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.5                   5.5.44-0+deb7u1               amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.5              5.5.44-0+deb7u1               amd64        MySQL database server binaries
ii  php5-mysqlnd                       5.4.45-0+deb7u1               amd64        MySQL module for php5 (Native Driver)


$ php --version
PHP 5.6.18-pl0-gentoo (cli) (built: Mar 12 2016 21:12:13) 
Copyright (c) 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies
    with Xdebug v2.2.6, Copyright (c) 2002-2014, by Derick Rethans

local libmysqlclient is also 18

[particleflux]

With 1M it gets worse:

Time: 3.93 hours, Memory: 289.50Mb

There was 1 failure:

1) yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #0 (array(true))
Failed asserting that 303197336 is less than 36536400.

EDIT: 1M through local Mysql 5.6 is even worse:

Running each() query... (Memory: 16298160)[==>                                 ] 5% (57700/1000000) ETA: 10243 sec. 

Time: 17.6 minutes, Memory: 289.50Mb

There was 1 error:

1) yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #1 (array(false))
Empty row packet body

/home/stefan/yii2/framework/db/DataReader.php:111
/home/stefan/yii2/framework/db/BatchQueryResult.php:143
/home/stefan/yii2/framework/db/BatchQueryResult.php:112
/home/stefan/yii2/tests/framework/db/BatchQueryResultTest.php:197
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:147
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:99
/home/stefan/.composer/vendor/phpunit/phpunit/phpunit:36

--

There was 1 failure:

1) yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #0 (array(true))
Failed asserting that 303195152 is less than 36536312.

/home/stefan/yii2/tests/framework/db/BatchQueryResultTest.php:191
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:147
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:99
/home/stefan/.composer/vendor/phpunit/phpunit/phpunit:36

FAILURES!
Tests: 4, Assertions: 175438, Errors: 1, Failures: 1.

[particleflux]

Another machine with:

PHP (no xdebug this time) PHP 7.0.4-pl0-gentoo (cli) (built: Mar 8 2016 13:57:47) ( NTS ) Copyright (c) 1997-2016 The PHP Group Zend Engine v3.0.0, Copyright (c) 1998-2016 Zend Technologies with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies

MySql 5.6.29 (localhost), mysqlclient 18

Output:

Running each() query... (Memory: 8532704)[>           ] 0% (64100/10000000) ETA: 537077 sec.

Time: 4.62 hours, Memory: 4393.24Mb

There was 1 error:

1) yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #1 (array(false))
Empty row packet body

/home/stefan/yii2/framework/db/DataReader.php:111
/home/stefan/yii2/framework/db/BatchQueryResult.php:143
/home/stefan/yii2/framework/db/BatchQueryResult.php:112
/home/stefan/yii2/tests/framework/db/BatchQueryResultTest.php:197
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:147
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:99
/home/stefan/.composer/vendor/phpunit/phpunit/phpunit:36

--

There was 1 failure:

1) yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #0 (array(true))
Failed asserting that 4576933128 is less than 31554568.

/home/stefan/yii2/tests/framework/db/BatchQueryResultTest.php:191
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:147
/home/stefan/.composer/vendor/phpunit/phpunit/src/TextUI/Command.php:99
/home/stefan/.composer/vendor/phpunit/phpunit/phpunit:36

FAILURES!
Tests: 4, Assertions: 728238, Errors: 1, Failures: 1.

EDIT: Adding a pure Debian machine (local mysql) to the list of failing ones:

 # dpkg -l | grep mysql
ii  libdbd-mysql-perl                  4.021-1+b1                     amd64        Perl5 database interface to the MySQL database
ii  libmysqlclient18:amd64             5.5.47-0+deb7u1                amd64        MySQL database client library
ii  mysql-client                       5.5.47-0+deb7u1                all          MySQL database client (metapackage depending on the latest version)
ii  mysql-client-5.5                   5.5.47-0+deb7u1                amd64        MySQL database client binaries
ii  mysql-common                       5.5.47-0+deb7u1                all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                       5.5.47-0+deb7u1                all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.5                   5.5.47-0+deb7u1                amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.5              5.5.47-0+deb7u1                amd64        MySQL database server binaries
ii  php5-mysql                         5.4.45-0+deb7u2                amd64        MySQL module for php5


 # php --version
PHP 5.4.45-0+deb7u2 (cli) (built: Oct 17 2015 08:26:31) 
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies

Test results:

 ~/yii2 # vendor/bin/phpunit tests/framework/db/BatchQueryResultTest.php 
PHPUnit 4.8.24-1-g1a1b632 by Sebastian Bergmann and contributors.



Time: 20.99 hours, Memory: 121.75Mb

There was 1 failure:

1) yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #0 (array(1))
Failed asserting that 64055168 is less than 33692736.

/root/yii2/tests/framework/db/BatchQueryResultTest.php:191
/root/yii2/vendor/bin/phpunit:47

FAILURES!
Tests: 4, Assertions: 20600039, Failures: 1.

[lav45]

// tests/framework/db/BatchQueryResultTest.php
    protected static $largeTableCount = 100000;
    protected static $largeTableInsertBatch = 100000;

~ $ dpkg -l | grep -E 'mysql|percona'
ii  libmysqlclient18:amd64                      5.6.29-1~dotdeb+7.1                                 amd64        MySQL database client library
ii  libperconaserverclient18.1                  5.6.29-76.2-1.trusty                                amd64        Percona Server database client library
ii  mysql-common                                5.6.29-1~dotdeb+7.1                                 all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  percona-server-client-5.6                   5.6.29-76.2-1.trusty                                amd64        Percona Server database client binaries
ii  percona-server-common-5.6                   5.6.29-76.2-1.trusty                                amd64        Percona Server database common files (e.g. /etc/mysql/my.cnf)
ii  percona-server-server-5.6                   5.6.29-76.2-1.trusty                                amd64        Percona Server database server binaries
ii  php5-mysql                                  5.5.33-1~dotdeb+7.1                                 amd64        MySQL module for php5
~ $ php -v
PHP 5.5.33-1~dotdeb+7.1 (cli) (built: Mar  3 2016 17:11:35) 
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2015 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2015, by Zend Technologies
    with Xdebug v2.3.3, Copyright (c) 2002-2015, by Derick Rethans
 ~ $ mysql --version 
mysql  Ver 14.14 Distrib 5.6.29-76.2, for debian-linux-gnu (x86_64) using readline 6.3

Test:

~/www/yii2 $ phpunit tests/framework/db/BatchQueryResultTest.php
PHPUnit 4.8-g0fe5ed3 by Sebastian Bergmann and contributors.



Time: 8.43 minutes, Memory: 161.75Mb

There was 1 failure:

1) yiiunit\framework\db\BatchQueryResultTest::testBatchHugeTable with data set #0 (array(1))
Failed asserting that 142464504 is less than 34859560.

/home/GURTAM/loal/www/yii2/tests/framework/db/BatchQueryResultTest.php:191
/home/GURTAM/loal/www/yii2/vendor/phpunit/phpunit/phpunit:36

FAILURES!
Tests: 4, Assertions: 136047, Failures: 1.

[mikehaertl]

@cebe I think you should also add a test for batch queries using ActiveRecord. I found, that they currently don't work at all (unless you have the schema already cached by accident). See https://github.com/yiisoft/yii2/issues/8420#issuecomment-295609656.