Bug #109268 binlog file record unused "use `database`" along with actually query

• Submitted: 2 Dec 2022 6:00
• Modified: 6 Dec 2022 4:37
• Reporter: wong Bruce
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: binlog;restore

[2 Dec 2022 6:00] wong Bruce

Description:
binlog file record unused "use `{database}`" along with actually query, even though the database is already deleted in other session, thus caused the restore failed at this time failed.
Sample binlog 

#221201  8:02:05 server id 1338292036  end_log_pos 1760 CRC32 0x9a1d52aa 	Query	thread_id=37	exec_time=0	error_code=0	Xid = 10932
SET TIMESTAMP=1669881725/*!*/;
drop database test1
/*!*/;
# at 1760
#221201  8:10:13 server id 1338292036  end_log_pos 1837 CRC32 0x6eda783b 	Anonymous_GTID	last_committed=7	sequence_number=8	rbr_only=no	original_committed_timestamp=1669882213545213	immediate_commit_timestamp=1669882213545213	transaction_length=217
# original_commit_timestamp=1669882213545213 (2022-12-01 08:10:13.545213 Coordinated Universal Time)
# immediate_commit_timestamp=1669882213545213 (2022-12-01 08:10:13.545213 Coordinated Universal Time)
/*!80001 SET @@session.original_commit_timestamp=1669882213545213*//*!*/;
/*!80014 SET @@session.original_server_version=80028*//*!*/;
/*!80014 SET @@session.immediate_server_version=80028*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1837
#221201  8:10:13 server id 1338292036  end_log_pos 1977 CRC32 0xe961de69 	Query	thread_id=35	exec_time=0	error_code=0	Xid = 11220
use `test1`/*!*/;
SET TIMESTAMP=1669882213/*!*/;
SET @@session.pseudo_thread_id=35/*!*/;
DROP TABLE `test2`.`test2` /* generated by server */

How to repeat:
1. Create two databases in MySQL(test1,test2), and each table under those database for test purpose(test1.test1, test2.test2).
2. Login this MySQL server with two sessions (session1, session2).
3. In session 1, run below queries, but keep this session opening for step 5:
   3.1 use test1;
       select * from test1;
4. In session 2, run below queries:
   4.1 use test2;
       select * from test2;
       drop database test1; 
5. In session 1 run below query:
   5.1 drop table test2.test2;

Then check the binlog it this server generated, should be like below, there is a use `test1` after drop database test1, this kind of query will cause restore server failed:

#221201  8:02:05 server id 1338292036  end_log_pos 1760 CRC32 0x9a1d52aa 	Query	thread_id=37	exec_time=0	error_code=0	Xid = 10932
SET TIMESTAMP=1669881725/*!*/;
drop database test1
/*!*/;
# at 1760
#221201  8:10:13 server id 1338292036  end_log_pos 1837 CRC32 0x6eda783b 	Anonymous_GTID	last_committed=7	sequence_number=8	rbr_only=no	original_committed_timestamp=1669882213545213	immediate_commit_timestamp=1669882213545213	transaction_length=217
# original_commit_timestamp=1669882213545213 (2022-12-01 08:10:13.545213 Coordinated Universal Time)
# immediate_commit_timestamp=1669882213545213 (2022-12-01 08:10:13.545213 Coordinated Universal Time)
/*!80001 SET @@session.original_commit_timestamp=1669882213545213*//*!*/;
/*!80014 SET @@session.original_server_version=80028*//*!*/;
/*!80014 SET @@session.immediate_server_version=80028*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1837
#221201  8:10:13 server id 1338292036  end_log_pos 1977 CRC32 0xe961de69 	Query	thread_id=35	exec_time=0	error_code=0	Xid = 11220
use `test1`/*!*/;
SET TIMESTAMP=1669882213/*!*/;
SET @@session.pseudo_thread_id=35/*!*/;
DROP TABLE `test2`.`test2` /* generated by server */

Suggested fix:
the actual query for this binlog event is "DROP TABLE `test2`.`test2`", the "use `test1`" should not be exists in binlog, please remove this wrong query

[6 Dec 2022 4:37] MySQL Verification Team

Thank you for the report and feedback.