summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/extra/rpl_tests/rpl_stm_create_if_not_exists.test235
-rw-r--r--mysql-test/include/rpl_diff_tables.inc1
-rw-r--r--mysql-test/r/trigger.result8
-rw-r--r--mysql-test/suite/rpl/r/rpl_create_if_not_exists.result9
-rw-r--r--mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result7
-rw-r--r--mysql-test/suite/rpl/r/rpl_row_create_table.result6
-rw-r--r--mysql-test/suite/rpl/r/rpl_stm_create_if_not_exists.result704
-rw-r--r--mysql-test/suite/rpl/t/rpl_create_if_not_exists.test27
-rw-r--r--mysql-test/suite/rpl/t/rpl_row_create_table.test13
-rw-r--r--mysql-test/suite/rpl/t/rpl_stm_create_if_not_exists.test14
-rw-r--r--sql/sql_class.h8
-rw-r--r--sql/sql_insert.cc122
-rw-r--r--sql/sql_lex.h17
-rw-r--r--sql/sql_parse.cc19
-rw-r--r--sql/sql_yacc.yy28
15 files changed, 1191 insertions, 27 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_stm_create_if_not_exists.test b/mysql-test/extra/rpl_tests/rpl_stm_create_if_not_exists.test
new file mode 100644
index 00000000000..65d2483dc4b
--- /dev/null
+++ b/mysql-test/extra/rpl_tests/rpl_stm_create_if_not_exists.test
@@ -0,0 +1,235 @@
+--echo
+--echo
+connection master;
+
+if ($is_temporary)
+{
+ --let $_temporary=TEMPORARY
+}
+
+CREATE TABLE t2(c1 INT, c2 char(10));
+INSERT INTO t2 VALUES(1, 'abc'), (2, 'abc');
+
+--echo
+--echo # The original query should be binlogged if the table does not exist.
+--echo # ------------------------------------------------------------------
+--echo
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+eval CREATE $_temporary TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY)
+ SELECT 'abc' AS c3, 1 AS c4;
+source include/show_binlog_events.inc;
+
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+
+--echo
+--echo # The statement should be binlogged as two events. one is
+--echo # 'CREATE $_temporary TABLE IF NOT EXISTS ..', another one is
+--echo # 'INSERT ... SELECT'.
+--echo # ------------------------------------------------------------------
+--echo
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ SELECT 'abc', 2;
+source include/show_binlog_events.inc;
+
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+
+--echo
+--echo # Verify if it can be binlogged with right database name when the table
+--echo # is not in the default database
+--echo
+--disable_warnings
+DROP DATABASE IF EXISTS db1;
+--enable_warnings
+CREATE DATABASE db1;
+USE db1;
+
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+eval CREATE $_temporary TABLE IF NOT EXISTS test.t1
+ SELECT 'abc', 20;
+source include/show_binlog_events.inc;
+
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+USE test;
+DROP DATABASE db1;
+
+--echo
+--echo # It should be binlogged as 'REPLACE ... SELECT'
+--echo # if the original statement has option REPLACE
+--echo
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ REPLACE SELECT '123', 2;
+source include/show_binlog_events.inc;
+
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+
+--echo
+--echo # It should be binlogged as 'INSERT IGNORE... SELECT'
+--echo # if the original statement has option IGNORE
+--echo
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ IGNORE SELECT '123', 2;
+source include/show_binlog_events.inc;
+
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+
+--echo
+--echo # Nothing should be binlogged if error happens and no any row is inserted
+--echo
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+--error ER_DUP_ENTRY
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ SELECT '123', 2;
+source include/show_binlog_events.inc;
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+
+--echo
+--echo # Verify it can binlog well when there are some braces('(')
+--echo
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ (SELECT '123', 3) UNION (SELECT '123', 4);
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ REPLACE (SELECT 'abc', 3) UNION (SELECT 'abc', 4);
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ IGNORE (SELECT '123', 3) UNION (SELECT '123', 4);
+source include/show_binlog_events.inc;
+
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+
+if (!$is_temporary)
+{
+ --echo
+ --echo # Throw a warning that table already exists and don't insert anything
+ --echo
+ CREATE VIEW t3 AS SELECT * FROM t2;
+ let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+ CREATE TABLE IF NOT EXISTS t3
+ SELECT '123', 2;
+ source include/show_binlog_events.inc;
+ DROP VIEW t3;
+}
+
+--echo
+--echo # The statement can be binlogged correctly when it is in a SP/EVENT/TRIGGER
+--echo
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+eval CREATE PROCEDURE p1(IN a INT)
+ CREATE $_temporary TABLE IF NOT EXISTS t1 SELECT '123', a;
+
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+call p1(500);
+call p1(600);
+source include/show_binlog_events.inc;
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+DROP PROCEDURE p1;
+
+--echo
+--echo # The statement can be binlogged correctly when it is in a prepared statement
+--echo
+eval PREPARE stm FROM "CREATE $_temporary TABLE IF NOT EXISTS t1 SELECT '123', ?";
+
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+SET @a= 700;
+EXECUTE stm USING @a;
+SET @a= 800;
+EXECUTE stm USING @a;
+source include/show_binlog_events.inc;
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+
+--echo
+--echo # The statement can be binlogged correctly when it is in a conditional comment
+--echo
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+--echo # The whole statement in a conditional comment
+eval /*!CREATE $_temporary TABLE IF NOT EXISTS t1
+ SELECT 'abc', 900*/;
+source include/show_binlog_events.inc;
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+--echo
+--echo # There is an long comment before SELECT
+eval /*!CREATE $_temporary /*blabla*/ TABLE IF NOT EXISTS t1
+ SELECT 'abc', 901*/;
+source include/show_binlog_events.inc;
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+--echo
+--echo # Conditional comment starts just from SELECT
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ /*!SELECT 'abc',*/ 902;
+source include/show_binlog_events.inc;
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+--echo
+--echo # Only SELECT keyword is in the conditional comment
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ /*!SELECT*/ /*!'abc',*/ 904;
+source include/show_binlog_events.inc;
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+--echo
+--echo # Conditional comment is after SELECT keyword
+eval CREATE $_temporary TABLE IF NOT EXISTS t1
+ SELECT /*!'abc',*/ 903;
+source include/show_binlog_events.inc;
+let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+--echo
+--echo # Conditional comment ends just before SELECT keyword
+eval /*!CREATE $_temporary TABLE IF NOT EXISTS t1
+ */SELECT 'abc', 905;
+source include/show_binlog_events.inc;
+
+if (!$is_temporary)
+{
+ let $diff_table= test.t1;
+ source include/rpl_diff_tables.inc;
+}
+
+DROP TABLE t2;
+eval DROP $_temporary TABLE t1;
+
diff --git a/mysql-test/include/rpl_diff_tables.inc b/mysql-test/include/rpl_diff_tables.inc
index c3a45578a79..7fc68422c40 100644
--- a/mysql-test/include/rpl_diff_tables.inc
+++ b/mysql-test/include/rpl_diff_tables.inc
@@ -33,3 +33,4 @@ while (`SELECT "XX$_servers" <> "XX"`)
--source include/diff_tables.inc
connection $_slave;
}
+connection $_master;
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index e3c0b0e1dd9..16b165cdc11 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1824,11 +1824,8 @@ Note 1050 Table 'v1' already exists
set @id=last_insert_id();
select * from t1;
id operation
-1 CREATE TABLE ... SELECT, inserting a new key
select * from t1_op_log;
operation
-Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
-After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
truncate t1_op_log;
create table if not exists v1 replace
select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
@@ -1836,13 +1833,8 @@ Warnings:
Note 1050 Table 'v1' already exists
select * from t1;
id operation
-1 CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
select * from t1_op_log;
operation
-Before INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
-Before DELETE, old=CREATE TABLE ... SELECT, inserting a new key
-After DELETE, old=CREATE TABLE ... SELECT, inserting a new key
-After INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
truncate t1;
truncate t1_op_log;
insert into v1 (id, operation)
diff --git a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
index fc53aca5136..e3cddf4c606 100644
--- a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
+++ b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
@@ -65,3 +65,12 @@ c1
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;
diff --git a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result
index 8d0b61cc6d8..de44a1e5d2a 100644
--- a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result
+++ b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result
@@ -19,4 +19,9 @@ master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS t
master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp
master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp
master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp
-master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `tmp2` (
+ `c1` int(11) DEFAULT NULL
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`tmp2` (`c1`) SELECT * FROM tmp
+master-bin.000001 # Query # # COMMIT
diff --git a/mysql-test/suite/rpl/r/rpl_row_create_table.result b/mysql-test/suite/rpl/r/rpl_row_create_table.result
index e480ff3dfe9..c6aa05f9e90 100644
--- a/mysql-test/suite/rpl/r/rpl_row_create_table.result
+++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result
@@ -467,4 +467,10 @@ DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3;
DROP TEMPORARY TABLES t7;
DROP TABLES t4, t5;
DROP TABLES IF EXISTS bug48506_t4;
+CREATE TABLE t1 SELECT 1;
+CREATE TABLE IF NOT EXISTS t1 SELECT 1;
+Warnings:
+Note 1050 Table 't1' already exists
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t1;
end of the tests
diff --git a/mysql-test/suite/rpl/r/rpl_stm_create_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_stm_create_if_not_exists.result
new file mode 100644
index 00000000000..9ae1ef315b6
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_stm_create_if_not_exists.result
@@ -0,0 +1,704 @@
+# WL#5370 Keep forward-compatibility when changing 'CREATE TABLE IF NOT
+# EXISTS ... SELECT' behaviour
+#
+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;
+
+
+CREATE TABLE t2(c1 INT, c2 char(10));
+INSERT INTO t2 VALUES(1, 'abc'), (2, 'abc');
+
+# The original query should be binlogged if the table does not exist.
+# ------------------------------------------------------------------
+
+CREATE TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY)
+SELECT 'abc' AS c3, 1 AS c4;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY)
+SELECT 'abc' AS c3, 1 AS c4
+Comparing tables master:test.t1 and slave:test.t1
+
+# The statement should be binlogged as two events. one is
+# 'CREATE TABLE IF NOT EXISTS ..', another one is
+# 'INSERT ... SELECT'.
+# ------------------------------------------------------------------
+
+CREATE TABLE IF NOT EXISTS t1
+SELECT 'abc', 2;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 2
+master-bin.000001 # Query # # COMMIT
+Comparing tables master:test.t1 and slave:test.t1
+
+# Verify if it can be binlogged with right database name when the table
+# is not in the default database
+
+DROP DATABASE IF EXISTS db1;
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE IF NOT EXISTS test.t1
+SELECT 'abc', 20;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `db1`; CREATE TABLE IF NOT EXISTS `test`.`t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `db1`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 20
+master-bin.000001 # Query # # COMMIT
+Comparing tables master:test.t1 and slave:test.t1
+USE test;
+DROP DATABASE db1;
+
+# It should be binlogged as 'REPLACE ... SELECT'
+# if the original statement has option REPLACE
+
+CREATE TABLE IF NOT EXISTS t1
+REPLACE SELECT '123', 2;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; REPLACE INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 2
+master-bin.000001 # Query # # COMMIT
+Comparing tables master:test.t1 and slave:test.t1
+
+# It should be binlogged as 'INSERT IGNORE... SELECT'
+# if the original statement has option IGNORE
+
+CREATE TABLE IF NOT EXISTS t1
+IGNORE SELECT '123', 2;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT IGNORE INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 2
+master-bin.000001 # Query # # COMMIT
+Comparing tables master:test.t1 and slave:test.t1
+
+# Nothing should be binlogged if error happens and no any row is inserted
+
+CREATE TABLE IF NOT EXISTS t1
+SELECT '123', 2;
+ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+Comparing tables master:test.t1 and slave:test.t1
+
+# Verify it can binlog well when there are some braces('(')
+
+CREATE TABLE IF NOT EXISTS t1
+(SELECT '123', 3) UNION (SELECT '123', 4);
+Warnings:
+Note 1050 Table 't1' already exists
+CREATE TABLE IF NOT EXISTS t1
+REPLACE (SELECT 'abc', 3) UNION (SELECT 'abc', 4);
+Warnings:
+Note 1050 Table 't1' already exists
+CREATE TABLE IF NOT EXISTS t1
+IGNORE (SELECT '123', 3) UNION (SELECT '123', 4);
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) (SELECT '123', 3) UNION (SELECT '123', 4)
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; REPLACE INTO `test`.`t1` (`c3`,`c4`) (SELECT 'abc', 3) UNION (SELECT 'abc', 4)
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT IGNORE INTO `test`.`t1` (`c3`,`c4`) (SELECT '123', 3) UNION (SELECT '123', 4)
+master-bin.000001 # Query # # COMMIT
+Comparing tables master:test.t1 and slave:test.t1
+
+# Throw a warning that table already exists and don't insert anything
+
+CREATE VIEW t3 AS SELECT * FROM t2;
+CREATE TABLE IF NOT EXISTS t3
+SELECT '123', 2;
+Warnings:
+Note 1050 Table 't3' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+DROP VIEW t3;
+
+# The statement can be binlogged correctly when it is in a SP/EVENT/TRIGGER
+
+DROP PROCEDURE IF EXISTS p1;
+CREATE PROCEDURE p1(IN a INT)
+CREATE TABLE IF NOT EXISTS t1 SELECT '123', a;
+call p1(500);
+Warnings:
+Note 1050 Table 't1' already exists
+call p1(600);
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', NAME_CONST('a',500)
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', NAME_CONST('a',600)
+master-bin.000001 # Query # # COMMIT
+Comparing tables master:test.t1 and slave:test.t1
+DROP PROCEDURE p1;
+
+# The statement can be binlogged correctly when it is in a prepared statement
+
+PREPARE stm FROM "CREATE TABLE IF NOT EXISTS t1 SELECT '123', ?";
+SET @a= 700;
+EXECUTE stm USING @a;
+Warnings:
+Note 1050 Table 't1' already exists
+SET @a= 800;
+EXECUTE stm USING @a;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 700
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 800
+master-bin.000001 # Query # # COMMIT
+Comparing tables master:test.t1 and slave:test.t1
+
+# The statement can be binlogged correctly when it is in a conditional comment
+
+# The whole statement in a conditional comment
+/*!CREATE TABLE IF NOT EXISTS t1
+SELECT 'abc', 900*/;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 900*/
+master-bin.000001 # Query # # COMMIT
+
+# There is an long comment before SELECT
+/*!CREATE /*blabla*/ TABLE IF NOT EXISTS t1
+SELECT 'abc', 901*/;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 901*/
+master-bin.000001 # Query # # COMMIT
+
+# Conditional comment starts just from SELECT
+CREATE TABLE IF NOT EXISTS t1
+/*!SELECT 'abc',*/ 902;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc',*/ 902
+master-bin.000001 # Query # # COMMIT
+
+# Only SELECT keyword is in the conditional comment
+CREATE TABLE IF NOT EXISTS t1
+/*!SELECT*/ /*!'abc',*/ 904;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT*/ /*!'abc',*/ 904
+master-bin.000001 # Query # # COMMIT
+
+# Conditional comment is after SELECT keyword
+CREATE TABLE IF NOT EXISTS t1
+SELECT /*!'abc',*/ 903;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT /*!'abc',*/ 903
+master-bin.000001 # Query # # COMMIT
+
+# Conditional comment ends just before SELECT keyword
+/*!CREATE TABLE IF NOT EXISTS t1
+*/SELECT 'abc', 905;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 905
+master-bin.000001 # Query # # COMMIT
+Comparing tables master:test.t1 and slave:test.t1
+DROP TABLE t2;
+DROP TABLE t1;
+
+
+CREATE TABLE t2(c1 INT, c2 char(10));
+INSERT INTO t2 VALUES(1, 'abc'), (2, 'abc');
+
+# The original query should be binlogged if the table does not exist.
+# ------------------------------------------------------------------
+
+CREATE TEMPORARY TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY)
+SELECT 'abc' AS c3, 1 AS c4;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY)
+SELECT 'abc' AS c3, 1 AS c4
+
+# The statement should be binlogged as two events. one is
+# 'CREATE TEMPORARY TABLE IF NOT EXISTS ..', another one is
+# 'INSERT ... SELECT'.
+# ------------------------------------------------------------------
+
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+SELECT 'abc', 2;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 2
+master-bin.000001 # Query # # COMMIT
+
+# Verify if it can be binlogged with right database name when the table
+# is not in the default database
+
+DROP DATABASE IF EXISTS db1;
+CREATE DATABASE db1;
+USE db1;
+CREATE TEMPORARY TABLE IF NOT EXISTS test.t1
+SELECT 'abc', 20;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `db1`; CREATE TEMPORARY TABLE IF NOT EXISTS `test`.`t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `db1`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 20
+master-bin.000001 # Query # # COMMIT
+USE test;
+DROP DATABASE db1;
+
+# It should be binlogged as 'REPLACE ... SELECT'
+# if the original statement has option REPLACE
+
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+REPLACE SELECT '123', 2;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; REPLACE INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 2
+master-bin.000001 # Query # # COMMIT
+
+# It should be binlogged as 'INSERT IGNORE... SELECT'
+# if the original statement has option IGNORE
+
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+IGNORE SELECT '123', 2;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT IGNORE INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 2
+master-bin.000001 # Query # # COMMIT
+
+# Nothing should be binlogged if error happens and no any row is inserted
+
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+SELECT '123', 2;
+ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+
+# Verify it can binlog well when there are some braces('(')
+
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+(SELECT '123', 3) UNION (SELECT '123', 4);
+Warnings:
+Note 1050 Table 't1' already exists
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+REPLACE (SELECT 'abc', 3) UNION (SELECT 'abc', 4);
+Warnings:
+Note 1050 Table 't1' already exists
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+IGNORE (SELECT '123', 3) UNION (SELECT '123', 4);
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) (SELECT '123', 3) UNION (SELECT '123', 4)
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; REPLACE INTO `test`.`t1` (`c3`,`c4`) (SELECT 'abc', 3) UNION (SELECT 'abc', 4)
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT IGNORE INTO `test`.`t1` (`c3`,`c4`) (SELECT '123', 3) UNION (SELECT '123', 4)
+master-bin.000001 # Query # # COMMIT
+
+# The statement can be binlogged correctly when it is in a SP/EVENT/TRIGGER
+
+DROP PROCEDURE IF EXISTS p1;
+CREATE PROCEDURE p1(IN a INT)
+CREATE TEMPORARY TABLE IF NOT EXISTS t1 SELECT '123', a;
+call p1(500);
+Warnings:
+Note 1050 Table 't1' already exists
+call p1(600);
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', NAME_CONST('a',500)
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', NAME_CONST('a',600)
+master-bin.000001 # Query # # COMMIT
+DROP PROCEDURE p1;
+
+# The statement can be binlogged correctly when it is in a prepared statement
+
+PREPARE stm FROM "CREATE TEMPORARY TABLE IF NOT EXISTS t1 SELECT '123', ?";
+SET @a= 700;
+EXECUTE stm USING @a;
+Warnings:
+Note 1050 Table 't1' already exists
+SET @a= 800;
+EXECUTE stm USING @a;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 700
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 800
+master-bin.000001 # Query # # COMMIT
+
+# The statement can be binlogged correctly when it is in a conditional comment
+
+# The whole statement in a conditional comment
+/*!CREATE TEMPORARY TABLE IF NOT EXISTS t1
+SELECT 'abc', 900*/;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 900*/
+master-bin.000001 # Query # # COMMIT
+
+# There is an long comment before SELECT
+/*!CREATE TEMPORARY /*blabla*/ TABLE IF NOT EXISTS t1
+SELECT 'abc', 901*/;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 901*/
+master-bin.000001 # Query # # COMMIT
+
+# Conditional comment starts just from SELECT
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+/*!SELECT 'abc',*/ 902;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc',*/ 902
+master-bin.000001 # Query # # COMMIT
+
+# Only SELECT keyword is in the conditional comment
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+/*!SELECT*/ /*!'abc',*/ 904;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT*/ /*!'abc',*/ 904
+master-bin.000001 # Query # # COMMIT
+
+# Conditional comment is after SELECT keyword
+CREATE TEMPORARY TABLE IF NOT EXISTS t1
+SELECT /*!'abc',*/ 903;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT /*!'abc',*/ 903
+master-bin.000001 # Query # # COMMIT
+
+# Conditional comment ends just before SELECT keyword
+/*!CREATE TEMPORARY TABLE IF NOT EXISTS t1
+*/SELECT 'abc', 905;
+Warnings:
+Note 1050 Table 't1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL,
+ `c3` char(10) DEFAULT NULL,
+ `c4` int(11) NOT NULL,
+ PRIMARY KEY (`c4`)
+)
+master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 905
+master-bin.000001 # Query # # COMMIT
+DROP TABLE t2;
+DROP TEMPORARY TABLE t1;
diff --git a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
index 114f71af873..cf26e58c3ec 100644
--- a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
+++ b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
@@ -119,5 +119,32 @@ SELECT * FROM t2;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
+sync_slave_with_master;
+
+--echo
+--echo # Bug#55616 Killing thread or query during CREATE IF NOT EXISTS makes
+--echo # slave SQL thread abort
+--echo
+
+--connection master1
+let $con_id = `SELECT CONNECTION_ID()`;
+
+CREATE TABLE t1 ( i INT );
+send CREATE TABLE IF NOT EXISTS t1
+ 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;
+
+--replace_result $con_id master1
+eval KILL QUERY $con_id;
+sync_slave_with_master;
+
+connection master;
+DROP TABLE t1;
source include/master-slave-end.inc;
diff --git a/mysql-test/suite/rpl/t/rpl_row_create_table.test b/mysql-test/suite/rpl/t/rpl_row_create_table.test
index a72ca75e975..148032f2987 100644
--- a/mysql-test/suite/rpl/t/rpl_row_create_table.test
+++ b/mysql-test/suite/rpl/t/rpl_row_create_table.test
@@ -299,5 +299,18 @@ DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3;
DROP TEMPORARY TABLES t7;
DROP TABLES t4, t5;
DROP TABLES IF EXISTS bug48506_t4;
+sync_slave_with_master;
+
+#
+# Bug#55598 RBR: CREATE TABLE IF NOT EXISTS and INSERT written to binary log
+# twice
+#
+connection master;
+CREATE TABLE t1 SELECT 1;
+CREATE TABLE IF NOT EXISTS t1 SELECT 1;
+let $diff_table=test.t1;
+source include/rpl_diff_tables.inc;
+DROP TABLE t1;
+
source include/master-slave-end.inc;
--echo end of the tests
diff --git a/mysql-test/suite/rpl/t/rpl_stm_create_if_not_exists.test b/mysql-test/suite/rpl/t/rpl_stm_create_if_not_exists.test
new file mode 100644
index 00000000000..69866cad267
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_stm_create_if_not_exists.test
@@ -0,0 +1,14 @@
+#
+--echo # WL#5370 Keep forward-compatibility when changing 'CREATE TABLE IF NOT
+--echo # EXISTS ... SELECT' behaviour
+--echo #
+
+source include/master-slave.inc;
+source include/have_binlog_format_statement.inc;
+
+source extra/rpl_tests/rpl_stm_create_if_not_exists.test;
+
+let $is_temporary=1;
+source extra/rpl_tests/rpl_stm_create_if_not_exists.test;
+
+source include/master-slave-end.inc;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 1627b6ec02d..42c873e9fc3 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -2622,7 +2622,9 @@ public:
class select_insert :public select_result_interceptor {
- public:
+protected:
+ virtual int write_to_binlog(bool is_trans, int errcode);
+public:
TABLE_LIST *table_list;
TABLE *table;
List<Item> *fields;
@@ -2658,6 +2660,8 @@ class select_create: public select_insert {
MYSQL_LOCK *m_lock;
/* m_lock or thd->extra_lock */
MYSQL_LOCK **m_plock;
+
+ virtual int write_to_binlog(bool is_trans, int errcode);
public:
select_create (TABLE_LIST *table_arg,
HA_CREATE_INFO *create_info_par,
@@ -2673,7 +2677,7 @@ public:
{}
int prepare(List<Item> &list, SELECT_LEX_UNIT *u);
- int binlog_show_create_table(TABLE **tables, uint count);
+ int binlog_show_create_table(TABLE **tables, uint count, int errcode);
void store_values(List<Item> &values);
void send_error(uint errcode,const char *err);
bool send_eof();
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index eb9e1e5b3af..8604f876f37 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -3268,7 +3268,7 @@ bool select_insert::send_eof()
/*
Write to binlog before commiting transaction. No statement will
- be written by the binlog_query() below in RBR mode. All the
+ be written by the write_to_binlog() below in RBR mode. All the
events are in the transaction cache and will be written when
ha_autocommit_or_rollback() is issued below.
*/
@@ -3280,9 +3280,8 @@ bool select_insert::send_eof()
thd->clear_error();
else
errcode= query_error_code(thd, killed_status == THD::NOT_KILLED);
- if (thd->binlog_query(THD::ROW_QUERY_TYPE,
- thd->query(), thd->query_length(),
- trans_table, FALSE, errcode))
+
+ if (write_to_binlog(trans_table, errcode))
{
table->file->ha_release_auto_increment();
DBUG_RETURN(1);
@@ -3356,9 +3355,7 @@ void select_insert::abort() {
{
int errcode= query_error_code(thd, thd->killed == THD::NOT_KILLED);
/* error of writing binary log is ignored */
- (void) thd->binlog_query(THD::ROW_QUERY_TYPE, thd->query(),
- thd->query_length(),
- transactional_table, FALSE, errcode);
+ write_to_binlog(transactional_table, errcode);
}
if (!thd->current_stmt_binlog_row_based && !can_rollback_data())
thd->transaction.all.modified_non_trans_table= TRUE;
@@ -3373,6 +3370,103 @@ void select_insert::abort() {
DBUG_VOID_RETURN;
}
+int select_insert::write_to_binlog(bool is_trans, int errcode)
+{
+ /* It is only for statement mode */
+ if (thd->current_stmt_binlog_row_based)
+ return 0;
+
+ return thd->binlog_query(THD::ROW_QUERY_TYPE,
+ thd->query(), thd->query_length(),
+ is_trans, FALSE, errcode);
+}
+
+/* Override the select_insert::write_to_binlog */
+int select_create::write_to_binlog(bool is_trans, int errcode)
+{
+ /* It is only for statement mode */
+ if (thd->current_stmt_binlog_row_based)
+ return 0;
+
+ /*
+ WL#5370 Keep the compatibility between 5.1 master and 5.5 slave.
+ Binlog a 'INSERT ... SELECT' statement only when it has the option
+ 'IF NOT EXISTS' and the table already exists as a base table.
+ */
+ if ((create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS) &&
+ create_info->table_existed)
+ {
+ String query;
+ int result;
+
+ thd->binlog_start_trans_and_stmt();
+ /* Binlog the CREATE TABLE IF NOT EXISTS statement */
+ result= binlog_show_create_table(&table, 1, 0);
+ if (result)
+ return result;
+
+ uint db_len= strlen(create_table->db);
+ uint table_len= strlen(create_info->alias);
+ uint select_len= thd->query_length() - thd->lex->create_select_pos;
+ uint field_len= (table->s->fields - (field - table->field)) *
+ (MAX_FIELD_NAME + 3);
+
+ /*
+ pre-allocating memory reduces the times of reallocating memory,
+ when calling query.appen().
+ 40bytes is enough for other words("INSERT IGNORE INTO", etc.).
+ */
+ if (query.real_alloc(40 + db_len + table_len + field_len + select_len))
+ return 1;
+
+ if (thd->lex->create_select_in_comment)
+ query.append(STRING_WITH_LEN("/*! "));
+ if (thd->lex->ignore)
+ query.append(STRING_WITH_LEN("INSERT IGNORE INTO `"));
+ else if (thd->lex->duplicates == DUP_REPLACE)
+ query.append(STRING_WITH_LEN("REPLACE INTO `"));
+ else
+ query.append(STRING_WITH_LEN("INSERT INTO `"));
+
+ query.append(create_table->db, db_len);
+ query.append(STRING_WITH_LEN("`.`"));
+ query.append(create_info->alias, table_len);
+ query.append(STRING_WITH_LEN("` "));
+
+ /*
+ The insert items.
+ Field is the the rightmost columns that the rows are inster in.
+ */
+ query.append(STRING_WITH_LEN("("));
+ for (Field **f= field ; *f ; f++)
+ {
+ if (f != field)
+ query.append(STRING_WITH_LEN(","));
+
+ query.append(STRING_WITH_LEN("`"));
+ query.append((*f)->field_name, strlen((*f)->field_name));
+ query.append(STRING_WITH_LEN("`"));
+ }
+ query.append(STRING_WITH_LEN(") "));
+
+ /* The SELECT clause*/
+ DBUG_ASSERT(thd->lex->create_select_pos);
+ if (thd->lex->create_select_start_with_brace)
+ query.append(STRING_WITH_LEN("("));
+ if (query.append(thd->query() + thd->lex->create_select_pos, select_len))
+ return 1;
+
+ /*
+ Avoid to use thd->binlog_query() twice, otherwise it will print the unsafe
+ warning twice.
+ */
+ Query_log_event ev(thd, query.c_ptr_safe(), query.length(), is_trans,
+ FALSE, errcode);
+ return mysql_bin_log.write(&ev);
+ }
+ else
+ return select_insert::write_to_binlog(is_trans, errcode);
+}
/***************************************************************************
CREATE TABLE (SELECT) ...
@@ -3613,7 +3707,8 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
!table->s->tmp_table &&
!ptr->get_create_info()->table_existed)
{
- if (int error= ptr->binlog_show_create_table(tables, count))
+ int errcode= query_error_code(thd, thd->killed == THD::NOT_KILLED);
+ if (int error= ptr->binlog_show_create_table(tables, count, errcode))
return error;
}
return 0;
@@ -3654,7 +3749,10 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
create_table->table_name);
if (thd->current_stmt_binlog_row_based)
- binlog_show_create_table(&(create_table->table), 1);
+ {
+ int errcode= query_error_code(thd, thd->killed == THD::NOT_KILLED);
+ binlog_show_create_table(&(create_table->table), 1, errcode);
+ }
table= create_table->table;
}
else
@@ -3722,10 +3820,10 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
}
int
-select_create::binlog_show_create_table(TABLE **tables, uint count)
+select_create::binlog_show_create_table(TABLE **tables, uint count, int errcode)
{
/*
- Note 1: In RBR mode, we generate a CREATE TABLE statement for the
+ Note 1: We generate a CREATE TABLE statement for the
created table by calling store_create_info() (behaves as SHOW
CREATE TABLE). In the event of an error, nothing should be
written to the binary log, even if the table is non-transactional;
@@ -3741,7 +3839,6 @@ select_create::binlog_show_create_table(TABLE **tables, uint count)
schema that will do a close_thread_tables(), destroying the
statement transaction cache.
*/
- DBUG_ASSERT(thd->current_stmt_binlog_row_based);
DBUG_ASSERT(tables && *tables && count > 0);
char buf[2048];
@@ -3759,7 +3856,6 @@ select_create::binlog_show_create_table(TABLE **tables, uint count)
if (mysql_bin_log.is_open())
{
- int errcode= query_error_code(thd, thd->killed == THD::NOT_KILLED);
result= thd->binlog_query(THD::STMT_QUERY_TYPE,
query.ptr(), query.length(),
/* is_trans */ TRUE,
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 7403bb5a1a4..9131cec9d04 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1817,6 +1817,23 @@ typedef struct st_lex : public Query_tables_list
*/
bool protect_against_global_read_lock;
+ /*
+ The following three variables are used in 'CREATE TABLE IF NOT EXISTS ...
+ SELECT' statement. They are used to binlog the statement.
+
+ create_select_start_with_brace will be set if there is a '(' before
+ the first SELECT clause
+
+ create_select_pos records the relative position of the SELECT clause
+ in the whole statement.
+
+ create_select_in_comment will be set if SELECT keyword is in conditional
+ comment.
+ */
+ bool create_select_start_with_brace;
+ uint create_select_pos;
+ bool create_select_in_comment;
+
st_lex();
virtual ~st_lex()
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 9ec03ea1d5f..fbe9c9753d9 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2717,6 +2717,25 @@ mysql_execute_command(THD *thd)
{
TABLE_LIST *duplicate;
create_table= lex->unlink_first_table(&link_to_local);
+
+ if (create_table->view)
+ {
+ if (create_info.options & HA_LEX_CREATE_IF_NOT_EXISTS)
+ {
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_TABLE_EXISTS_ERROR,
+ ER(ER_TABLE_EXISTS_ERROR),
+ create_info.alias);
+ my_ok(thd);
+ }
+ else
+ {
+ my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_info.alias);
+ res= 1;
+ }
+ goto end_with_restore_list;
+ }
+
if ((duplicate= unique_table(thd, create_table, select_tables, 0)))
{
update_non_unique_table_error(create_table, "CREATE", duplicate);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 5a3ad0b3eba..ed367582ba5 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -3881,17 +3881,26 @@ create2a:
create3 {}
| opt_partitioning
create_select ')'
- { Select->set_braces(1);}
+ {
+ Select->set_braces(1);
+ Lex->create_select_start_with_brace= TRUE;
+ }
union_opt {}
;
create3:
/* empty */ {}
| opt_duplicate opt_as create_select
- { Select->set_braces(0);}
+ {
+ Select->set_braces(0);
+ Lex->create_select_start_with_brace= FALSE;
+ }
union_clause {}
| opt_duplicate opt_as '(' create_select ')'
- { Select->set_braces(1);}
+ {
+ Select->set_braces(1);
+ Lex->create_select_start_with_brace= TRUE;
+ }
union_opt {}
;
@@ -4516,6 +4525,19 @@ create_select:
lex->current_select->table_list.save_and_clear(&lex->save_list);
mysql_init_select(lex);
lex->current_select->parsing_place= SELECT_LIST;
+
+ if (lex->sql_command == SQLCOM_CREATE_TABLE &&
+ (lex->create_info.options & HA_LEX_CREATE_IF_NOT_EXISTS))
+ {
+ Lex_input_stream *lip= YYLIP;
+
+ if (lex->spcont)
+ lex->create_select_pos= lip->get_tok_start() -
+ lex->sphead->m_tmp_query;
+ else
+ lex->create_select_pos= lip->get_tok_start() - lip->get_buf();
+ lex->create_select_in_comment= (lip->in_comment == DISCARD_COMMENT);
+ }
}
select_options select_item_list
{
View Lorry Controller Status