0

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> 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]> 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]> CREATE TABLE t1 (date TIMESTAMP);
Query OK, 0 rows affected (0.20 sec)

mysql [5.5.39-log]> 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]> SELECT @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql [5.6.20-68.0-log]> 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]> SELECT @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.04 sec)

mysql [5.6.20-68.0-log]> 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)
  • You need to compare the table definitions among the servers with SHOW CREATE TABLE and then also SELECT @@SQL_MODE;. When replicating from 5.5 to 5.6, cascaded or not, setting explicit_defaults_for_timestamp to 1 was probably the wrong choice, and your table definitions may be different, on at least one of the replicas, since the way CREATE TABLE statements are interpreted is what that variable modifies. Please update the question with what you find. – Michael - sqlbot Oct 1 '14 at 2:02
  • Maybe first replication is row based while the second one is statement based? – Ulrich Thomas Gabor Oct 1 '14 at 10:22
  • All three servers are using MIXED format. I checked binlogs and this particular statement was in STATEMENT format on both masters. – HTF Oct 1 '14 at 13:16
0

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

| improve this answer | |
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. – Anthony Genovese May 8 '17 at 16:19
0

I think you are misusing the explicit_defaults_for_timestamp. In your case this variable should be set to 0. Enabling it you are in fact restricting the insert of Null values into timestamp data type columns.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

If explicit_defaults_for_timestamp is disabled, the server enables the nonstandard behaviors and handles TIMESTAMP columns as follows:

TIMESTAMP columns not explicitly declared with the NULL attribute are automatically declared with the NOT NULL attribute. Assigning such a column a value of NULL is permitted and sets the column to the current timestamp.

The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

If explicit_defaults_for_timestamp is enabled, the server disables the nonstandard behaviors and handles TIMESTAMP columns as follows:

It is not possible to assign a TIMESTAMP column a value of NULL to set it to the current timestamp. To assign the current timestamp, set the column to CURRENT_TIMESTAMP or a synonym such as NOW().

Also, important stuff to pay attention to understand the different behavior: are the DB2 and DB3 identical in minor versions of MySQL? You only provided the major version which is 5.6. But from 5.6.6 there have been some important changes mainly on this issue. The replication to DB3 is done using the binlogs created by DB2 and applying the sql_mode specified on DB3. So these should be checked and seen what exactly is trying DB3 to replicate from the binlogs created by DB2? Can you paste the query here? Out of curiosity , if you test an insert from DB1 to DB2 and it goes ok, mysql doesn't throw any warnings?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.