Description:
When rows are inserted into a non-transactional table, new rows are committed as they are inserted: if an error causes the statement to abort in the middle of the update, the inserted rows stay.
When executing INSERT...SELECT without ORDER BY primary_key, the order of rows that are retreived is unspecified and may differ between master and slave. Hence, if such a statement is executed on a non-transactional table and one of the rows retreived by the SELECT causes an error when inserted into the table, then it can cause the slave to go out of sync.
Cf BUG#50439.
How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_statement.inc
--echo ==== primary key violation ====
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE = MyISAM;
CREATE TABLE t2 (a INT) ENGINE = MyISAM;
INSERT INTO t1 VALUES (2);
INSERT INTO t2 VALUES (1), (2);
--sync_slave_with_master
DELETE FROM t2;
INSERT INTO t2 VALUES (2), (1);
--connection master
--error ER_DUP_ENTRY
INSERT INTO t1 SELECT * FROM t2;
SELECT * FROM t1;
--sync_slave_with_master
SELECT * FROM t1;
--connection master
DROP TABLE t1, t2;
--echo ==== invalid date ====
CREATE TABLE t1 (a DATE) ENGINE = MyISAM;
CREATE TABLE t2 (a VARCHAR(100)) ENGINE = MyISAM;
SET SQL_MODE = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
INSERT INTO t2 VALUES ('2010-01-01'), ('2010-00-00');
--sync_slave_with_master
DELETE FROM t2;
INSERT INTO t2 VALUES ('2010-00-00'), ('2010-01-01');
--connection master
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 SELECT * FROM t2;
SELECT * FROM t1;
--sync_slave_with_master
SELECT * FROM t1;
--connection master
DROP TABLE t1, t2;
--echo ==== division by zero ====
CREATE TABLE t1 (a INT) ENGINE = MyISAM;
CREATE TABLE t2 (a INT) ENGINE = MyISAM;
SET SQL_MODE = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
INSERT INTO t2 VALUES (1), (0);
--sync_slave_with_master
DELETE FROM t2;
INSERT INTO t2 VALUES (0), (1);
--connection master
--error ER_DIVISION_BY_ZERO
INSERT INTO t1 SELECT a / a FROM t2;
SELECT * FROM t1;
--sync_slave_with_master
SELECT * FROM t1;
--connection master
DROP TABLE t1, t2;
Suggested fix:
Mark INSERT...SELECT statements without ORDER BY primary_key unsafe if the target table is nontransactional.
Maybe we don't have to mark such statements unsafe if both the following conditions hold:
- the table has no unique or primary key
- SQL_MODE does not contain STRICT_ALL_TABLES
On the other hand, maybe there are other ways statements can fail than those listed above - we have to check that before we decide to not mark statements unsafe.
