Description:
When a CREATE...SELECT statement is rolled back, e.g., due to a duplicate key error, it will not be written to the binlog. If the SELECT had side-effects (such as updates to a non-transactional table), then those side-effects will not be replicated to the slave, in case @@binlog_format = STATEMENT.
Cf. BUG#20265. BUG#28976 is similar but not the same.
How to repeat:
source include/master-slave.inc;
--echo ==== Init ====
# table that will be different on master and slave
CREATE TABLE myisam_table (a INT) ENGINE = MYISAM;
# auxiliary table with two rows
CREATE TABLE table_with_two_rows (a INT);
INSERT INTO table_with_two_rows VALUES (0), (0);
# function that inserts into myisam_table and returns a constant
DELIMITER |;
CREATE FUNCTION insert_into_myisam()
RETURNS INT
BEGIN
INSERT INTO myisam_table VALUES (1);
RETURN 1;
END|
DELIMITER ;|
--echo ==== Test ====
# Will fail with duplicate key error. This causes the statement to not
# be binlogged, despite it had side effects.
--error ER_DUP_ENTRY
CREATE TABLE new_table (a INT PRIMARY KEY)
SELECT insert_into_myisam() AS a FROM table_with_two_rows;
# Show that new_table does not exist.
SHOW TABLES;
# Show that two rows were inserted in the myisam_table.
SELECT * FROM myisam_table;
sync_slave_with_master;
# Show that new_table does not exist on slave.
SHOW TABLES;
# Show that the two rows were *not* inserted in the myisam_table.
SELECT * FROM myisam_table;
--echo ==== Clean up ====
connection master;
DROP TABLE myisam_table;
DROP TABLE table_with_two_rows;
DROP FUNCTION insert_into_myisam;
sync_slave_with_master;
exit;
Suggested fix:
One of the following:
(1) Document as a limitation of statement-based logging, mark CREATE-SELECT as unsafe.
(2) When a CREATE-SELECT is rolled back and the statement would have been binlogged in statement mode (as determined by decide_binlog_format()), write just the SELECT to the binlog. (It is only necessary to write SELECT to the binlog in case some non-transactional table was modified.)
(3) When a CREATE-SELECT is rolled back, write it to the binlog and insert a DROP TABLE IF EXISTS afterwards.
I think (3) is not good since CREATE has an implicit commit: if the CREATE succeeds on slave, the slave will be in a state that never existed on master between the CREATE...SELECT and the DROP TABLE IF EXISTS.
