How to Solve MySQL Errno:1782 Error

Errno: 1782 error occurred in a set of MySQL 8.0.16 master-slave test environment today. The detailed error reports are as follows:

Worker 1 failed executing transaction 'NOT_YET_DETERMINED' at master log mysql-bin.000029, end_log_pos 33350454; Error executing row event: '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.'

This problem is common in versions 5.7 and earlier: https://bugs.mysql.com/bug.php?id=85480

Through the site analysis of the relay log, it is found that the log contains a commit operation that does not generate gtid and begin, resulting in anonymous transactions, resulting in replication exceptions. The log contents are as follows.

From the log information, when the commit is executed, the gtid is not set_Next, so MySQL considers that the transaction is not anonymous, and the SQL thread stops abnormally

There is little information about the error “row event for unknown table” on the Internet. The information found is explained to the effect that it is caused by the loss of information about the establishment of table map.

 

The above situation is to skip the two parts “SET @@SESSION.GTID_NEXT” and “BEGIN”, and if only “SET @@SESSION.GTID_NEXT” is skipped, the following error will be reported when BEGIN is executed:

Last_Errno: 1782
Last_Error: Error '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.' on query. Default database: ''. Query: 'BEGIN'

The GTID at the error site is “7efd338e-ee5e-11ea-8957-000c29bed658:5634856”, and the GTID executed from the library is “7efd338e-ee5e-11ea-8957-000c29bed658:5634856”. Due to the lack of GTID_NEXT identifying the transaction, The slave library cannot determine whether the transaction has been executed on the slave library, and the transaction cannot be automatically skipped.

 

Try to repair, this problem can be fixed by restarting GTID, so that the master and slave can synchronize normally:

#close GTID
stop slave sql_thread;
SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';
SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';
SET GLOBAL GTID_MODE = 'ON';
start slave sql_thread;
#At this time, the start slave will have an Errno: 1781 exception
#open GTID
SET GLOBAL GTID_MODE = OFF_PERMISSIVE;
SET GLOBAL GTID_MODE = ON_PERMISSIVE;
SET GLOBAL GTID_MODE = ON;
start slave;

However, because the above error shows that the GTID executed first is greater than the GTID generated later, it is impossible to ensure whether the data is different (of course, you can use pt-checksum to compare the data). To be on the safe side, decide to use hot spare redo copy to quickly restore.

Similar Posts: