Bug #55478 Row events wrongly apply on the temporary table of the same name
Submitted: 22 Jul 2010 11:56 Modified: 27 Jan 2011 20:08
Reporter: Libing Song Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5, 5.1 OS:Any
Assigned to: Libing Song CPU Architecture:Any

[22 Jul 2010 11:56] Libing Song
Description:
Row event is only generated on row mode. Only the changes on base table can generate row events, as the changes on temporary table are never binlogged on row mode. When both base table and temporary table exist with the same name(example t1), all statements refer to the table name(t1) should operate on the temporary table. So, theoretically, there should no row event generated.

But there is a rare occasion from 'CREATE TABLE ... SELECT' statement. The result of SELECT clause inserts into the base table and binlogged as row event, even though there is a temporary table of the same name. Slave wrongly applies the row events on the temporary table. Slave will abort if the base table and the temporary table have different structure.

How to repeat:
source include/master-slave.inc;
SET BINLOG_FORMAT='STATEMENT';
CREATE TEMPORARY TABLE t1(c1 INT);
CREATE TABLE t1(c2 char(10));
INSERT INTO t1 VALUES(22);
SET BINLOG_FORMAT='ROW';
CREATE TABLE IF NOT EXISTS t1 SELECT 1;
CREATE TABLE IF NOT EXISTS t1 SELECT 1;
SELECT * FROM t1;
DROP TEMPORARY TABLE t1;
SELECT * FROM t1;
sync_slave_with_master;

Suggested fix:
Open only base tables for row event.
[22 Jul 2010 11:59] Libing Song
see Bug#47899.
[22 Jul 2010 12:42] Sveta Smirnova
Thank you for the report.

Verified as described. Looks like version 5.1 is not affected.
[22 Jul 2010 14:15] Luis Manuel Oliveira Soares
Hi Sveta, Li-Bing, I get a failure in 5.1 as well:

"Table definition on master and slave does not match: Column 0 type mismatch - received type 254, test.t1 has type 3"

if I use MIXED, create the table and then issue a statement that switches to
ROW. Sample test case:

source include/master-slave.inc;
SET BINLOG_FORMAT='MIXED';
CREATE TEMPORARY TABLE t1(c1 INT);
CREATE TABLE t1(c2 char(10));
INSERT INTO t1 SELECT RAND();
CREATE TABLE IF NOT EXISTS t1 SELECT 1;
CREATE TABLE IF NOT EXISTS t1 SELECT 1;
SELECT * FROM t1; 
DROP TEMPORARY TABLE t1; 
SELECT * FROM t1; 
sync_slave_with_master;

Sveta, can you please re-verify this against 5.1?
[23 Jul 2010 5:28] Sveta Smirnova
Luís,

thank you for the test. It fails with 5.1 too.
[5 Aug 2010 4:01] Zhenxing He
See also bug#55709
[15 Oct 2010 8:21] Bugs System
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/120810

3324 Li-Bing.Song@sun.com	2010-10-15
      Bug#55478 Row events wrongly apply on the temporary table of the same name
      
      Rows events were applied wrongly on the temporary table with the same name.
      But rows events are generated only for base tables. As temporary
      table's data never be binlogged on row mode. Normally, base table of the
      same name cannot be updated if a temporary table has the same name.
      But there are some cases which can generate rows events on
      the base table of same name.
      
      After this patch, slave opens only base table when applying a rows event.
[18 Oct 2010 6:46] Bugs System
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/120927

3324 Li-Bing.Song@sun.com	2010-10-18
      Bug#55478 Row events wrongly apply on the temporary table of the same name
      
      Rows events were applied wrongly on the temporary table with the same name.
      But rows events are generated only for base tables. As temporary
      table's data never be binlogged on row mode. Normally, base table of the
      same name cannot be updated if a temporary table has the same name.
      But there are two cases which can generate rows events on 
      the base table of same name.
      
      Case1: 'CREATE TABLE ... SELECT' statement.
      In mixed format, it will generate rows events if it is unsafe.
      
      Case2: Drop a transactional temporary table in a transaction.
      BEGIN;
      DROP TEMPORARY TABLE t1;       # t1 is a InnoDB table
      INSERT INTO t1 VALUES(rand()); # t1 is a MyISAM table
      COMMIT;
      'DROP TEMPORARY TABLE' will be put in the transaction cache and
      binlogged after the rows events generated by the 'INSERT' statement.
      
      After this patch, slave opens only base table when applying a rows event.
[19 Oct 2010 17:33] Omer Barnir
triage: changing tag from CHECKED to SR51MRU based on low risk and feedback from development
[21 Oct 2010 5:44] Bugs System
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/121432

3544 Li-Bing.Song@sun.com	2010-10-21
      Bug#55478 Row events wrongly apply on the temporary table of the same name
      
      Rows events were applied wrongly on the temporary table with the same name.
      But rows events are generated only for base tables. As temporary
      table's data never be binlogged on row mode. Normally, base table of the
      same name cannot be updated if a temporary table has the same name.
      But there are two cases which can generate rows events on 
      the base table of same name.
            
      Case1: 'CREATE TABLE ... SELECT' statement.
      In mixed format, it will generate rows events if it is unsafe.
            
      Case2: Drop a transactional temporary table in a transaction
             (happens only on 5.5+).
      BEGIN;
      DROP TEMPORARY TABLE t1;       # t1 is a InnoDB table
      INSERT INTO t1 VALUES(rand()); # t1 is a MyISAM table
      COMMIT;
      'DROP TEMPORARY TABLE' will be put in the transaction cache and
      binlogged after the rows events generated by the 'INSERT' statement.
            
      After this patch, slave opens only base table when applying a rows event.
[21 Oct 2010 6:02] Libing Song
Pushed into mysql-5.1-bugteam and merged into mysql-5.5-bugteam
and mysql-trunk-merge.
[1 Nov 2010 12:28] Jon Stephens
Documented bugfix as follows in the 5.1.53 changelog:

        If there exist both a temporary table and a non-temporary table
        having the same, updates normally apply only to the temporary
        table, with the exception of a CREATE TABLE ... SELECT statement
        that creates a non-temporary table having the same name as an
        existing temporary table. When such a statement was replicated
        using the MIXED logging format, and the statement was unsafe,
        updates were misapplied to the temporary table.

Changelog entry for 5.5.8 includes above plus the following:

        Updates were also applied wrongly in the case where a temporary
        table that used a transactional storage engine was dropped
        inside a transaction, followed by updates within the same
        transaction to a non-temporary table having the same name.

Set Need Merge status, waiting for merge to -trunk.
[4 Nov 2010 17:29] Jon Stephens
Already documented in the 5.5.8 changelog; still waiting for merge to trunk.
[13 Nov 2010 16:19] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:38] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[15 Nov 2010 13:26] Jon Stephens
Does not appear in a 5.6 release; no additional changelog entries required.

Closed.
[18 Nov 2010 15:53] Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[16 Dec 2010 22:32] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[13 Jan 2011 3:22] James Day
Please add the missing word "name" before the comma in the release notes for this:

 If there exist both a temporary table and a non-temporary table
 having the same, updates normally apply only to the temporary
[27 Jan 2011 20:08] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.