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.
