SQL_MODE="NO_ENGINE_SUBSTITUTION" replication issue - 5.7.19 on Centos 7

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hi,

We are in the process of attempting to upgrade our production DB from 5.6 to 5.7, and are at the stage of adding in a new slave so that it can form a 3 slave 1 master setup, which will ultimately be promoted to be the new Master once the other 2 slaves have also been upgraded.

New slave is running on Centos 7.4, data volume is a 4 disk raid10 ssd lvm.

So, Percona 5.7 installed, full backup restored, mysql upgrade run.

Slave setup and started.

Both Master (5.6) and Slave (5.7) have SQL_MODE=“NO_ENGINE_SUBSTITUTION” set in ./etc/my.cnf, in the correct section. Checked in both mysqld.log and global variables.

However, despite this, once the slave is started and begins to catch up, it soon runs into error 1048’s

2017-11-17T09:30:19.772355Z 13 [ERROR] Slave SQL for channel ‘’: Error ‘Column ‘date’ cannot be null’ on query. Default database: ‘my_database’. Query: ‘INSERT INTO user_favourites (user_id, favourite_id, date) VALUES (‘3504328’, 2316904, NULL)’, Error_code: 1048
2017-11-17T09:30:19.772377Z 13 [Warning] Slave: Column ‘date’ cannot be null Error_code: 1048

From what we have read, this is an expected feature of the new stricter sql_mode setting, however, our understanding was that setting to NO_ENGINE_SUBSTITUTION was the correct temporary work round.

As this is a new slave there is no pressing issue, however, this doesn’t happen on test setup (Centos 6.9 Master and Slave, running same version, 5.7.19) and currently prevents further progress.

Any insights would be greatly appreciated.

Thanks,

Mike

2

Once again, it would seem that this is our fault.

Attempting to reduce the number of warnings on startup, I had enabled “explict_defaults_for_timestamp”, which when taken off, allowed the slave to continue.

We are attempting to understand which particular one of the 6 options changed the following line to NULL.

date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

which appears to be

No TIMESTAMP column is automatically declared with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. Those attributes must be explicitly specified.

But still investigating.

Mike