Bug #92398 point in time recovery using mysqlbinlog broken with temporary table -> errors
Submitted: 13 Sep 7:18
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified 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

[13 Sep 7:18] Shane Bester
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
[13 Sep 7:19] Shane Bester
sample php script to run queries in random order

Attachment: tmp_table_bug.php (application/octet-stream, text), 1.51 KiB.