MySQL Bugs: #55616: Killing thread or query during CREATE IF NOT EXISTS makes slave SQL thread abort

Bug #55616	Killing thread or query during CREATE IF NOT EXISTS makes slave SQL thread abort
Submitted:	28 Jul 2010 21:13	Modified:	15 Oct 2010 14:06
Reporter:	Elena Stepanova	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:
If a connection or a query is killed while executing CREATE TABLE IF NOT EXISTS t SELECT ... with already existing table t, the CREATE statement is written into the binary log with error_code ER_SERVER_SHUTDOWN or ER_QUERY_INTERRUPTED, correspondingly, which makes slave SQL thread abort with "Query partially completed on the master..."

Not reproducible on old 5.1, or 5.5, or the new 5.1 when the table does not exist.

How to repeat:
--source include/master-slave.inc

--connection slave
SET GLOBAL binlog_format = MIXED;
STOP SLAVE;
START SLAVE;

--connection master1
SET binlog_format = MIXED;

let $con_id = `SELECT CONNECTION_ID()`;

CREATE TABLE t ( i INT );
send CREATE TABLE IF NOT EXISTS t
  AS SELECT SLEEP(3);

--connection master
let $wait_timeout = 3;
let $show_statement = SHOW PROCESSLIST;
let $field = State;
let $condition = = 'User sleep';
--source include/wait_show_condition.inc

eval KILL $con_id;

--echo # Table contents on master:
SELECT * FROM t;

--sync_slave_with_master

--echo # Table contents on slave (if we get here):
SELECT * FROM t;

SET GLOBAL binlog_format = @@binlog_format;

--connection master

DROP TABLE t;

--exit

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

3476 Li-Bing.Song@sun.com	2010-07-30
      Postfix for WL#5370
      It fixs Bug#55598 and Bug#55616
      
      Add test for Bug#44498.
      Add parameter errcode for binlog_show_create_table function, and set right
      error code to it.
      Confine select_create::write_to_binlog on SBR.

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)

This bug appears in no 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 per Paul's comment above.