MySQL Bugs: #47418: RBR fails, failure with mixup of base/temporary/view TABLE DDL

Bug #47418	RBR fails, failure with mixup of base/temporary/view TABLE DDL
Submitted:	17 Sep 2009 14:50	Modified:	15 Mar 2010 4:48
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1,5.4	OS:	Any
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
My script:
----------
--disable_abort_on_error
--source include/master-slave.inc
CREATE TABLE t1 ( f1 INTEGER );
CREATE TEMPORARY TABLE t2 LIKE t1;
CREATE TABLE t3 AS SELECT * FROM t2;
CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t3;
DROP TABLE t2;
CREATE TABLE t2 LIKE t1;
CREATE VIEW t3 AS SELECT * FROM t2;
sync_slave_with_master;

Result on mysql-next-bugfixing 2009-09-16 12:23:16:
---------------------------------------------------
=== SHOW SLAVE STATUS ===
...
Last_Errno  1352
Last_Error  View's SELECT refers to a temporary table 't2'

=== var/log/mysqld.2.err ===
...
[ERROR] Slave SQL: Query caused different errors on master and slave.     Error on master: 'Table '%-.192s' already exists' (1050), Error on slave: 'View's SELECT refers to a temporary table 't2'' (1352). Default database: 'test'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t3` AS SELECT * FROM t2', Error_code: 0
[ERROR] Slave SQL: View's SELECT refers to a temporary table 't2', Error_code: 1352
[ERROR] Slave: View's SELECT refers to a temporary table 't2' Error_code: 1352
[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 808

mysql-5.1-bugteam shows the same result

My environment:
---------------
- mysql-next-bugfixing 2009-09-16 12:23:16
- mysql-5.1-bugteam 2009-09-16 09:01:28
- ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
Please take the script from above and run
./mysql-test-run --mysqld=--binlog-format=row \
   --no-check-testcases <script>

What happened on master side (taken from mysqltest.log)?
--------------------------------------------------------
CREATE TABLE t1 ( f1 INTEGER );
CREATE TEMPORARY TABLE t2 LIKE t1;
CREATE TABLE t3 AS SELECT * FROM t2;
CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t3;
Warnings:
Note  1050  Table 't2' already exists
     Ok
     Nothing will be modified..
     This means this statement is not relevant 
     for logging of modifications.
DROP TABLE t2;
     The temporary table t2 gets dropped.
CREATE TABLE t2 LIKE t1;
     We have now a non temporary table t2.
CREATE VIEW t3 AS SELECT * FROM t2;
ERROR 42S01: Table 't3' already exists

Final state on master:
----------------------
name | type
t1   | non temporary table
t2   | non temporary table
t3   | non temporary table

SHOW BINLOG EVENTS IN 'master-bin.000001';
------------------------------------------
Event_type End_log_pos     Info
     Server ver: 5.4.4-alpha-debug-log, Binlog ver: 4
Query  200     use `test`; CREATE TABLE t1 ( f1 INTEGER )
Query  268     BEGIN
Query  380     use `test`; CREATE TABLE `t3` ( `f1` int(11) DEFAULT NULL)
Query  449     COMMIT
Query  517     BEGIN
Query  653     use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2` (
  `f1` int(11) DEFAULT NULL)
Query  722     COMMIT
Query  808     use `test`; CREATE TABLE t2 LIKE t1
Query  975     use `test`; CREATE ... VIEW `t3` AS SELECT * FROM t2

I am surprised that
1. the "DROP TABLE t2;" is missing.
2. the failing CREATE ... VIEW `t3` is included
3. the statements are to this extend reordered

If I would replay this master binary log somewhere
(I guess the slave does this), I would get
-----------------------------------------------------
name | type
t1   | non temporary table
t2   | non temporary table
t2   | temporary table which hides the non temporary t2
     | in SELECT, INSERT, SHOW etc.       !!!!!!!!!!
t3   | non temporary table
A final
   CREATE ... VIEW `t3` AS SELECT * FROM t2
could depending on order of runtime checks come up with
- check of object name to be created first
  Table 't3' already exists
- check of object structure definition first
  View's SELECT refers to a temporary table 't2'
  !! This is what the slave gets !!

"temporary table which hides the non temporary t2"

.. I think this is related: http://bugs.mysql.com/bug.php?id=39850
.. it should not be possible to have tables with same name/identifier

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87388

3183 Li-Bing.Song@sun.com	2009-10-20
      BUG#47418 RBR fails, failure with mixup of base/temporary/view 
      
      'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as a TEMPORARY
      table if the table exists as a temporary table.
      
      With this patch, in RBR, 'CREATE TABLE IF NOT EXIST ... SELECT' statement will not be 
      binlogged when the existing table is a temporary table.

The reason for this bug is that

  CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t3;

is logged as CREATE TEMPORARY TABLE. This is a duplicate of BUG#47442.

It is normal that DROP TABLE t2 is not logged, because it drops the temporary table, and temporary tables are not logged when binlog_format=row.

I'm not sure if it is a bug that "CREATE VIEW t3 AS SELECT * FROM t2" is logged despite it fails. Note that it implicitly commits any ongoing transaction, so it does have side effects even if it fails. It does not break replication, since the expected error code is logged. However, it may be unnecessary to write the failing CREATE VIEW, because (at least after WL#2687) the implicit commit is logged as an explicit commit.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89408

3694 Li-Bing.Song@sun.com	2009-11-05
      BUG#47418 RBR fails, failure with mixup of base/temporary/view TABLE DDL
      
      Base table is not in the same name space with temporary, but operation
      of 'CREATE TABLE ... SELECT' statement gives warning and inserts data into 
      the temporary table which has the same name. Because of this, the statement
      is binlogged as 'CREATE TEMPORARY TABLE ...'.
      
      To resolve this bug, the operation of 'CREATE TABLE ... SELECT' will skip 
      temporary table when opening and locking tables.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91234

3203 Li-Bing.Song@sun.com	2009-11-22
      BUG#47418 RBR fails, failure with mixup of base/temporary/view 
      
      'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as a TEMPORARY
      table if the table exists as a temporary table. This is caused by that the
      temporary table is opened and the results of the 'SELECT' is inserted into
      the temporary table when a temporary exists with the same name.
      
      After the patch for this bug, the base table is created and the results of
      the 'SELECT' are inserted into it, even though a temporary table exists with
      the same name and the statement is still binlogged as a base table.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/93413

3203 Li-Bing.Song@sun.com	2009-12-10
      BUG#47418 RBR fails, failure with mixup of base/temporary/view 
      
      Before this patch, 'CREATE TABLE IF NOT EXIST ... SELECT' statement was 
      binlogged as a TEMPORARY
      table if the table existed as a temporary table. This was caused by that the
      temporary table was opened and the results of the 'SELECT' was inserted into
      the temporary table when a temporary table existed with the same name.
      
      After the patch for this bug, the base table is created and the results of
      the 'SELECT' are inserted into it, even though a temporary table exists with
      the same name and the statement is still binlogged as a base table.

Sent review for the most recent version of patch by e-mail.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/95934

3203 Li-Bing.Song@sun.com	2010-01-05
      BUG#47418 RBR fails, failure with mixup of base/temporary/view 
      
      'CREATE TABLE IF NOT EXISTS ... SELECT' statement were causing 'CREATE
      TEMPORARY TABLE ...' to be written to the binary log in row-based 
      mode (a.k.a. RBR), when there was a temporary table with the same name.
      Because the 'CREATE TABLE ... SELECT' statement was executed as 
      'INSERT ... SELECT' into the temporary table. Since in RBR mode no 
      other statements related to temporary tables are written into binary log,
      this sometimes broke replication.
      
      This patch changes behavior of 'CREATE TABLE [IF NOT EXISTS] ... SELECT ...'.
      it ignores existence of temporary table with the 
      same name as table being created and is interpreted
      as attempt to create/insert into base table. This makes behavior of
      'CREATE TABLE [IF NOT EXISTS] ... SELECT' consistent with
      how ordinary 'CREATE TABLE' and 'CREATE TABLE ... LIKE' behave.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/95935

3203 Li-Bing.Song@sun.com	2010-01-05
      BUG#47418 RBR fails, failure with mixup of base/temporary/view 
      
      'CREATE TABLE IF NOT EXISTS ... SELECT' statement were causing 'CREATE
      TEMPORARY TABLE ...' to be written to the binary log in row-based 
      mode (a.k.a. RBR), when there was a temporary table with the same name.
      Because the 'CREATE TABLE ... SELECT' statement was executed as 
      'INSERT ... SELECT' into the temporary table. Since in RBR mode no 
      other statements related to temporary tables are written into binary log,
      this sometimes broke replication.
      
      This patch changes behavior of 'CREATE TABLE [IF NOT EXISTS] ... SELECT ...'.
      it ignores existence of temporary table with the 
      same name as table being created and is interpreted
      as attempt to create/insert into base table. This makes behavior of
      'CREATE TABLE [IF NOT EXISTS] ... SELECT' consistent with
      how ordinary 'CREATE TABLE' and 'CREATE TABLE ... LIKE' behave.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/95936

3203 Li-Bing.Song@sun.com	2010-01-05
      BUG#47418 RBR fails, failure with mixup of base/temporary/view 
      
      'CREATE TABLE IF NOT EXISTS ... SELECT' statement were causing 'CREATE
      TEMPORARY TABLE ...' to be written to the binary log in row-based 
      mode (a.k.a. RBR), when there was a temporary table with the same name.
      Because the 'CREATE TABLE ... SELECT' statement was executed as 
      'INSERT ... SELECT' into the temporary table. Since in RBR mode no 
      other statements related to temporary tables are written into binary log,
      this sometimes broke replication.
      
      This patch changes behavior of 'CREATE TABLE [IF NOT EXISTS] ... SELECT ...'.
      it ignores existence of temporary table with the 
      same name as table being created and is interpreted
      as attempt to create/insert into base table. This makes behavior of
      'CREATE TABLE [IF NOT EXISTS] ... SELECT' consistent with
      how ordinary 'CREATE TABLE' and 'CREATE TABLE ... LIKE' behave.

It is OK to push the patch after implementing minor adjustments requests for which were sent by e-mail.

The bug is fixed with the following resolution:
Temporary table and base table are not in the same name space. So when executing 'CREATE TABLE [IF NOT EXISTS]' t1 SELECT ...', even though a temporary table exists with the same name, the base table shall be created and the results of 'SELECT...' part are always insert into the base table.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/97170

3317 Li-Bing.Song@sun.com	2010-01-16
      BUG#47418 RBR fails, failure with mixup of base/temporary/view 
      
      'CREATE TABLE IF NOT EXISTS ... SELECT' statement were causing 'CREATE
      TEMPORARY TABLE ...' to be written to the binary log in row-based 
      mode (a.k.a. RBR), when there was a temporary table with the same name.
      Because the 'CREATE TABLE ... SELECT' statement was executed as 
      'INSERT ... SELECT' into the temporary table. Since in RBR mode no 
      other statements related to temporary tables are written into binary log,
      this sometimes broke replication.
      
      This patch changes behavior of 'CREATE TABLE [IF NOT EXISTS] ... SELECT ...'.
      it ignores existence of temporary table with the 
      same name as table being created and is interpreted
      as attempt to create/insert into base table. This makes behavior of
      'CREATE TABLE [IF NOT EXISTS] ... SELECT' consistent with
      how ordinary 'CREATE TABLE' and 'CREATE TABLE ... LIKE' behave.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/97171

3317 Li-Bing.Song@sun.com	2010-01-16
      BUG#47418 RBR fails, failure with mixup of base/temporary/view 
      
      'CREATE TABLE IF NOT EXISTS ... SELECT' statement were causing 'CREATE
      TEMPORARY TABLE ...' to be written to the binary log in row-based 
      mode (a.k.a. RBR), when there was a temporary table with the same name.
      Because the 'CREATE TABLE ... SELECT' statement was executed as 
      'INSERT ... SELECT' into the temporary table. Since in RBR mode no 
      other statements related to temporary tables are written into binary log,
      this sometimes broke replication.
      
      This patch changes behavior of 'CREATE TABLE [IF NOT EXISTS] ... SELECT ...'.
      it ignores existence of temporary table with the 
      same name as table being created and is interpreted
      as attempt to create/insert into base table. This makes behavior of
      'CREATE TABLE [IF NOT EXISTS] ... SELECT' consistent with
      how ordinary 'CREATE TABLE' and 'CREATE TABLE ... LIKE' behave.

Pushed into mysql-5.1-bugteam and merged into mysql-pe

Pushed into 5.1.44 (revid:joro@sun.com-20100204101444-2j32mhqroo0iiio6) (version source revid:li-bing.song@sun.com-20100116074424-yore75rkekzizh8j) (merge vers: 5.1.43) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100127193504-tssbt5ia6vt11rme) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alexey.kopytov@sun.com-20100123210923-lx4o1ettww9fdkqk) (merge vers: 5.5.2-m2) (pib:16)

Documented bugfix in the 5.1.44, 5.5.2, and 6.0.14 changelogs, as follows:

      When using row-based logging, the statement CREATE TABLE t IF NOT 
      EXIST ... SELECT was logged as CREATE TEMPORARY TABLE t IF NOT 
      EXIST ... SELECT if t already existed as a temporary table. This 
      was caused by the fact that the temporary table was opened and 
      the results of the SELECT were inserted into it when a temporary 
      table existed having the same name.
      
      Now, when this statement is executed, t is created as a base table, 
      the results of the SELECT are inserted into it -- even if there 
      already exists a temporary table having the same name -- and the 
      statement is logged correctly.

Closed.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

No new changelog entries required. Closed.