# SQL\_MODE="NO\_ENGINE\_SUBSTITUTION" replication issue - 5.7.19 on Centos 7

**URL:** <https://forums.percona.com/t/sql-mode-no-engine-substitution-replication-issue-5-7-19-on-centos-7/6004>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [November 17, 2017, 4:46am UTC](https://forums.percona.com/t/sql-mode-no-engine-substitution-replication-issue-5-7-19-on-centos-7/6004 "2017-11-17T04:46:13Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mikes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mikes/32/2876_2.png) [@mikes](https://forums.percona.com/u/mikes)\
**Post date:** [November 17, 2017, 4:46am UTC](https://forums.percona.com/t/sql-mode-no-engine-substitution-replication-issue-5-7-19-on-centos-7/6004/1 "2017-11-17T04:46:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mikes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mikes/32/2876_2.png) [@mikes](https://forums.percona.com/u/mikes)\
**Post date:** [November 17, 2017, 6:52am UTC](https://forums.percona.com/t/sql-mode-no-engine-substitution-replication-issue-5-7-19-on-centos-7/6004/2 "2017-11-17T06:52:45Z")

</div>

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](https://dev.mysql.com/doc/refman/5.6/en/datetime.html) 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