MySQL Bugs: #49137: Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE

Bug #49137	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
Submitted:	26 Nov 2009 11:43	Modified:	12 Mar 2010 16:43
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	next-mr, 6.0-codebase, 5.1	OS:	Any
Assigned to:	Daogang Qu	CPU Architecture:	Any

Description:
In statement-based or mixed-mode replication, issuing a multi-table DROP TEMPORARY TABLE causes the slave to fail as follows:

091126 14:41:52 [ERROR] Slave SQL: Query caused different errors on master and slave.     Error on master: 'Unknown table '%-.100s'' (1051), Error on slave: 'no error' (0). Default database: 'test'. Query: 'DROP TEMPORARY TABLE t1 , t2', Error_code: 0
091126 14:41:52 [Warning] Slave: Unknown table 't2' Error_code: 1051

How to repeat:
--source include/master-slave.inc
--disable_abort_on_error
CREATE TEMPORARY TABLE t1 ( f1 int );
DROP TEMPORARY TABLE t1 , t2;
--connection default
--sync_slave_with_master

see also bug#49132

Exists in 5.1

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/94663

3271 Dao-Gang.Qu@sun.com	2009-12-17
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      In statement-based or mixed-mode replication, DROP MULTI TEMPORARY TABLE 
      causes different errors on master and slave, when one does not exist. 
      Because the 'Unknown table' error is ignored on slave.
      
      To fix the problem, issue the 'Unknown table' error, if the temporary table 
      does not exist when dropping it on slave.
     @ mysql-test/suite/rpl/r/rpl_drop_temp_table.result
        Test result for bug#49137.
     @ mysql-test/suite/rpl/t/rpl_drop_temp_table.test
        Added test to verify if DROP MULTI TEMPORARY TABLE 
        will cause different errors on master and slave, 
        when one does not exist.
     @ sql/sql_table.cc
        Added code to issue the 'unknown table' error, 
        if the temporary table does not exist when 
        dropping it on slave.

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/94878

3271 Dao-Gang.Qu@sun.com	2009-12-18
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      In statement-based or mixed-mode replication, DROP MULTI TEMPORARY TABLE 
      causes different errors on master and slave, when one does not exist. 
      Because the 'Unknown table' error is ignored on slave.
      
      To fix the problem, issue the 'Unknown table' error, if the temporary table 
      does not exist when dropping it on slave.
     @ mysql-test/suite/rpl/r/rpl_temp_table.result
        Updated for the patch of bug#49137.
     @ mysql-test/suite/rpl/t/rpl_temp_table.test
        Added the test file to verify if DROP MULTI TEMPORARY TABLE
        will cause different errors on master and slave, when one 
        does not exist.
     @ sql/sql_table.cc
        Added code to issue the 'unknown table' error, 
        if the temporary table does not exist when 
        dropping it on slave.

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/95178

3271 Dao-Gang.Qu@sun.com	2009-12-21
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      In statement-based or mixed-mode replication, DROP MULTI TEMPORARY TABLE 
      causes different errors on master and slave, when one does not exist. 
      Because the 'Unknown table' error is ignored on slave.
      
      To fix the problem, issue the 'Unknown table' error, if the temporary table 
      does not exist when dropping it on slave.
     @ mysql-test/suite/rpl/r/rpl_temp_table.result
        Updated for the patch of bug#49137.
     @ mysql-test/suite/rpl/t/rpl_temp_table.test
        Added the test file to verify if DROP MULTI TEMPORARY TABLE
        will cause different errors on master and slave, when one 
        does not exist.
     @ sql/log_event.cc
        Added code to handle above cases which are 
        removed from sql_parse.cc
     @ sql/sql_parse.cc
        Remove the code to check the above cases in log_event.cc.
     @ sql/sql_table.cc
        Added code to issue the 'unknown table' error, 
        if the temporary table does not exist when 
        dropping it on slave.

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/95340

3271 Dao-Gang.Qu@sun.com	2009-12-22
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      In statement-based or mixed-mode replication, DROP MULTI TEMPORARY TABLE 
      causes different errors on master and slave, when one does not exist. 
      Because the 'Unknown table' error is ignored on slave.
      
      To fix the problem, issue the 'Unknown table' error, if the temporary table 
      does not exist when dropping it on slave.
     @ mysql-test/suite/rpl/r/rpl_temp_table.result
        Updated for the patch of bug#49137.
     @ mysql-test/suite/rpl/t/rpl_temp_table.test
        Added the test file to verify if DROP MULTI TEMPORARY TABLE
        will cause different errors on master and slave, when one 
        does not exist.
     @ sql/log_event.cc
        Added code to handle above cases which are 
        removed from sql_parse.cc
     @ sql/sql_parse.cc
        Remove the code to issue the 'Unknown table' error, 
        if the temporary table does not exist when dropping 
        it on slave. The above cases which are decribed in 
        comments will be handled later in log_event.cc.

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/95346

3271 Dao-Gang.Qu@sun.com	2009-12-22
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      In statement-based or mixed-mode replication, DROP MULTI TEMPORARY TABLE 
      causes different errors on master and slave, when one does not exist. 
      Because the 'Unknown table' error is ignored on slave.
      
      To fix the problem, issue the 'Unknown table' error, if the temporary table 
      does not exist when dropping it on slave.
     @ mysql-test/suite/rpl/r/rpl_temp_table.result
        Updated for the patch of bug#49137.
     @ mysql-test/suite/rpl/t/rpl_temp_table.test
        Added the test file to verify if DROP MULTI TEMPORARY TABLE
        will cause different errors on master and slave, when one 
        does not exist.
     @ sql/log_event.cc
        Added code to handle above cases which are 
        removed from sql_parse.cc
     @ sql/sql_parse.cc
        Remove the code to issue the 'Unknown table' error, 
        if the temporary table does not exist when dropping 
        it on slave. The above cases which are decribed in 
        comments will be handled later in log_event.cc.

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/95449

2822 Dao-Gang.Qu@sun.com	2009-12-23
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
            
      In statement-based or mixed-mode replication, DROP MULTI TEMPORARY TABLE 
      causes different errors on master and slave, when one does not exist. 
      Because the 'Unknown table' error is ignored on slave.
            
      To fix the problem, issue the 'Unknown table' error, if the temporary table 
      does not exist when dropping it on slave.
     @ mysql-test/r/rpl_drop_temp.result
        Updated for the patch of bug#49137.
     @ mysql-test/t/rpl_drop_temp.test
        Added the test file to verify if DROP MULTI TEMPORARY TABLE
        will cause different errors on master and slave, when one 
        does not exist.
     @ sql/log_event.cc
        Added code to handle above cases which are 
        removed from sql_parse.cc
     @ sql/sql_parse.cc
        Remove the code to issue the 'Unknown table' error, 
        if the temporary table does not exist when dropping 
        it on slave. The above cases which are decribed in 
        comments will be handled later in log_event.cc.

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/95454

2822 Dao-Gang.Qu@sun.com	2009-12-23
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
            
      In statement-based or mixed-mode replication, use DROP TEMPORARY TABLE
      to drop multiple tables causes different errors on master and slave, 
      when one or more of these tables do not exist. Because when executed
      on slave, it would automatically add IF EXISTS to the query to ignore
      all ER_BAD_TABLE_ERROR errors.
      
      To fix the problem, do not add IF EXISTS when executing DROP TEMPORARY
      TABLE on the slave, and clear the ER_BAD_TABLE_ERROR error after
      execution if the query does not expect any errors.
     @ mysql-test/r/rpl_drop_temp.result
        Updated for the patch of bug#49137.
     @ mysql-test/t/rpl_drop_temp.test
        Added the test file to verify if DROP MULTI TEMPORARY TABLE
        will cause different errors on master and slave, when one or
        more of these tables do not exist.
     @ sql/log_event.cc
        Added code to handle above cases which are
        removed from sql_parse.cc
     @ sql/sql_parse.cc
        Remove the code to issue the 'Unknown table' error,
        if the temporary table does not exist when dropping
        it on slave. The above cases decribed in comments
        will be handled later in log_event.cc.

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/95460

2822 Dao-Gang.Qu@sun.com	2009-12-23
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
            
      In statement-based or mixed-mode replication, use DROP TEMPORARY TABLE
      to drop multiple tables causes different errors on master and slave, 
      when one or more of these tables do not exist. Because when executed
      on slave, it would automatically add IF EXISTS to the query to ignore
      all ER_BAD_TABLE_ERROR errors.
      
      To fix the problem, do not add IF EXISTS when executing DROP TEMPORARY
      TABLE on the slave, and clear the ER_BAD_TABLE_ERROR error after
      execution if the query does not expect any errors.
     @ mysql-test/r/rpl_drop_temp.result
        Updated for the patch of bug#49137.
     @ mysql-test/t/rpl_drop_temp.test
        Added the test file to verify if DROP MULTI TEMPORARY TABLE
        will cause different errors on master and slave, when one or
        more of these tables do not exist.
     @ sql/log_event.cc
        Added code to handle above cases which are
        removed from sql_parse.cc
     @ sql/sql_parse.cc
        Remove the code to issue the 'Unknown table' error,
        if the temporary table does not exist when dropping
        it on slave. The above cases decribed in comments
        will be handled later in log_event.cc.

Pushed to mysql-5.0-bugteam, mysql-5.1-bugteam and mysql-pe.

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/96055

2835 Dao-Gang.Qu@sun.com	2010-01-06
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      Fixed valgrind failure on PB2.
     @ sql/log_event.cc
        Added code to fix valgrind failure on PB2.

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/96056

3304 Dao-Gang.Qu@sun.com	2010-01-06 [merge]
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      Fixed valgrind failure on PB2.
     @ sql/log_event.cc
        Added code to fix valgrind failure on PB2.

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/96057

3814 Dao-Gang.Qu@sun.com	2010-01-06 [merge]
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      Fixed valgrind failure on PB2.
     @ sql/log_event.cc
        Added code to fix valgrind failure on PB2.

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/96064

2836 Dao-Gang.Qu@sun.com	2010-01-06
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      Fixed valgrind failure on PB2.
     @ sql/log_event.cc
        Added code to fix valgrind failure on PB2.

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/96065

3305 Dao-Gang.Qu@sun.com	2010-01-06 [merge]
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      Fixed valgrind failure on PB2.
     @ sql/log_event.cc
        Added code to fix valgrind failure on PB2.

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/96066

3815 Dao-Gang.Qu@sun.com	2010-01-06 [merge]
      Bug #49137  	Replication failure on SBR/MBR + multi-table DROP TEMPORARY TABLE
      
      Fixed valgrind failure on PB2.
     @ sql/log_event.cc
        Added code to fix valgrind failure on PB2.

Pushed into 5.0.90 (revid:joro@sun.com-20100114082402-05fod2h6z9x9wok8) (version source revid:dao-gang.qu@sun.com-20100106051240-cj1ke8k17i9kjabg) (merge vers: 5.0.89) (pib:16)

No changelog entry needed.

Setting report to NDI pending push to 5.1.x+.

Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:dao-gang.qu@sun.com-20100106052806-hce1fzky3fodiwgq) (merge vers: 5.1.42) (pib:16)

Setting report to NDI pending push to 5.5.x+.

Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100119163614-172adculixyu26j5) (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-20100115112653-e3a24041ag1cv6v3) (merge vers: 5.5.1-m2) (pib:16)

Setting report to Need Merge pending push to Celosia.

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 changelog entry needed.