|
When CREATE OR REPLACE TABLE fails after dropping the table, a special DROP TABLE is automatically written in the binary log.
It causes data inconsistency between master and slave in the following scenario:
- table t2 is created;
- temporary table t2 is created and written to the binary log in statement format;
- create or replace table t2 drops the base table t2 and then fails;
- drop table t2 is generated and written to the binary log;
=> at the end, master and slave end up with different sets of base tables: the slave has t2, but the master does not.
|
Test case
|
--source include/have_binlog_format_mixed.inc
|
--source include/master-slave.inc
|
|
--enable_connect_log
|
|
--let $con0 = `select connection_id()`
|
create table t1 (i int);
|
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8);
|
insert into t1 select t1a.* from t1 t1a, t1 t1b, t1 t1c, t1 t1d, t1 t1e, t1 t1f, t1 t1g;
|
|
create table t2 like t1;
|
create temporary table t2 as select 1;
|
send create or replace table t2 as select * from t1;
|
|
--connection master1
|
|
--let $show_statement= SHOW PROCESSLIST
|
--let $field= State
|
--let $condition= = 'Sending data'
|
--source include/wait_show_condition.inc
|
|
eval kill query $con0;
|
|
--connection master
|
--error ER_QUERY_INTERRUPTED
|
--reap
|
|
show tables;
|
|
--sync_slave_with_master
|
|
show tables;
|
drop table if exists t1, t2;
|
|
|
--connection master
|
drop table if exists t1, t2;
|
|
--disable_connect_log
|
--source include/rpl_end.inc
|
|
Actual result, 10.3 62d21ddac, 10.0 55abcfa7b, and others
|
connection master1;
|
show tables;
|
Tables_in_test
|
t1
|
connection slave;
|
show tables;
|
Tables_in_test
|
t1
|
t2
|
Note: if your machine is too fast, CREATE OR REPLACE might end too quickly and won't be caught by SHOW PROCESSLIST, in which case you'll get a timeout on wait condition and subsequently success instead of ER_QUERY_INTERRUPTED on reap. Try to increase the amount of data in t1, to make CREATE AND REPLACE take longer.
|