• unknown's avatar
    WL#5370 Keep forward-compatibility when changing · 26391375
    unknown authored
            '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.
    26391375
rpl_create_if_not_exists.result 1.94 KB
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
DROP DATABASE IF EXISTS mysqltest;
CREATE DATABASE IF NOT EXISTS mysqltest;
USE mysqltest;
CREATE TABLE IF NOT EXISTS t(c1 int);
CREATE TABLE IF NOT EXISTS t1 LIKE t;
CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t;
CREATE EVENT IF NOT EXISTS e 
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
DO SELECT now();
DROP DATABASE mysqltest;
CREATE DATABASE IF NOT EXISTS mysqltest;
USE mysqltest;
CREATE TABLE IF NOT EXISTS t(c1 int);
CREATE TABLE IF NOT EXISTS t1 LIKE t;
CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t;
CREATE EVENT IF NOT EXISTS e 
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
DO SELECT now();
SHOW TABLES in mysqltest;
Tables_in_mysqltest
t
t1
t2
SHOW EVENTS in mysqltest;
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
mysqltest	e	root@localhost	SYSTEM	ONE TIME	#	NULL	NULL	NULL	NULL	SLAVESIDE_DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
DROP DATABASE IF EXISTS mysqltest;
-------------BUG#47418-------------
USE test;
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(c1 INTEGER);
INSERT INTO t3 VALUES(33);
CREATE TEMPORARY TABLE t1(c1 INTEGER);
CREATE TEMPORARY TABLE t2(c1 INTEGER);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1);
CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 FROM t3;
CREATE TABLE t2(c1 INTEGER) SELECT c1 FROM t3;
SELECT * FROM t1;
c1
1
SELECT * FROM t2;
c1
1
SELECT * FROM t1;
c1
33
SELECT * FROM t2;
c1
33
DROP TEMPORARY TABLE t1;
DROP TEMPORARY TABLE t2;
SELECT * FROM t1;
c1
33
SELECT * FROM t2;
c1
33
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;

# Bug#55616 Killing thread or query during CREATE IF NOT EXISTS makes
# slave SQL thread abort

CREATE TABLE t1 ( i INT );
CREATE TABLE IF NOT EXISTS t1
AS SELECT SLEEP(3);
KILL QUERY master1;
DROP TABLE t1;