MySQL Bugs: #55499: Wrong binlog(Repl. breaks) with 'CREATE TABLE IF NOT EXISTS' & tbl prefix schema

Bug #55499	Wrong binlog(Repl. breaks) with 'CREATE TABLE IF NOT EXISTS' & tbl prefix schema
Submitted:	23 Jul 2010 8:13	Modified:	15 Oct 2010 13:50
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1-wl5370	OS:	Any
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
While testing replication for WL#5370 with
mysql-5.1-wl5370 tree(dated 20jul)
in the scenario: New Master (mysql-5.1-wl5370) -> New Slave (mysql-5.1-wl5370)

Replication breaks due to wrong binlog entry(in INSERT stmt. table name not prefixed with schema name) and slave tries to use the database in 'USE' above.

For stmt:
CREATE TABLE IF NOT EXISTS testdb_wl5370.t1_1 SELECT 'hello' AS a;

Slave error:
1146    Error 'Table 'jettest_database.t1_1' doesn't exist' on query. Default database: 'jettest_database'. Query: 'INSERT INTO `t1_1` (`a`) SELECT 'hello' AS a' 

Binlog entry:
# at 1235 
#100722 17:41:45 server id 1  end_log_pos 1375  Query   thread_id=8     exec_time=0     error_code=0 
use jettest_database/*!*/; 
SET TIMESTAMP=1279800705/*!*/; 
CREATE TABLE IF NOT EXISTS testdb_wl5370.t1_1 SELECT 'hello' AS a 
/*!*/; 
# at 1375 
#100722 17:41:45 server id 1  end_log_pos 1479  Query   thread_id=8     exec_time=0     error_code=0 
SET TIMESTAMP=1279800705/*!*/; 
DROP TABLE testdb_wl5370.t1_1 
/*!*/; 
# at 1479 
#100722 17:41:45 server id 1  end_log_pos 1601  Query   thread_id=8     exec_time=0     error_code=0 
SET TIMESTAMP=1279800705/*!*/; 
CREATE TABLE testdb_wl5370.t1_1 (a VARCHAR(10)) 
/*!*/; 
# at 1601 
#100722 17:41:45 server id 1  end_log_pos 1681  Query   thread_id=8     exec_time=0     error_code=0 
SET TIMESTAMP=1279800705/*!*/; 
BEGIN 
/*!*/; 
# at 1681 
#100722 17:41:45 server id 1  end_log_pos 1840  Query   thread_id=8     exec_time=0     error_code=0 
SET TIMESTAMP=1279800705/*!*/; 
CREATE TABLE IF NOT EXISTS `testdb_wl5370`.`t1_1` ( 
  `a` varchar(10) DEFAULT NULL 
) 
/*!*/; 
# at 1840 
#100722 17:41:45 server id 1  end_log_pos 1959  Query   thread_id=8     exec_time=0     error_code=0 
SET TIMESTAMP=1279800705/*!*/; 
INSERT INTO `t1_1` (`a`) SELECT 'hello' AS a 
/*!*/; 
# at 1959 
#100722 17:41:45 server id 1  end_log_pos 2040  Query   thread_id=8     exec_time=0     error_code=0 
SET TIMESTAMP=1279800705/*!*/; 
COMMIT 

How to repeat:
As mentioned in the description.

The patch is http://lists.mysql.com/commits/114491.
It was pushed into mysql-5.1-wl5370 as a postfix.
revision-id: li-bing.song@sun.com-20100728031656-mw3qixe5mwl8xswl

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

3479 Li-Bing.Song@sun.com	2010-08-17
      WL#5370 Keep forward-compatibility when changing
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#55474, BUG#55499, BUG#55598, BUG#55616 and BUG#55777 are fixed
      in this patch too.
      
      This is the 5.1 part.
      It implements:
      - if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS
        and INSERT ... SELECT
      
      - Insert nothing and binlog nothing on master if the existing object
        is a view. It only generates a warning that table already exists.
     @ sql/sql_class.h
        Declare virtual function write_to_binlog() for select_insert.
        It's used to binlog 'create select'
     @ sql/sql_insert.cc
        Implement write_to_binlog();
        Use write_to_binlog() instead of binlog_query() to binlog the statement.
        if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS
        and INSERT ... SELECT
     @ sql/sql_lex.h
        Declare create_select_start_with_brace and create_select_pos.
        They are helpful for binlogging 'create select'
     @ sql/sql_parse.cc
        Do nothing on master if the existing object is a view.
     @ sql/sql_yacc.yy
        Record the relative postion of 'SELECT' in the 'CREATE ...SELECT' statement.
        Record whether there is a '(' before the 'SELECT' clause.

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

3484 Li-Bing.Song@sun.com	2010-08-18
      WL#5370 Keep forward-compatibility when changing
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#55474, BUG#55499, BUG#55598, BUG#55616 and BUG#55777 are fixed
      in this patch too.
      
      This is the 5.1 part.
      It implements:
      - if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS
        and INSERT ... SELECT
      
      - Insert nothing and binlog nothing on master if the existing object
        is a view. It only generates a warning that table already exists.
     @ mysql-test/r/trigger.result
        Ather this patch, 'CREATE TABLE IF NOT EXISTS ... SELECT' will not
        insert anything if the creating table already exists and is a view.
     @ sql/sql_class.h
        Declare virtual function write_to_binlog() for select_insert.
        It's used to binlog 'create select'
     @ sql/sql_insert.cc
        Implement write_to_binlog();
        Use write_to_binlog() instead of binlog_query() to binlog the statement.
        if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS
        and INSERT ... SELECT
     @ sql/sql_lex.h
        Declare create_select_start_with_brace and create_select_pos.
        They are helpful for binlogging 'create select'
     @ sql/sql_parse.cc
        Do nothing on master if the existing object is a view.
     @ sql/sql_yacc.yy
        Record the relative postion of 'SELECT' in the 'CREATE ...SELECT' statement.
        Record whether there is a '(' before the 'SELECT' clause.

It was fixed in the final patch for WL#5370.

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825102234-a3q8x0l7voa13ts3) (version source revid:alik@ibmvm-20100825102234-a3q8x0l7voa13ts3) (merge vers: 5.5.6-m3) (pib:20)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100901130501-4g2k86dub29auj8y) (version source revid:alik@sun.com-20100901130012-9bmmvzcnnw6n5rw6) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100901130614-pgop3m80rmutewxn) (version source revid:alik@sun.com-20100901130033-8k19cjn6n2blm3py) (pib:21)

Bug does not appear in any released version. No changelog entry needed.

Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Reverting to Closed state; see above.