MySQL Bugs: #92398: point in time recovery using mysqlbinlog broken with temporary table -> errors

Bug #92398	point in time recovery using mysqlbinlog broken with temporary table -> errors
Submitted:	13 Sep 2018 7:18	Modified:	17 Apr 2019 12:31
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6.41, 5.7.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Affects at least 5.6, 5.7.
Running a multi-threaded workload involving temporary and non-temporary tables leads to binary log playback giving errors..

>mysqlbinlog log-bin.000001 > out.sql

>mysql -uroot --force test < out.sql
ERROR 1137 (HY000) at line 1022: Can't reopen table: 'd1'
ERROR 1146 (42S02) at line 1707: Table 'test.t1' doesn't exist
ERROR 1137 (HY000) at line 2202: Can't reopen table: 'd1'
ERROR 1050 (42S01) at line 2306: Table 't1' already exists
ERROR 1146 (42S02) at line 2714: Table 'test.t1' doesn't exist
ERROR 1146 (42S02) at line 2740: Table 'test.t1' doesn't exist
ERROR 1137 (HY000) at line 2857: Can't reopen table: 'd1'
ERROR 1146 (42S02) at line 3193: Table 'test.t1' doesn't exist
ERROR 1146 (42S02) at line 3393: Table 'test.t1' doesn't exist
ERROR 1050 (42S01) at line 3860: Table 't1' already exists
ERROR 1146 (42S02) at line 4221: Table 'test.t1' doesn't exist
ERROR 1146 (42S02) at line 4234: Table 'test.t1' doesn't exist
ERROR 1146 (42S02) at line 4247: Table 'test.t1' doesn't exist
ERROR 1146 (42S02) at line 4273: Table 'test.t1' doesn't exist

How to repeat:
1. run mysqld with --log-bin --server-id=1 --binlog-format=statement
2. execute the sample php script in two sessions at once.
3. then try to replay the binary logs generated by this run.

mysqlbinlog log-bin.000001 > out.sql
mysql -uroot --force < out.sql

For me, the error 1146 (42S02) occurs in Magento 2.6 during reindex when trying to create a temporary table.

Posted by developer:
 
Changelog entry added for MySQL 5.6.45, 5.7.27, and 8.0.17:

In query log events in the binary log, the thread ID used for the execution of DROP TABLE and DELETE statements was identified incorrectly or not at all. On a multi-threaded replication slave, where temporary tables were involved (which require the correct thread ID as they are session specific), this omission resulted in errors when using mysqlbinlog to replay the binary log for point-in-time recovery. The thread ID is now set correctly.