MySQL · 捉蟲動態·DROP DATABASE外鍵約束的GTID BUG · 數據庫內核月報

**背景** MySQL的DDL沒有被設計成事務操作，因此DDL操作是無法回滾的（像PgSQL把DDL也設計成事務操作，DDL就可以在執行成功后被回滾操作取消）。這就會導致如果某個DDL語句內部被拆分為多個原子的DDL調用，那么這個DDL語句就不具備中途執行失敗后回滾整個DDL語句的能力，也就是說，即使語句邏輯內的某個原子DDL調用失敗了，也無法回滾已經完成的那些原子DDL調用。 **問題描述** DROP DATABASE 就是一個例子，對于MySQL而言，DROP DATABASE 并非是一個原子DDL操作，因為它是一個個刪除DB下的每張表，而 DROP TABLE 操作本身是會做預檢查的，無法刪除就會取消刪表操作返回失敗，所以 DROP TABLE 才能認為是原子的DDL調用。這就會引起一個問題，如果一個DB中的某張表DROP失敗了，實際上 DROP DATABASE 作為一個整體是執行失敗的，但是DB中已經有一些表被刪除了，因此Binlog中會記錄成多個 DROP TABLE 操作，而不是一個 DROP DATABASE 語句。如果被刪除的表的表名都不長，還是會記錄成一個刪除多張表的 DROP TABLE 語句（DROP TABLE tbl1, tbl2, ...），但是如果表名總長度太長，MySQL會拆分為多個 DROP TABLE 語句來記錄。沒有GTID的時候這似乎也不是什么大問題，但是引入GTID之后就有一個問題：每個語句只分配一個GTID。如果一個 DROP DATABASE 語句被拆分為多個 DROP TABLE 語句，Binlog中就會出現多個 DROP TABLE 事件共用一個GTID的情況！舉個例子： ~~~ CREATE DATABASE db1; USE db1; CREATE TABLE t1 (id INT, name VARCHAR(20), PRIMARY KEY (`id`)) ENGINE=InnoDB; CREATE TABLE t2 (id INT) ENGINE=InnoDB; CREATE TABLE t3 (id INT) ENGINE=InnoDB; CREATE TABLE t4 (id INT) ENGINE=InnoDB; INSERT INTO t1 VALUES(1, "test"), (2, "try"); INSERT INTO t2 VALUES(1); # 創建很多表名很長的表 let $count = 50; while ($count) { eval create table a_very_long_long_long_long_long_table_name_$count(id int) engine = InnoDB; dec $count; } CREATE DATABASE db2; USE db2; CREATE TABLE t3 (id INT, num INT, ext_id INT, CONSTRAINT t3_fk_1 FOREIGN KEY (ext_id) REFERENCES db1.t1(id)) ENGINE=InnoDB; INSERT INTO t3 VALUES (1, 2, 2); DROP DATABASE IF EXISTS db1; ~~~ 這里因為 db2.t3 表引用了 db1.t1 的字段作為外鍵約束，所以當 db1 做 DROP DATABASE 刪除到 t1 表時就報錯了，但此時很多表已經被刪除了。我們看Binlog中記錄的內容： ~~~ SET @@SESSION.GTID_NEXT= '340d95b8-a699-11e4-868d-a0d3c1f20ae4:61'/*!*/; # at 12209 #150128 10:56:10 server id 1 end_log_pos 13259 CRC32 0xcf952733 Query thread_id=6 exec_time=1 error_code=0 use `db1`/*!*/; SET TIMESTAMP=1422413770/*!*/; DROP TABLE IF EXISTS `a_very_long_long_long_long_long_table_name_33`,`a_very_long_long_long_long_long_table_name_15`,`a_very_long_long_long_long_long_table_name_43`,`a_very_long_long_long_long_long_table_name_13`,`a_very_long_long_long_long_long_table_name_10`,`a_very_long_long_long_long_long_table_name_28`,`a_very_long_long_long_long_long_table_name_23`,`a_very_long_long_long_long_long_table_name_32`,`a_very_long_long_long_long_long_table_name_50`,`a_very_long_long_long_long_long_table_name_17`,`a_very_long_long_long_long_long_table_name_19`,`a_very_long_long_long_long_long_table_name_30`,`a_very_long_long_long_long_long_table_name_48`,`a_very_long_long_long_long_long_table_name_49`,`a_very_long_long_long_long_long_table_name_3`,`a_very_long_long_long_long_long_table_name_29`,`a_very_long_long_long_long_long_table_name_9`,`a_very_long_long_long_long_long_table_name_47`,`a_very_long_long_long_long_long_table_name_12`,`a_very_long_long_long_long_long_table_name_42` /*!*/; # at 13259 #150128 10:56:10 server id 1 end_log_pos 14315 CRC32 0xd91d1210 Query thread_id=6 exec_time=1 error_code=0 SET TIMESTAMP=1422413770/*!*/; DROP TABLE IF EXISTS `a_very_long_long_long_long_long_table_name_36`,`a_very_long_long_long_long_long_table_name_1`,`a_very_long_long_long_long_long_table_name_38`,`a_very_long_long_long_long_long_table_name_24`,`a_very_long_long_long_long_long_table_name_16`,`a_very_long_long_long_long_long_table_name_34`,`a_very_long_long_long_long_long_table_name_37`,`a_very_long_long_long_long_long_table_name_6`,`a_very_long_long_long_long_long_table_name_5`,`a_very_long_long_long_long_long_table_name_40`,`t2`,`a_very_long_long_long_long_long_table_name_4`,`a_very_long_long_long_long_long_table_name_20`,`a_very_long_long_long_long_long_table_name_45`,`a_very_long_long_long_long_long_table_name_2`,`a_very_long_long_long_long_long_table_name_27`,`a_very_long_long_long_long_long_table_name_46`,`a_very_long_long_long_long_long_table_name_35`,`t3`,`a_very_long_long_long_long_long_table_name_26`,`a_very_long_long_long_long_long_table_name_8`,`a_very_long_long_long_long_long_table_name_22` /*!*/; # at 14315 #150128 10:56:10 server id 1 end_log_pos 14891 CRC32 0x06158e42 Query thread_id=6 exec_time=1 error_code=0 SET TIMESTAMP=1422413770/*!*/; DROP TABLE IF EXISTS `a_very_long_long_long_long_long_table_name_44`,`a_very_long_long_long_long_long_table_name_11`,`a_very_long_long_long_long_long_table_name_25`,`a_very_long_long_long_long_long_table_name_18`,`a_very_long_long_long_long_long_table_name_7`,`a_very_long_long_long_long_long_table_name_31`,`a_very_long_long_long_long_long_table_name_21`,`a_very_long_long_long_long_long_table_name_14`,`t4`,`a_very_long_long_long_long_long_table_name_39`,`a_very_long_long_long_long_long_table_name_41` ~~~ 3個 DROP TABLE 語句都是同一個GTID：340d95b8-a699-11e4-868d-a0d3c1f20ae4:61 這就導致備庫復制報錯： ~~~ Last_SQL_Errno: 1837 Last_SQL_Error: Error 'When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '340d95b8-a699-11e4-868d-a0d3c1f20ae4:61'.' on query. Default database: 'db1'. Query: 'DROP TABLE IF EXISTS `a_very_long_long_long_long_long_table_name_36`,`a_very_long_long_long_long_long_table_name_1`,`a_very_long_long_long_long_long_table_name_38`,`a_very_long_long_long_long_long_table_name_24`,`a_very_long_long_long_long_long_table_name_16`,`a_very_long_long_long_long_long_table_name_34`,`a_very_long_long_long_long_long_table_name_37`,`a_very_long_long_long_long_long_table_name_6`,`a_very_long_long_long_long_long_table_name_5`,`a_very_long_long_long_long_long_table_name_40`,`t2`,`a_very_long_long_long_long_long_table_name_4`,`a_very_long_long_long_long_long_table_name_20`,`a_very_long_long_long_long_long_table_name_45`,`a_very_long_long_long_long_long_table_name_2`,`a_very_long_lon ~~~ **解決方案** 怎么解決這個問題呢？ 1\. 讓MySQL支持DDL事務 2\. 對DROP DATABASE操作進行預檢查第一種方案對MySQL改動太大了，完全不現實。因此我們采用了第二種方案，也間接實現了 DROP DATABASE 這個操作的原子性。 DROP DATABASE 之所以出現上面的狀況，就是因為沒有先檢查表是否可以刪除，而是走一步看一步，一個個刪的時候才看能不能刪除。我們對MySQL做了修正，對于DB中的每張表，在 DROP DATABASE 執行之前，都先預檢查所有可能導致刪除表失敗的條件，如果一旦發現某張表會無法刪除，就放棄整個 DROP DATABASE 操作，提示用戶刪除錯誤，讓用戶先自行解決問題后，再重新執行 DROP DATABASE。例如上面例子中的情況，本來 DROP DATABASE 執行到有外鍵約束的表時會報錯: ~~~ ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails ~~~ 但此時其他表已經刪除了，而我們修正以后，同樣的操作會報一個Error和一個Warning，并且沒有真的刪任何表： ~~~ SHOW WARNINGS; Level Code Message Error 1217 Cannot delete or update a parent row: a foreign key constraint fails Note 3000 There are at least one table referenced by foreign key, the first table is 't1'. Please drop table(s) that referenced by foreign key first! ~~~ 這里提示了用戶有表存在問題無法刪除，讓用戶先處理掉之后，再來執行 DROP DATABASE。此時庫下面所有的表都還在，一定要預檢查通過才會真的刪除。