Description:
Archive engine uses TL_READ type of lock for tables used in
subqueries in DML statements (including INSERT ... SELECT)
but does not take any extra measures to provide proper
isolation between such statements and concurrent INSERT
statements (like e.g. InnoDB does).
In some cases (concurrent load, long running statements or
simply unfortunate scheduling) this leads to broken statement
replication. See How-to-repeat for details.
How to repeat:
# Script #1 for mysqltest tool which demonstrates the problem
--source include/have_log_bin.inc
connect (addcon1, localhost, root,,);
connect (addcon2, localhost, root,,);
connection default;
create table t1 (i int) engine=archive;
insert into t1 values (1);
create table t2 (j int);
# Using sleep here to emulate unfortunate scheduling
--send insert into t2 select i*sleep(5) from t1;
connection addcon1;
--sleep 1
insert into t1 values (2);
connection default;
--reap
# See if binlog contents are consistent with contents of 't2' and 't1'.
show binlog events;
# Log_name Pos Event_type Server_id End_log_pos Info
# master-bin.000001 4 Format_desc 1 106 Server ver: 5.1.41-debug-log, Binlog ver: 4
# master-bin.000001 106 Query 1 207 use `test`; create table t1 (i int) engine=archive
# master-bin.000001 207 Query 1 295 use `test`; insert into t1 values (1)
# master-bin.000001 295 Query 1 381 use `test`; create table t2 (j int)
# master-bin.000001 381 Query 1 469 use `test`; insert into t1 values (2)
# master-bin.000001 469 Query 1 572 use `test`; insert into t2 select i*sleep(5) from t1
select * from t2;
# j
# 0
# Oops. Mismatch. According to binary log there should be 2 rows in 't2' !!!
------------------------------------------------------------------------------
# Script #2 for mysqltest tool which demonstrates another instance
# of the problem
--source include/have_log_bin.inc
connect (addcon1, localhost, root,,);
connect (addcon2, localhost, root,,);
connection default;
create table t1 (i int) engine=archive;
create table t2 (j int);
# Using sleep here to emulate unfortunate scheduling
--send insert into t1 values (1), (sleep(6)+2);
connection addcon1;
--sleep 2
insert into t1 values (3);
connection addcon2;
--sleep 2
insert into t2 select * from t1;
connection default;
--reap
# See if binlog contents are consistent with contents of 't2'.
show binlog events;
# Log_name Pos Event_type Server_id End_log_pos Info
# master-bin.000001 4 Format_desc 1 106 Server ver: 5.1.41-debug-log, Binlog ver: 4
# master-bin.000001 106 Query 1 207 use `test`; create table t1 (i int) engine=archive
# master-bin.000001 207 Query 1 293 use `test`; create table t2 (j int)
# master-bin.000001 293 Query 1 381 use `test`; insert into t1 values (3)
# master-bin.000001 381 Query 1 475 use `test`; insert into t2 select * from t1
# master-bin.000001 475 Query 1 577 use `test`; insert into t1 values (1), (sleep(6)+2)
select * from t2;
# j
# 1
# 3
# Oops!!! Contents of 't2' contradict the order of statements in binlog!
