Bug #75683 create table db.table like tmptable is binlogged incorrectly - breaks a slave

• Submitted: 29 Jan 2015 18:40
• Modified: 26 Mar 2015 13:17
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Utilities: Binlog Events
• Severity: S1 (Critical)
• Version: 5.1.73, 5.5.42, 5.6.22, 5.7.6
• OS: Any
• CPU Architecture: Any

[29 Jan 2015 18:40] Shane Bester

Description:
Binlog is written incorrectly, thus slave dies: with 'table already exists'.
See the binlog writtne (5.7.6)

mysql> show binlog events;
+---------------+-----+-------------+-----------+-------------+--------------------------------------------------------------
| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info
+---------------+-----+-------------+-----------+-------------+--------------------------------------------------------------
| i7-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.7.6-m16-debug-log, Binlog ver: 4
| i7-bin.000001 | 120 | Query       |         1 |         249 | use `db`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t1`
| i7-bin.000001 | 249 | Query       |         1 |         380 | use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t3`
| i7-bin.000001 | 380 | Query       |         1 |         488 | drop database if exists db
| i7-bin.000001 | 488 | Query       |         1 |         607 | create database if not exists db
| i7-bin.000001 | 607 | Query       |         1 |         726 | drop database if exists test
| i7-bin.000001 | 726 | Query       |         1 |         851 | create database if not exists test
| i7-bin.000001 | 851 | Query       |         1 |         979 | use `test`; create table t1 (a int) engine=innodb
| i7-bin.000001 | 979 | Query       |         1 |        1111 | use `test`; CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) |
+---------------+-----+-------------+-----------+-------------+--------------------------------------------------------------
9 rows in set (0.00 sec)

How to repeat:
Setup a clean replication using ROW format.  On master:

------
#reset master;
set binlog_format=row;
drop database if exists db;
create database if not exists db;
drop database if exists test;
create database if not exists test;
use test;
create table t1 (a int) engine=innodb;
create temporary table t3 like t1;
create table db.t1 like t3;
show binlog events;
-------

[26 Mar 2015 13:17] Erlend Dahl

Fixed in 5.5.44 5.6.25 5.7.8 5.8.0

[23 Jun 2015 13:51] Laurynas Biveinis

commit 151b8ec4d11abbb3bf1fb55e10df3e3e7449fe1c
Author: Venkatesh Duggirala <[e-mail]>
Date:   Fri Mar 13 12:32:44 2015 +0530

    Bug #20439913 CREATE TABLE DB.TABLE LIKE TMPTABLE IS BINLOGGED INCORRECTLY - BREAKS A SLAVE
    Analysis:
    In row based replication, Master does not send temp table information
    to Slave. If there are any DDLs that involves in regular table that needs
    to be sent to Slave and a temp tables (which will not be available at Slave),
    the Master rewrites the query replacing temp table with it's defintion.
    Eg: create table regular_table like temptable.
    In rewrite logic, server is ignoring the database of regular table
    which can cause problems mentioned in this bug.
    
    Fix: dont ignore database information (if available) while
    rewriting the query

commit 59142d9a279339f766a23e35a0f7b183406e43c0
Author: Venkatesh Duggirala <[e-mail]>
Date:   Fri Mar 13 13:13:48 2015 +0530

    Bug #20439913 CREATE TABLE DB.TABLE LIKE TMPTABLE IS
    BINLOGGED INCORRECTLY - BREAKS A SLAVE
    
    Submitted a incomplete patch with my previous push,
    re submitting the extra changes the required to make
    the patch complete.