MySQL Bugs: #85258: DROP TEMPORARY TABLE creates a transaction in binary log on read only server

Bug #85258	DROP TEMPORARY TABLE creates a transaction in binary log on read only server
Submitted:	1 Mar 2017 21:32	Modified:	5 Dec 2017 14:09
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.6.35, 5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Originally reported at https://bugs.launchpad.net/percona-server/+bug/1668602

If "DROP TEMPORARY TABLE..." gets executed on server with GTID enabled and read_only mode enabled, then 'DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `sometablename`' gets inserted in server binary log. This creates errant transaction, that other slaves in cluster might not have and can break replication if server with errant transaction gets promoted to master and this transaction is already deleted from binary logs.

Percona version tested: 5.6.35-80.0, Debian jessie
How to reproduce:
1. On any read-only server with non-privileged user execute "drop temporary table if exists `sometablename`", no need to actually create temporary table before dropping.
2. Check `show master status`, you'll find new gtid generated on slave in read-only mode

How to repeat:
Test case for MTR:

--source include/master-slave.inc

connection slave;
set global read_only=1;
show binlog events;
show master status;
drop temporary table if exists foo;

show binlog events;
show master status;

Put 

--gtid_mode=ON --log-slave-updates --enforce-gtid-consistency  --binlog-format=row

into both master and slave option files

Suggested fix:
Do not write DROP TEMPORARY TABLE Into binary log if nothing dropped.

Hello Sveta,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

Bug 85258 fix for 8.0.0 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug85258-8.0.0.patch (application/octet-stream, text), 244.84 KiB.

The contributed fix has to be applied on the top of the contributed fix for bug 83003.

Bug 85258 fix for 8.0.1 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug85258-8.0.1.patch (application/octet-stream, text), 240.58 KiB.

Hey, is it fixed in MySQL standard version and if yes, which one ?

Thanks

Bug 85258 fix for 8.0.2 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug85258-8.0.2.patch (application/octet-stream, text), 240.63 KiB.

Posted by developer:
 
Changelog entry added for MySQL 8.0.4:

On a read-only server with GTIDs enabled, a DROP TEMPORARY TABLE IF EXISTS statement relating to a non-existent or filtered table could write an unnecessary transaction to the binary log and create an unnecessary GTID. In this situation, the missing temporary table was previously assumed to be transactional, leading to the statement being split. Now, MySQL checks that the temporary table exists and that its DROP TEMPORARY TABLE statement is recorded in the binary log. If this is not the case, no GTID is created. Thanks to Laurynas Biveinis for the patch.