MySQL :: MySQL 5.7 Reference Manual :: 5.1.7 Server SQL Modes
5.7.4から5.7.7の間までは、STRICT_{TRANS|ALL}_TABLESは "5.6までのSTRICT_{TRANS|ALL}_TABLES" + "ERROR_FOR_DIVISION_BY_ZERO" + "NO_ZERO_DATE" + "NO_ZERO_IN_DATE" でした。
これが5.7.8からもとに戻ります。STRICT_TRANS_TABLESは5.6までのSTRICT_TRANS_TABLESと同じ効果を持つようになり、同時に暗黙のデフォルトにERROR_FOR_DIVISION_BY_ZERO
, NO_ZERO_DATE, NO_ZERO_IN_DATEが追加されて、動作としては5.7.7までと同じです。
なんでこんな迷走をしたかというと、sql_modeの名前を同じにしてしまったことで、 *同じsql_modeでもバージョンによってエラーになったりならなかったりする* クエリーが存在出来てしまったからです。オチを先に言うと、 バージョンをまたいだレプリケーションが壊れる問題がありました。
MySQL Bugs: #75439: MySQL 5.7 sql_mode problems breaks replication
MySQL 5.6のマスターにMySQL 5.7のスレーブをぶら下げ（スレーブ側が1世代離れたレプリケーションは公式にサポートされています） sql_mode= STRICT_TRANS_TABLES に設定して、
master> CREATE TABLE t1 (dt DATETIME);
Query OK, 0 rows affected (0.04 sec)
master> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
master> INSERT INTO t1 VALUES ('2015-07-00');
Query OK, 1 row affected (0.00 sec)
master> SELECT * FROM t1;
+---------------------+
| dt |
+---------------------+
| 2015-07-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
slave> SHOW SLAVE STATUS\G
..
Last_SQL_Errno: 1292
Last_SQL_Error: Error 'Incorrect datetime value: '2015-07-00' for column 'dt' at row 1' on query. Default database: 'd1'. Query: 'INSERT INTO t1 VALUES ('2015-07-00')'
..
と、レプリケーションが止まります。レプリケーションにおいてsql_modeは *マスターでクエリーが実行された時にバイナリーログに記録され*, スレーブでクエリーが実行される時にそのsql_modeが復元されます。5.6のSTRICT_TRNAS_TABLESでは2015/07/00は入れられるけれど、5.7.7のSTRICT_TRANS_TABLESではNO_ZERO_IN_DATEとの合わせ技でエラーになるから。
$ mysqlbinlog master-bin.000003
# at 199
#150717 18:21:55 server id 56 end_log_pos 330 CRC32 0x35ed6930 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437124915/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
grant replication slave on *.* to replicator
/*!*/;
# at 330
#150717 18:22:21 server id 56 end_log_pos 378 CRC32 0x56b39dd7 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'cdd4d843-2c64-11e5-af02-0242ac11000a:2'/*!*/;
# at 378
#150717 18:22:21 server id 56 end_log_pos 466 CRC32 0xc4857014 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437124941/*!*/;
create database d1
/*!*/;
# at 466
#150717 18:22:32 server id 56 end_log_pos 514 CRC32 0x02f50263 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'cdd4d843-2c64-11e5-af02-0242ac11000a:3'/*!*/;
# at 514
#150717 18:22:32 server id 56 end_log_pos 613 CRC32 0x4de24e96 Query thread_id=2 exec_time=0 error_code=0
use `d1`/*!*/;
SET TIMESTAMP=1437124952/*!*/;
CREATE TABLE t1 (dt DATETIME)
/*!*/;
# at 613
#150717 18:22:48 server id 56 end_log_pos 661 CRC32 0x5a89febd GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'cdd4d843-2c64-11e5-af02-0242ac11000a:4'/*!*/;
# at 661
#150717 18:22:48 server id 56 end_log_pos 736 CRC32 0xaeb90296 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437124968/*!*/;
BEGIN
/*!*/;
# at 736
#150717 18:22:48 server id 56 end_log_pos 842 CRC32 0xbc225a86 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437124968/*!*/;
INSERT INTO t1 VALUES ('2015-07-00')
/*!*/;
..
$ mysqlbinlog slave-relay-bin.000002
..
# at 400
#150717 18:21:55 server id 56 end_log_pos 330 CRC32 0x35ed6930 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437124915/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
grant replication slave on *.* to replicator
/*!*/;
# at 531
#150717 18:22:21 server id 56 end_log_pos 378 CRC32 0x56b39dd7 GTID last_committed=0 sequence_number=0
SET @@SESSION.GTID_NEXT= 'cdd4d843-2c64-11e5-af02-0242ac11000a:2'/*!*/;
# at 579
#150717 18:22:21 server id 56 end_log_pos 466 CRC32 0xc4857014 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437124941/*!*/;
create database d1
/*!*/;
# at 667
#150717 18:22:32 server id 56 end_log_pos 514 CRC32 0x02f50263 GTID last_committed=0 sequence_number=0
SET @@SESSION.GTID_NEXT= 'cdd4d843-2c64-11e5-af02-0242ac11000a:3'/*!*/;
# at 715
#150717 18:22:32 server id 56 end_log_pos 613 CRC32 0x4de24e96 Query thread_id=2 exec_time=0 error_code=0
use `d1`/*!*/;
SET TIMESTAMP=1437124952/*!*/;
CREATE TABLE t1 (dt DATETIME)
/*!*/;
# at 814
#150717 18:22:48 server id 56 end_log_pos 661 CRC32 0x5a89febd GTID last_committed=0 sequence_number=0
SET @@SESSION.GTID_NEXT= 'cdd4d843-2c64-11e5-af02-0242ac11000a:4'/*!*/;
# at 862
#150717 18:22:48 server id 56 end_log_pos 736 CRC32 0xaeb90296 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437124968/*!*/;
BEGIN
/*!*/;
# at 937
#150717 18:22:48 server id 56 end_log_pos 842 CRC32 0xbc225a86 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437124968/*!*/;
INSERT INTO t1 VALUES ('2015-07-00')
/*!*/;
# at 1043
#150717 18:22:48 server id 56 end_log_pos 873 CRC32 0xcab86b8a Xid = 23
COMMIT/*!*/;
..
このSET sql_mode= ..の部分(sql_modeは内部的にビット和で表現されているので、STRICT_TRANS_TABLES(= 2097152) + NO_ENGINE_SUBSTITUTION(= 1073741824) = 1075838976と表現されてます)とINSERTクエリーがそのままスレーブで実行されるので、スレーブではエラーになります。
https://github.com/mysql/mysql-server/blob/5.7/sql/sql_class.h#L121-L160
直ったは直ったので気にする必要はないんですが、レプリケーションのsql_modeはどこの値が適用されるか？ みたいな話題があったのでネタにしてみました。
