MySQL 5.6: explicit_defaults_for_timestamp

Question

我有以下复制拓扑：

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

• “日期”字段：

`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

• DB3复制错误：

[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

DB3失败的原因解释了这里：

没有自动分配默认CURRENT_TIMESTAMP或更新CURRENT_TIMESTAMP属性的时间戳列。必须显式地指定这些属性。

我想了解为什么DB2工作得很好，我想这是因为它是在复制MySQL 5.5，但是是什么设置造成的呢？

更新10月1日09:34:03英国夏令时间：

三台服务器上的表定义匹配：

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不应该是这样的：

• DB1:无
• DB2，DB3: NO_ENGINE_SUBSTITUTION

摘要：

我无法在两个从服务器(DB1、DB2)上手动运行此查询，但它在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

显示这种行为的另一个快速测试：

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)

Answer 1

我也有同样的问题，试图更改sql_mode但没有工作，然后将explicit_defaults_for_timestamp更改为0并工作了！，但我鼓励您阅读以下内容来理解这个问题：http://openbedrock.blogspot.mx/2015/04/what-happened-to-mysql-timestamp-columns.html和https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Answer 2

我认为你在滥用explicit_defaults_for_timestamp。在您的例子中，这个变量应该设置为0。启用它实际上是在限制将Null值插入时间戳数据类型列。

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_显式_默认值_为_时间戳

如果禁用explicit_defaults_for_timestamp，服务器将启用非标准行为，并按以下方式处理时间戳列:未显式声明的时间戳列将使用NULL属性自动声明。允许将这样的列赋值为NULL，并将该列设置为当前时间戳。表中的第一个时间戳列(如果没有显式声明为NULL属性、显式默认值或UPDATE属性)将自动使用默认CURRENT_TIMESTAMP和UPDATE CURRENT_TIMESTAMP属性声明。如果启用了explicit_defaults_for_timestamp，服务器将禁用非标准行为并按以下方式处理时间戳列:不可能将时间戳列的值设置为NULL以将其设置为当前时间戳。若要分配当前时间戳，请将列设置为CURRENT_TIMESTAMP或同义词(如NOW() )。

同时，要注意了解不同的行为: DB2和DB3在MySQL的次要版本中是相同的吗？您只提供了主要版本，即5.6。但从5.6.6开始，主要在这一问题上发生了一些重要变化。对DB3的复制使用DB2创建的绑定日志并应用在DB3上指定的sql_mode完成。因此，应该检查并查看DB3到底在尝试从DB2创建的绑定日志中复制什么？你能在这里粘贴查询吗？出于好奇，如果您测试了从DB1到DB2的插入并运行良好，mysql不会抛出任何警告？