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

[1 Mar 2017 21:32] Sveta Smirnova
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.
[2 Mar 2017 9:47] Umesh Shastry
Hello Sveta,

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

Thanks,
Umesh
[22 Mar 2017 6:49] Laurynas Biveinis
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.

[22 Mar 2017 6:50] Laurynas Biveinis
The contributed fix has to be applied on the top of the contributed fix for bug 83003.
[14 Apr 2017 9:56] Laurynas Biveinis
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.

[22 May 2017 19:37] Cyril SCETBON
Hey, is it fixed in MySQL standard version and if yes, which one ?

Thanks
[4 Aug 2017 19:50] Laurynas Biveinis
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.

[5 Dec 2017 14:09] Margaret Fisher
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.