MySQL 主從復(fù)制錯誤1837

MySQL5.6.37版本，某人在測試環(huán)境主庫誤操作執(zhí)行刪表操作，導(dǎo)致主從斷開，在從庫查看主從信息如下：

Last_Errno: 1837

Last_Error: Worker 3 failed executing transaction '' at master log mysql-bin.013343, end_log_pos 289330740; 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 '07fd3067-b250-11e7-a2f0-1866da9e4b15:2618462902'.' on query. Default database: 'DBNAME'. Query: 'DROP TABLE IF EXISTS TABLENAME_1,TABLENAME_2 ...

執(zhí)行刪表操作怎么可能會導(dǎo)致主從斷開，問后知道是通過某工具誤操作導(dǎo)致了刪表，并立刻停止了。

故障可能原因：

1、create table table_name as select * from table_name; 會拆分成 creat table 和 insert 兩個事務(wù)，傳到slave時，slave執(zhí)行完     create table以后，沒有insert的GTID，于是報錯

2、MyISAM 存儲引擎，myisam引擎支持insert delayed語法，insert delay是異步寫入，也就是一旦執(zhí)行立即返回給客戶端成功。mysql內(nèi)部處理insert delay時，會將多個線程的insert合并后一起執(zhí)行，但是只生成了一個GTID；于是傳到slave后，由于是myisam表，從庫的同樣只能執(zhí)行第一條SQL，于是報錯

3、主庫innodb執(zhí)行一個事務(wù)，只產(chǎn)生一個gtid，myisam不支持事務(wù)，事務(wù)的第一條執(zhí)行完以后，第二個sql就沒有g(shù)tid，于是報錯

4、臨時表

5、BUG

本次故障

1、檢查對應(yīng)的庫沒有MyISAM表：

   select table_schema,table_name,engine from information_schema.tables where engine !='innodb' and table_schema = 'DBNAME';

2、檢查過enforce_gtid_consistency主從庫都為on，CREATE TABLE ... SELECT語句不能執(zhí)行成功，并且這次故障并不涉及CREATE TABLE ... SELECT語句，故排除

3、主從存儲引擎一致

4、沒有臨時表

在主庫審計日志查看，執(zhí)行了drop schema dbname;

20200707 12:55:20 '/* ApplicationName=DataGrip 2020.1.4 */ drop schema DBNAME'

查看主庫 binlog ：

# at 289328506

#200707 12:55:08 server id 100  end_log_pos 289328554 CRC32 0x1401e82a  GTID [commit=yes]

SET @@SESSION.GTID_NEXT= '07fd3067-b250-11e7-a2f0-1866da9e4b15:2618462902'/*!*/;

# at 289328554

#200707 12:55:08 server id 100  end_log_pos 289329642 CRC32 0x0728afdf  Query   thread_id=2388454119    exec_time=12    error_code=0

SET TIMESTAMP=1594097708/*!*/;

SET @@session.sql_mode=270532608/*!*/;

/*!\C utf8mb4 *//*!*/;

SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;

DROP TABLE IF EXISTS TABLENAME_1,TABLENAME_2,TABLENAME_3,TABLENAME_4,....../*!*/;

# at 289329642

#200707 12:55:08 server id 100  end_log_pos 289330740 CRC32 0x0d0122e4  Query   thread_id=2388454119    exec_time=12    error_code=0

SET TIMESTAMP=1594097708/*!*/;

DROP TABLE IF EXISTS TABLENAME_5,TABLENAME_6,TABLENAME_7,TABLENAME_8,....../*!*/;

# at 289330740

#200707 12:55:08 server id 100  end_log_pos 289331832 CRC32 0xd8409afa  Query   thread_id=2388454119    exec_time=12    error_code=0

SET TIMESTAMP=1594097708/*!*/;

DROP TABLE IF EXISTS TABLENAME_9,TABLENAME_10,TABLENAME_11,TABLENAME_12,....../*!*/;

# at 289331832

#200707 12:55:08 server id 100  end_log_pos 289332298 CRC32 0xa6657cc5  Query   thread_id=2388454119    exec_time=12    error_code=0

SET TIMESTAMP=1594097708/*!*/;

DROP TABLE IF EXISTS TABLENAME_13,TABLENAME_14,TABLENAME_15,TABLENAME_16,....../*!*/;

# at 289332298

#200707 12:55:20 server id 100  end_log_pos 289332346 CRC32 0x0cc19e83  GTID [commit=yes]

SET @@SESSION.GTID_NEXT= '07fd3067-b250-11e7-a2f0-1866da9e4b15:2618462903'/*!*/;

查看從庫 binlog ：

# at 19856236

#200707 12:55:08 server id 100  end_log_pos 19856284 CRC32 0x5e42595e   GTID [commit=yes]

SET @@SESSION.GTID_NEXT= '07fd3067-b250-11e7-a2f0-1866da9e4b15:2618462902'/*!*/;

DROP TABLE IF EXISTS TABLENAME_1,TABLENAME_2,TABLENAME_3,TABLENAME_4,......# at 19857398

#200707 12:55:23 server id 100  end_log_pos 19857446 CRC32 0x81998bd5   GTID [commit=yes]

SET @@SESSION.GTID_NEXT= '07fd3067-b250-11e7-a2f0-1866da9e4b15:2618463065'/*!*/;

# at 19857446

#200707 12:55:23 server id 100  end_log_pos 19857509 CRC32 0x0916a5e2   Query   thread_id=2388456043    exec_time=0     error_code=0

SET TIMESTAMP=1594097723/*!*/;

SET @@session.sql_mode=524288/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

BEGIN

/*!*/;

# at 19857509

#200707 12:55:23 server id 100  end_log_pos 19859435 CRC32 0x2eec3842   Rows_query

# insert into talename....

......

......

#200707 12:55:23 server id 100  end_log_pos 19860287 CRC32 0xc983ec89   Xid = 5405680966

COMMIT/*!*/;

# at 19860287

#200707 12:55:08 server id 100  end_log_pos 19861411 CRC32 0x20f2bc78   Query   thread_id=2388454119    exec_time=1669  error_code=0

SET TIMESTAMP=1594097708/*!*/;

SET @@session.sql_mode=270532608/*!*/;

/*!\C utf8mb4 *//*!*/;

SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;

DROP TABLE IF EXISTS TABLENAME_5,TABLENAME_6,TABLENAME_7,TABLENAME_8,....../*!*/;

# at 19861411

#200707 12:55:08 server id 100  end_log_pos 19862529 CRC32 0x35204dfe   Query   thread_id=2388454119    exec_time=1672  error_code=0

SET TIMESTAMP=1594097708/*!*/;

DROP TABLE IF EXISTS TABLENAME_9,TABLENAME_10,TABLENAME_11,TABLENAME_12,....../*!*/;

# at 19862529

#200707 12:55:23 server id 100  end_log_pos 19862577 CRC32 0x48b02b33   GTID [commit=yes]

SET @@SESSION.GTID_NEXT= '07fd3067-b250-11e7-a2f0-1866da9e4b15:2618463066'/*!*/;

1、因為數(shù)據(jù)庫開啟了GTID復(fù)制，每一個GTID需要與一個唯一的事務(wù)對應(yīng)，"drop schema dbname;" 在從庫將刪表語句拆分成了多個語句。

2、查看主從GTID，發(fā)現(xiàn)從庫GTID缺少了從'07fd3067-b250-11e7-a2f0-1866da9e4b15:2618462903' 至 '07fd3067-b250-11e7-a2f0-1866da9e4b15:2618463064'信息

3、而且從庫在GTID為'07fd3067-b250-11e7-a2f0-1866da9e4b15:2618462902'時執(zhí)行了一個DROP TABLE IF EXISTS語句后，直接進入GTID為'07fd3067-b250-11e7-a2f0-1866da9e4b15:2618463065' 執(zhí)行insert into語句并COMMIT

4、COMMIT后，正常需要設(shè)置不同的@@SESSION.GTID_NEXT，但是沒有，而是再次執(zhí)行DROP TABLE IF EXISTS語句。事務(wù)在GTID為'07fd3067-b250-11e7-a2f0-1866da9e4b15:2618462902'后發(fā)生了異常拆分，所以主從復(fù)制發(fā)生報錯。