Bug #42134 Stored functions or triggers using selects might break statement replication
Submitted: 15 Jan 2009 15:35 Modified: 28 May 2009 13:28
Reporter: Dmitry Lenev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.77-bzr, 5.1.31-bzr, 6.0.10-bzr OS:Any
Assigned to: Christoffer Hall CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[15 Jan 2009 15:35] Dmitry Lenev
Description:
Concurrent execution of stored function or trigger which uses SELECT statement (as part of RETURN or SET statements, or in SELECT ... INTO form) and INSERT statement affecting table used in this select can result in wrong binary log order and therefore broken statement replication. See How-to-repeat for example.

How to repeat:
# This script for mysqltest tool demonstrates the problem
--source include/have_log_bin.inc

connect (addcon, localhost, root,,);
connection default;
create table t1 (i int);
insert into t1 values (1);
create function f1() returns int return (select count(*) from t1);
create table t2 (j int);
# We use sleep() here just to enforce certain order of events. In practice
# the same can happen due to unfortunate scheduling.
--send insert into t2 values (sleep(6) + f1());
connection addcon;
--sleep 2
insert into t1 values (2);
connection default;
--reap
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.31-debug-log, Binlog ver: 4
#master-bin.000001      106     Query   1       192     use `test`; create table t1 (i int)
#master-bin.000001      192     Query   1       280     use `test`; insert into t1 values (1)
#master-bin.000001      280     Query   1       441     use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
#return (select count(*) from t1)
#master-bin.000001      441     Query   1       527     use `test`; create table t2 (j int)
#master-bin.000001      527     Query   1       615     use `test`; insert into t1 values (2)
#master-bin.000001      615     Query   1       717     use `test`; insert into t2 values (sleep(6) + f1())

select * from t2;
# Result from this SELECT contradicts above binary log
# i
# 1

Suggested fix:
Ensure that we use proper type of lock (TL_READ_NO_INSERT) for such selects.
[15 Jan 2009 21:48] Sveta Smirnova
Thank you for the report.

Verified as described.
[28 May 2009 13:28] Christoffer Hall
Can't repeat.
[28 May 2009 14:14] Dmitry Lenev
Hello!

I can't it repeat it any longer as well. Investigation shows that it is likely that this problem was fixed by the same patch as bug "Bug #39843 DELETE requires write access to table in subquery in where clause".