Shulou(Shulou.com)06/01 Report--

In MySQL5.6.37 version, someone deletes a table in the master database of the test environment, resulting in a master-slave disconnection. Check the master-slave information in the slave database as follows:

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-1866da9e4b15pur2618462902room.' On query. Default database: 'DBNAME'. Query: 'DROP TABLE IF EXISTS TABLENAME_1,TABLENAME_2...

How could the operation of deleting the table lead to the master-slave disconnection? after asking, I knew that the deletion of the table was caused by the misoperation of some tool, and stopped immediately.

Possible causes of failure:

1. Create table table_name as select * from table_name; will be split into two transactions: creat table and insert. When passed to slave, after slave executes create table, there is no GTID for insert, so an error is reported.

2. MyISAM storage engine. Myisam engine supports insert delayed syntax. Insert delay is written asynchronously, that is, once executed, it is returned to the client successfully. When mysql processes insert delay internally, the insert of multiple threads will be merged and executed together, but only one GTID; will be generated, so after it is passed to slave, because it is a myisam table, the slave can only execute the first SQL, so an error is reported.

3. The main library innodb executes a transaction, and only one gtid,myisam does not support the transaction. After the first item of the transaction is executed, the second sql has no gtid, so an error is reported.

4. Temporary table

5 、 BUG

This fault

1. Check that there is no MyISAM table in the corresponding library:

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

2. Check that the master and slave libraries of enforce_gtid_consistency are all on,CREATE TABLE. The SELECT statement could not be executed successfully, and this failure does not involve CREATE TABLE. SELECT statement, so exclude

3. Master-slave storage engine is consistent

4. No temporary table

Check the audit log in the main database and execute drop schema dbname

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

View the main library 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-1866da9e4b15purl 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.sqlcards modewords 270532608 Universe

/ *!\ C utf8mb4 * /! * /

SET @ @ session. Session. Session setting client.45 session. Collationalization connection45 session. Session. Collationalization server33 apperance.

DROP TABLE IF EXISTS TABLENAMEQUE 1 Magi TABLENAMEQUE 2 pr. TABLENAMEY3 TABLENAMEQUE 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 TABLENAMEQUE 5 Magi TABLENAMEQUE 6 pr. TABLENAME7 TABLENAMEY7 TABLENAMEY8.

# 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 TABLENAMEQUE 9 Magi TABLENAMEQUE 10 pr. TABLENAMEY11 TABLENAMEY11 TABLENAMEI 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 TABLENAMEPROTED 13 repartedTABLENAMEY14, TABLENAMEO15, TABLENAMEHER 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-1866da9e4b15purl 2618462903 /

View the slave 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-1866da9e4b15purl 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-1866da9e4b1507fd3067-b250 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.sqlcards modewords 524288 Universe accounts /

/ *!\ C utf8 * /! * /

SET @ @ session. Session. Session setting setting clientmakers 33 minutes. Session. Collationalization connections 33 minutes.

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

Com _ MIT _ blank /

# 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.sqlcards modewords 270532608 Universe

/ *!\ C utf8mb4 * /! * /

SET @ @ session. Session. Session setting client.45 session. Collationalization connection45 session. Session. Collationalization server33 apperance.

DROP TABLE IF EXISTS TABLENAMEQUE 5 Magi TABLENAMEQUE 6 pr. TABLENAME7 TABLENAMEY7 TABLENAMEY8.

# 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 TABLENAMEQUE 9 Magi TABLENAMEQUE 10 pr. TABLENAMEY11 TABLENAMEY11 TABLENAMEI 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-1866da9e4b1507fd3067-b250 2618463066 /

1. Because GTID replication is enabled in the database, each GTID needs to correspond to a unique transaction. "drop schema dbname;" splits the delete table statement into multiple statements from the library.

2. Looking at the master-slave GTID, it is found that the slave library GTID is missing information from '07fd3067-b250-11e7-a2f0-1866da9e4b15RV 2618462903' to' 07fd3067-b250-11e7-a2f0-1866da9e4b15Rose 2618463064'

3. After the slave library executes a DROP TABLE IF EXISTS statement when the GTID is' 07fd3067-b250-11e7-a2f0-1866da9e4b15DROP TABLE IF EXISTS 2618462902', directly enter the GTID '07fd3067-b250-11e7-a2f0-1866da9e4b1511e7-a2f0' to execute the insert into statement and COMMIT

4. After COMMIT, you normally need to set a different @ @ SESSION.GTID_NEXT, but do not, but execute the DROP TABLE IF EXISTS statement again. An abnormal split of the transaction occurred after the GTID was' 07fd3067-b250-11e7-a2f0-1866da9e4b15purl 2618462902', so an error occurred in the master-slave replication.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.