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.
- MySQL 5.7 error reporting from database 1032 [How to Solve]
- How to Solve MYSQL ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
- [Solved] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=20131)
- [Solved] Last_IO_Error: error connecting to master ‘[email protected]:3306’ – retry-time: 60 …
- MySQL master-slave synchronization error 1507
- mysql Slave_IO_Running:NO [How to Solve]
- ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL ” first.
- The ‘INFORMATION_SCHEMA.GLOBAL_STATUS’ feature is disabled; see the documentation for …
- MySQL reports an error lock wait timeout exceeded; Try restarting transaction problem solving method
- [Solved] MYSQL Deadlock found when trying to get lock; try restarting transaction