Bug #48343 Wrong locking for Archive tables leads to broken statement replication.
Submitted: 27 Oct 2009 9:09 Modified: 27 Oct 2009 10:23
Reporter: Dmitry Lenev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S3 (Non-critical)
Version:5.1.41-bzr OS:Any
Assigned to: CPU Architecture:Any

[27 Oct 2009 9:09] Dmitry Lenev
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!
[27 Oct 2009 10:23] MySQL Verification Team
Thank you for the bug report.