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

-- 5.6.35

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.35/mysql-test: ./mtr bug_rpl85258
Logging: ./mtr  bug_rpl85258
2017-03-02 10:26:20 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2017-03-02 10:26:20 0 [Note] /export/umesh/server/binaries/GABuilds/mysql-5.6.35/bin/mysqld (mysqld 5.6.35-enterprise-commercial-advanced) starting as process 24817 ...
2017-03-02 10:26:20 24817 [Note] Plugin 'FEDERATED' is disabled.
2017-03-02 10:26:20 24817 [Note] Binlog end
2017-03-02 10:26:20 24817 [Note] Shutting down plugin 'CSV'
2017-03-02 10:26:20 24817 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.35
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-5.6.35/mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
Warnings:
Note    ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
Note    ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
set global read_only=1;
show binlog events;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
slave-bin.000001        4       Format_desc     2       120     Server ver: 5.6.35-enterprise-commercial-advanced-log, Binlog ver: 4
slave-bin.000001        120     Previous_gtids  2       151
show master status;
File    Position        Binlog_Do_DB    Binlog_Ignore_DB        Executed_Gtid_Set
slave-bin.000001        151
drop temporary table if exists foo;
Warnings:
Note    1051    Unknown table 'test.foo'
show binlog events;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
slave-bin.000001        4       Format_desc     2       120     Server ver: 5.6.35-enterprise-commercial-advanced-log, Binlog ver: 4
slave-bin.000001        120     Previous_gtids  2       151
slave-bin.000001        151     Gtid    2       199     SET @@SESSION.GTID_NEXT= '4d8f6823-ff2a-11e6-92bf-0010e05f3e06:1'
slave-bin.000001        199     Query   2       335     use `test`; DROP TEMPORARY TABLE IF EXISTS `foo` /* generated by server */
show master status;
File    Position        Binlog_Do_DB    Binlog_Ignore_DB        Executed_Gtid_Set
slave-bin.000001        335                     4d8f6823-ff2a-11e6-92bf-0010e05f3e06:1
main.bug_rpl85258                        [ pass ]    102

-- 5.7.17

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.17/mysql-test: ./mtr bug_rpl85258
Logging: ./mtr  bug_rpl85258
MySQL Version 5.7.17
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-5.7.17/mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
Warnings:
Note    ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
Note    ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
set global read_only=1;
show binlog events;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
slave-bin.000001        4       Format_desc     2       123     Server ver: 5.7.17-log, Binlog ver: 4
slave-bin.000001        123     Previous_gtids  2       154
show master status;
File    Position        Binlog_Do_DB    Binlog_Ignore_DB        Executed_Gtid_Set
slave-bin.000001        154
drop temporary table if exists foo;
Warnings:
Note    1051    Unknown table 'test.foo'
show binlog events;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
slave-bin.000001        4       Format_desc     2       123     Server ver: 5.7.17-log, Binlog ver: 4
slave-bin.000001        123     Previous_gtids  2       154
slave-bin.000001        154     Gtid    2       219     SET @@SESSION.GTID_NEXT= '3e2e9df0-ff29-11e6-9d92-0010e05f3e06:1'
slave-bin.000001        219     Query   2       355     use `test`; DROP TEMPORARY TABLE IF EXISTS `foo` /* generated by server */
show master status;
File    Position        Binlog_Do_DB    Binlog_Ignore_DB        Executed_Gtid_Set
slave-bin.000001        355                     3e2e9df0-ff29-11e6-9d92-0010e05f3e06:1
main.bug_rpl85258                        [ pass ]    101

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.