Bug #83003 Using temporary tables on slaves increases GTID sequence number
Submitted: 15 Sep 2016 3:48 Modified: 5 Dec 2017 14:12
Reporter: Ovais Tariq Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.6.33 OS:Any
Assigned to: CPU Architecture:Any

[15 Sep 2016 3:48] Ovais Tariq
Description:
When ROW binlog_format is used then CREATE statement does not increase the GTID sequence number as the CREATE is not logged to the binary log. However, on connection termination a DROP statement is written to the binary log which increases the GTID sequence number.

How to repeat:
mysql> set session binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000004 | 433 | | | bedd0818-5f32-11e6-bfa0-0242ac110002:1-7061 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> create temporary table t1_temp_3 as select * from t1;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000004 | 433 | | | bedd0818-5f32-11e6-bfa0-0242ac110002:1-7061 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> Bye
root@4b9ad2a13f3d:/# mysql -proot
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.31-77.0-log Percona Server (GPL), Release 77.0, Revision 5c1061c

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000004 | 602 | | | bedd0818-5f32-11e6-bfa0-0242ac110002:1-7062 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> Bye

Suggested fix:
Don't log DROP statements for temporary tables if ROW binlog_format is used
[15 Sep 2016 6:17] MySQL Verification Team
Hello Ovais,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[1 Dec 2016 20:05] Laurynas Biveinis
Bug 83003 patch 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: bug83003-8.0.0.patch (application/octet-stream, text), 38.97 KiB.

[1 Dec 2016 20:06] Laurynas Biveinis
The contributed patch has to be applied on the top of the contributed patch for bug 72475, as they both touch overlapping set of the testcases.
[2 Dec 2016 4:26] MySQL Verification Team
Thank you Laurynas for the contribution/patch.

Thanks,
Umesh
[8 Mar 2017 9:18] Laurynas Biveinis
The combination of the contributed fix and its prerequisite the fix for bug 72475 is incorrect for the following case. It results in "DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `tmp1`" logged for mixed but not row replication, but only with CREATE TABLE ... SELECT, regular CREATE TABLE is not affected. I am planning to re-submit the patches.

--source include/have_log_bin.inc
--source include/have_binlog_format_row.inc

CREATE TABLE t1(a INT);

DELIMITER |;

CREATE PROCEDURE p1()
BEGIN
#   CREATE TEMPORARY TABLE tmp1(a INT);
   CREATE TEMPORARY TABLE tmp1 SELECT * FROM t1;
   SELECT GET_LOCK('temp_table_created', 0);
   SELECT GET_LOCK('sync', -1);
   DROP TEMPORARY TABLE tmp1;
   SELECT RELEASE_LOCK('sync');
END|

DELIMITER ;|

SELECT GET_LOCK('sync', 0);

--source include/count_sessions.inc

--connect(con1,localhost,root)
--let $con1_id= `SELECT CONNECTION_ID()`
send CALL p1();

--connection default
--let $wait_condition= SELECT IS_USED_LOCK('temp_table_created') = $con1_id
--source include/wait_condition.inc

--eval KILL CONNECTION $con1_id
--connection con1
--disable_reconnect
--error 2013
reap;

--connection default
--source include/wait_until_count_sessions.inc

--source include/show_binlog_events.inc

DROP PROCEDURE p1;

DROP TABLE t1;
[8 Mar 2017 9:30] Laurynas Biveinis
A stored procedure is not required, only CREATE ... SELECT is.

--source include/have_log_bin.inc
--source include/have_binlog_format_mixed.inc

CREATE TABLE t1(a INT);

--source include/count_sessions.inc

--connect(con1,localhost,root)
--let $con1_id= `SELECT CONNECTION_ID()`
CREATE TEMPORARY TABLE tmp1 SELECT * FROM t1;
# CREATE TEMPORARY TABLE tmp1 (a INT);

--connection default

--eval KILL CONNECTION $con1_id
--connection con1
--disable_reconnect
--source include/wait_until_disconnected.inc

--connection default
--source include/wait_until_count_sessions.inc

--source include/show_binlog_events.inc

DROP TABLE t1;
[15 Mar 2017 8:24] Laurynas Biveinis
Bug 83003 follow-up patch 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: bug83003-8.0.0-2.patch (application/octet-stream, text), 7.41 KiB.

[15 Mar 2017 8:25] Laurynas Biveinis
The above patch fixes the last issue and adds more testcases, that should have been added in the original patch. It has to be applied on the top of the first patch. The patches will be combined on the next rebase.
[14 Apr 2017 9:35] Laurynas Biveinis
Bug 83003 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: bug83003-8.0.1.patch (application/octet-stream, text), 43.93 KiB.

[4 Aug 2017 19:38] Laurynas Biveinis
Bug 83003 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: bug83003-8.0.2.patch (application/octet-stream, text), 44.04 KiB.

[5 Dec 2017 14:12] Margaret Fisher
Posted by developer:
 
Changelog entry added for MySQL 8.0.4:

When binlog_format is ROW or (from MySQL 8.0.4) MIXED, operations on temporary tables are not logged. Previously, the exception to this rule was that when the connection was terminated at the end of the session, the statement DROP TEMPORARY TABLE IF EXISTS was logged for any temporary tables that had been opened in the session. For row-based replication, this behavior caused an unnecessary write to the binary log, and added a transaction sequence number for the GTID where these were enabled. 

Now, when a temporary table is created in a session, the binary logging format is tracked. The DROP TEMPORARY TABLE IF EXISTS statement is only logged at the end of the session if statement-based format was in effect when the temporary table was created, so the CREATE TEMPORARY TABLE statement was logged. If row-based or mixed format binary logging was in use when the table was created, the DROP TEMPORARY TABLE IF EXISTS statement is not logged.

Thanks to Laurynas Biveinis for the patch.

- Documentation change also made in 
https://dev.mysql.com/doc/refman/8.0/en/replication-rbr-usage.html
[1 Nov 2018 14:03] Margaret Fisher
Posted by developer:
 
Updated changelog entry and docs to reflect the backport of the bug. Reclosing.
[24 Jan 2019 13:18] Yura Sorokin
Backported to 5.7.25

When binlog_format is ROW or MIXED, operations on temporary tables are not logged. Previously, the exception to this rule was that when the connection was terminated at the end of the session, the statement DROP TEMPORARY TABLE IF EXISTS was logged for any temporary tables that had been opened in the session. For row-based replication, this behavior caused an unnecessary write to the binary log, and added a transaction sequence number for the GTID where these were enabled.

Now, when a temporary table is created in a session, the binary logging format is tracked. The DROP TEMPORARY TABLE IF EXISTS statement is only logged at the end of the session if statement-based format was in effect when the temporary table was created, so the CREATE TEMPORARY TABLE statement was logged. If row-based or mixed-format binary logging was in use when the table was created, the DROP TEMPORARY TABLE IF EXISTS statement is not logged.

Thanks to Laurynas Biveinis for the patch.
[24 Jan 2019 13:21] Yura Sorokin
Should be backported to the 5.6 branch as well.