MySQL中GTID的幾個限制和解決方案（r13筆記第21天）

   現在我看待一個技術，總是會換一種角度來看，在他能實現什麼的基礎上，我更喜歡看他不能做什麼，為什麼不能這麼做。

   比如MySQL GTID在5.6試水，5.7已經發展完善，但是還是有一些場景是受限的。比如下面的兩個。

    一個是create table xxx as select 的模式，另外一個是臨時表相關的。

   今天我們就來簡單說說這兩個場景。

GTID中create 語句限制的解法

   create table xxx as select的語句，其實會被拆分為兩部分，create語句和insert語句，但是如果想一次搞定，MySQL會丟擲如下的錯誤。

mysql> create table test_new as select *from test;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

   這種語句其實目標明確，複製表結構，複製資料，insert的部分好解決，難點就在於create table的部分，如果一個表的列有100個，那麼拼出這麼一個語句來就是一個工程了。

    我們也巧學巧用，看看MySQL有什麼特別的方法來處理。

    除了規規矩矩的拼出建表語句之外，還有一個方法是MySQL特有的用法 like。

    create table xxx as select 的方式會被拆分成兩部分。

 create table xxxx like data_mgr;
 insert into xxxx select *from data_mgr;

臨時表的限制和考慮

   另外一個看起來就有些蹊蹺了，看著文件就是沒有什麼好說的，記住了就好，其實不然。

    如果在事務中有臨時表的變動，很可能會導致資料不一致，這在MySQL的5.5版本中有相應的bug,可以參見https://bugs.mysql.com/bug.php?id=76940

     如果需要復現，可以在找一套5.5的環境來模擬一下，分分鐘出效果。

    我們建立兩個表t1,t2,然後建立兩個表之間的外來鍵關聯，作為 後續測試所用。

create table t1(c1 int primary key)  engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);
create table t2 (c1 int, c2 int, foreign key(c2) references t1(c1)) engine=innodb;
insert into t2 values(1,1),(2,2),(5,5);  

 建立臨時表

> create temporary table tmp as select * from t1;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

模擬這個bug,開啟事務。

> begin;
> drop temporary table if exists tmp;
Query OK, 0 rows affected (0.00 sec)
> delete from t1 where c1 > 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a fore;
Query OK, 0 rows affected (0.00 sec)

然後使用mysqlbinlog來檢視一下里面的資訊。可以看到除了上面的臨時表操作，後面的delete也會寫入binlog

use `test`/*!*/;
SET TIMESTAMP=1499784283/*!*/;
DROP TEMPORARY TABLE IF EXISTS `tmp` /* generated by server */
/*!*/;
# at 300
# at 341
#170711 22:44:46 server id 13386  end_log_pos 341       Table_map: `test`.`t1` mapped to number 207
#170711 22:44:46 server id 13386  end_log_pos 380       Delete_rows: table id 207 flags: STMT_END_F

BINLOG '
XuRkWRNKNAAAKQAAAFUBAAAAAM8AAAAAAAEABHRlc3QAAnQxAAEDAAA=
XuRkWRlKNAAAJwAAAHwBAAAAAM8AAAAAAAEAAf/+AwAAAP4EAAAA
'/*!*/;
### DELETE FROM test.t1
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
### DELETE FROM test.t1
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
# at 380
#170711 22:44:49 server id 13386  end_log_pos 449       Query   thread_id=176   exec_time=0     error_code=0
SET TIMESTAMP=1499784289/*!*/;
COMMIT

   通過這個可以清晰的看到儘管已經做了事務回滾，但是binlog還是會記錄下回滾的變更，這在某些場景中會觸發主從資料不一致。

   而在GTID中，已經做了這個檢查，歸根結底，還是cache裡面的機制，大體來說，binlog有兩個cache來快取事務的binlog：

  binlog_cache_data stmt_cache; //存放非事務表和臨時表binlog
  binlog_cache_data trx_cache;  //存放事務表binlog

此處參考了https://www.kancloud.cn/taobaomysql/monthly/67044

   所以說兩個概念性的知識點如果稍一擴充套件就會有很多可行的方案來。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29371470/viewspace-2142056/，如需轉載，請註明出處，否則將追究法律責任。