MySQL 5.6: explicit_defaults_for_timestamp I have the following replication topology:


  DB1 (MySQL 5.5) -> DB2 (MySQL 5.6,  explicit_defaults_for_timestamp = 1) -> DB3 (MySQL 5.6,  explicit_defaults_for_timestamp = 1)


- "date" field:

`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,


- DB3 replication error:

[ERROR] Slave SQL: Error 'Column 'date' cannot be null' on query. Default database: 'test'. Query: 'INSERT INTO test_log VALUES (null,'12345',12345,'test','saved')', Error_code: 1048


The reason why DB3 is failing is explained here:


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


I would like to understand why DB2 is working fine, I guess that's because it's replicating from MySQL 5.5 but what settings are responsible for this?

Update Wed  1 Oct 09:34:03 BST 2014:

Table definition match on all three servers:

mysql&gt; SHOW CREATE TABLE test_log\G
*************************** 1. row ***************************
       Table: feedback_log
Create Table: CREATE TABLE `feedback_log` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `order_ref` varchar(32) NOT NULL,
  `id` int(11) NOT NULL,
  `version` varchar(12) NOT NULL,
  `event` varchar(60) NOT NULL,
  KEY `order_ref` (`order_ref`),
  KEY `id` (`id`),
  KEY `version` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


SQL_MODE shouldn't be the case here:


  
  DB1: None
  DB2, DB3: NO_ENGINE_SUBSTITUTION
  


Summary:

I can't run this query manually on both slaves (DB1, DB2) but it's replicated successfully on DB2:

mysql [5.6.20-68.0-log]&gt; INSERT INTO test_log VALUES (null,'12345',12345,'test','saved')';
ERROR 1048 (23000): Column 'date' cannot be null


Another quick test showing this behaviour:

DB1

mysql [5.5.39-log]&gt; CREATE TABLE t1 (date TIMESTAMP);
Query OK, 0 rows affected (0.20 sec)

mysql [5.5.39-log]&gt; SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


DB2

mysql [5.6.20-68.0-log]&gt; SELECT @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql [5.6.20-68.0-log]&gt; SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


DB3

mysql [5.6.20-68.0-log]&gt; SELECT @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.04 sec)

mysql [5.6.20-68.0-log]&gt; SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `date` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
I had the same problem, tried to change the sql_mode but didn't work, then changed the explicit_defaults_for_timestamp to 0 and worked!!!, but I encourage you to read the following to understand the issue:
http://openbedrock.blogspot.mx/2015/04/what-happened-to-mysql-timestamp-columns.html
and
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html