Yii2: MySQL 5.7 compatibility

[SilverFire]

https://github.com/yiisoft/yii2/blob/f9fd4da8de6c0272cd8956e94da1656b41f1416c/framework/db/mysql/QueryBuilder.php#L41

What steps will reproduce the problem?

Run tests on MySQL 5.7

What is the expected result?

Passed tests

What do you get instead?

1) yiiunit\framework\db\mysql\QueryBuilderTest::testCreateTableColumnTypes
yii\db\Exception: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'col59'
The SQL being executed was: CREATE TABLE `column_type_table` (
...
    `col59` timestamp,
...
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'col59'

Additional info

https://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field

@sergeymakinen

[SilverFire]

We can set ALLOW_INVALID_DATES for MySQL in test environment.
Developers will resolve this issue on their own.

[rob006]

I would rather replace these tests and use ->timestamp()->null() instead just ->timestamp(), because this is the way how people should use it.

I'm also for calling null() implicitly for timestamp() in 2.1. Right now you need to always use ->timestamp()->null() in your migrations because default value for timestamp may differ depending on DBMS you're using - ->timestamp() will work like ->timestamp()->notNull() for MySQL, but in PostgreSQL it will work like ->timestamp()->null(). Using null() by default improve consistency of ColumnSchemaBuilder and allow to avoid weird mistakes.

[schmunk42]

Issue was also spotted here https://github.com/yiisoft/yii2/issues/15248#issuecomment-348014962

[schmunk42]

For the record, this is no issue with:

• MariaDB 5.5 and 10.0, 10.1
• Percona 5.7

~But we need to check if this is related:~

• ~MariaDB 10.3~

There was 1 failure:

1) yiiunit\framework\db\mysql\SchemaTest::testColumnSchema
defaultValue of column ts_default is expected to be an object but it is not.
Failed asserting that 'current_timestamp()' is of type "object".

/project/tests/framework/db/SchemaTest.php:485
/project/vendor/phpunit/phpunit/phpunit:52

Above issue tracked here: https://github.com/yiisoft/yii2/issues/15167

[rugabarbo]

It's interesting that travis builds work now thanks to the mode NO_ZERO_IN_DATE

I draw your attention that modes NO_ZERO_IN_DATE and NO_ZERO_DATE are different.

Travis sets it here: https://travis-ci.org/yiisoft/yii2/jobs/358090431#L961

Check out the results of my local tests:

MySQL: 5.7.21
Yii2 branch: master

1) First test:

Commands:

1. sudo service mysql restart
2. sudo mysql -uroot -proot <<< "SET GLOBAL sql_mode = 'NO_ZERO_IN_DATE';"
3. php vendor/bin/phpunit --group=mysql

Result:

OK, but incomplete, skipped, or risky tests!
Tests: 706, Assertions: 3251, Skipped: 7.

2) Second test:

Commands:

1. sudo service mysql restart
2. php vendor/bin/phpunit --group=mysql

Result:

There was 1 error:

1) yiiunit\framework\db\mysql\QueryBuilderTest::testCreateTableColumnTypes
yii\db\Exception: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'col62'
The SQL being executed was: CREATE TABLE `column_type_table` (
    `col1` bigint(20),
    `col2` bigint(20) NOT NULL,
    `col3` bigint(20) CHECK (`col3` > 5),
    `col4` bigint(8),
    `col5` bigint(8) CHECK (`col5` > 5),
    `col6` blob,
    `col7` tinyint(1) NOT NULL DEFAULT 1,
    `col8` tinyint(1),
    `col9` char(1) CHECK (`col9` LIKE "test%"),
    `col10` char(1) NOT NULL,
    `col11` char(6) CHECK (`col11` LIKE "test%"),
    `col12` char(6),
    `col13` char(1),
    `col14` date NOT NULL,
    `col15` date,
    `col16` datetime NOT NULL,
    `col17` datetime,
    `col18` decimal(10,0) CHECK (`col18` > 5.6),
    `col19` decimal(10,0) NOT NULL,
    `col20` decimal(12,4) CHECK (`col20` > 5.6),
    `col21` decimal(12,4),
    `col22` decimal(10,0),
    `col23` double CHECK (`col23` > 5.6),
    `col24` double NOT NULL,
    `col25` double CHECK (`col25` > 5.6),
    `col26` double,
    `col27` double,
    `col28` float CHECK (`col28` > 5.6),
    `col29` float NOT NULL,
    `col30` float CHECK (`col30` > 5.6),
    `col31` float,
    `col32` float,
    `col33` int(11) CHECK (`col33` > 5),
    `col34` int(11) NOT NULL,
    `col35` int(8) CHECK (`col35` > 5),
    `col36` int(8),
    `col37` int(11),
    `col38` decimal(19,4) CHECK (`col38` > 0.0),
    `col39` decimal(19,4) NOT NULL,
    `col40` decimal(16,2) CHECK (`col40` > 0.0),
    `col41` decimal(16,2),
    `col42` decimal(19,4),
    `col43` tinyint(2),
    `col44` tinyint(3) UNSIGNED,
    `col45` tinyint(3),
    `col46` smallint(8),
    `col47` smallint(6),
    `col48` varchar(255) CHECK (`col48` LIKE "test%"),
    `col49` varchar(255) NOT NULL,
    `col50` varchar(32) CHECK (`col50` LIKE "test%"),
    `col51` varchar(32),
    `col52` varchar(255),
    `col53` text CHECK (`col53` LIKE "test%"),
    `col54` text NOT NULL,
    `col55` text CHECK (`col55` LIKE "test%"),
    `col56` text NOT NULL,
    `col57` text,
    `col58` text,
    `col59` time NOT NULL,
    `col60` time,
    `col61` timestamp NOT NULL,
    `col62` timestamp,
    `col63` timestamp NULL DEFAULT NULL,
    `col64` int(11) COMMENT 'test comment',
    `col65` json
)

/yii2/framework/db/Schema.php:664
/yii2/framework/db/Command.php:1263
/yii2/framework/db/Command.php:1075
/yii2/tests/framework/db/QueryBuilderTest.php:1080
/yii2/vendor/phpunit/phpunit/phpunit:52

Caused by
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'col62'

/yii2/framework/db/Command.php:1258
/yii2/framework/db/Command.php:1075
/yii2/tests/framework/db/QueryBuilderTest.php:1080
/yii2/vendor/phpunit/phpunit/phpunit:52

FAILURES!
Tests: 706, Assertions: 3248, Errors: 1, Skipped: 7.

[rugabarbo]

And I want to ask why we use the NO_ZERO_IN_DATE mode in the travis config?

[samdark]

We've tried to use strictest mode possible to eliminate possible errors.

[rugabarbo]

1) I just created PR that will allow to ignore 'TIMESTAMP' column definition test when NO_ZERO_DATE enabled (it's default for MySQL 5.7).

2) It strange decision to use strictest mode for MySQL 5.6 test environment because strict mode reduces testing capabilities (for example, it isn't possible to test this 'TIMESTAMP' column definition case), and MySQL <= 5.6 doesn't use strict mode by default. It's good decision for MySQL 5.7 because it uses the strict mode by default: https://www.percona.com/blog/2016/10/18/upgrading-to-mysql-5-7-beware-of-the-new-strict-mode/

3) However, if you want to use strictest mode you should enable NO_ZERO_DATE and NO_ZERO_IN_DATE in travis configuration together. I've never seen them turned on separately for MySQL instances.

4) @rob006 you can't use TIMESTAMP NULL column definition without DEFAULT value (for MySQL). So if you want to call null() implicitly for timestamp() you also must call defaultValue(null) for it...

[samdark]

1. I think we forgot it by mistake.