2015年7月21日火曜日

MySQL 5.7.8でまたsql_modeがちょっと変わるらしい

STRICT_TRANS_TABLESが暗黙のデフォルトになったことが俺の中で有名なMySQL 5.7ですが、MySQL 5.7.8でまたちょっと変更になるらしい。

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はどこの値が適用されるか? みたいな話題があったのでネタにしてみました。

0 件のコメント :

コメントを投稿