MySQL Bugs: #60580: Statement improperly replicated crashes slave SQL thread

Bug #60580	Statement improperly replicated crashes slave SQL thread
Submitted:	22 Mar 2011 8:56	Modified:	13 May 2011 10:58
Reporter:	Romain Muller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.5.10, 5.6.3	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	regression, replication

Description:
Some statements in the binary log result when decoded into invalid DML being executed, that contains <cache>(...) elements, e.g.:

#Begin_load_query: file_id: 242  block_len: 5039
# at 1057777760
#110321  9:51:52 server id 6411  end_log_pos 1057778311         Execute_load_query      thread_id=947   exec_time=0     error_code=0
use AHEM/*!*/;
SET TIMESTAMP=1300726312/*!*/;
LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-f2-0' REPLACE INTO TABLE `tbl_InboundTrack` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`DATE`, `SCOPE`, @FC, `PL_id`, `VALUE`,
 `USER`) SET `FC_id`=(select `AHEM`.`stat_tbl_FC`.`FC_id` from `AHEM`.`stat_tbl_FC` where ((`AHEM`.`stat_tbl_FC`.`FC` = (@FC)) and (isnull(`AHEM`.`stat_tbl_FC`.`CLOSED_ON`) or (`AHEM`.`stat_tbl_FC`.`CLOSED_ON`
 > <cache>(utc_date()))))), `CREATED`=utc_date()
/*!*/;
# file_id: 242 
# at 1057778311
#110321  9:51:52 server id 6411  end_log_pos 1057778338         Xid = 72485
COMMIT/*!*/;

How to repeat:
Execute LOAD DATA LOCAL INFILE into a table, and have a SET clause that uses a view which relies on utc_date().

The error mentionned in the description actually wad generated from an original statement like:

LOAD DATA LOCAL INFILE 'path-to-file' INTO TABLE tbl_InboundTrack (DATE, SCOPE, @FC, PL_ID, VALUE) SET FC_ID = (SELECT FC_ID FROM stat_tbl_FC_Open WHERE FC = @FC)

AND stat_tbl_FC_Open is a view like:
SELECT *
FROM stat_tbl_FC
WHERE CLOSED_ON IS NULL OR CLOSE_ON > UTC_TIMESTAMP()

Though the UTC_TIMESTAMP() part was turned into a utc_date() when migrating from 5.1.x to 5.5.x.

Thank you for the report.

Verified as described. This is regression: bug does not exists in 5.1 and 5.0

Test case for MTR:

--source include/master-slave.inc

create table t1(f1 int, f2 int);
create table t2(f1 int, f2 timestamp);

insert into t2 values(1, '2011-03-22 21:01:28');
insert into t2 values(2, '2011-03-21 21:01:28');
insert into t2 values(3, '2011-03-20 21:01:28');

create view v1 as select * from t2 where f2 < UTC_TIMESTAMP();

--exec echo 1 > $MYSQL_TEST_DIR/var/tmp/rpl_bug60580.csv

--eval LOAD DATA LOCAL INFILE '$MYSQL_TEST_DIR/var/tmp/rpl_bug60580.csv' INTO TABLE t1 (@f1) set f2 = (select f1 from v1 where f1=@f1)

select * from t1;

sleep 1;

connection slave;
--vertical_results
show slave status;

select * from t1;

Documented bugfix as follows in the 5.5.15 and 5.6.3 changelogs:

        If LOAD DATA INFILE statement--replicated using
        statement-based replication--featured a SET clause, the
        name-value pairs were regenerated using a method intended
        primarily for generating output for statements such as EXPLAIN
        EXTENDED, and which cannot be relied on to return valid SQL.
        This could in certain cases lead to a crash on the slave.

        To fix this problem, we now name each value in its original,
        user-supplied form, and use that to create LOAD DATA INFILE
        statements for statement-based replication.

Closed.