MySQL Bugs: #92132: secure-file-priv breaks LOAD DATA INFILE replication in statement mode on 5.7.23

Bug #92132	secure-file-priv breaks LOAD DATA INFILE replication in statement mode on 5.7.23
Submitted:	22 Aug 2018 16:00	Modified:	26 Sep 2018 14:01
Reporter:	Nicolai Plum	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.7.23, 8.0.12	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	LOAD DATA INFILE, replication, Statement-based replication

Description:
Bug #38174 has resurfaced in MySQL 5.7.23.

If you have replication and your master is 5.7.22 and slave is 5.7.23 then LOAD DATA LOCAL INFILE breaks replication (at least on an RPM installation)

The cause is that the secure-priv-file variable is set to "/var/lib/mysql-files" (default on RPM installations and others) while the temporary file created when statement-based replication replicated LOAD DATA LOCAL INFILE is in "../tmp/..."  The result is that replication stops on the slave with an error like:

Error 'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement' on query. Default database: 'people_analytics'. Query: 'LOAD DATA INFILE '../tmp/SQL_LOAD-a9e215d5-277c-11e8-87d1-c4346eab3b80-193262017-3.data' IGNORE INTO  TABLE `dest_table ` FIELDS...

This is a regression of bug #38174

How to repeat:
Set up 5.7.22 master and 5.7.23 slave with statement-based replication. Create a table. Create local data file with data suitable to load into the table.

eg:
CREATE TABLE t (
id int NOT NULL,
col1 text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

test data:

1,hello
2,cruel
3,world

Connect to the master with a mysql command line client and execute

LOAD DATA LOCAL INFILE '/tmp/testfile' INTO TABLE `t` FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n' (`id`, `col1`);

Check replication status on the 5.7.23 slave and it will be stopped with the error:

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Suggested fix:
Remove the regression causing this bug.

Workaround: change the replication to Row-Based Replication.

Hello Nicolai Plum,

Thank you for the report.
Observed this on 5.7.23/8.0.12 using test case provided in Bug #38174.

regards,
Umesh

test results

Attachment: 92132.results (application/octet-stream, text), 99.30 KiB.

Posted by developer:
 
Changelog entry added for MySQL 5.7.24 and 8.0.14:

Following a patch in MySQL 5.7.23, LOAD DATA INFILE statements stopped statement-based replication from a MySQL 5.7.22 master to a replication slave at a later release. The problem has now been fixed.

Hi, 
Is this only happening from 5.7.22(master) to 5.7.23(slave), because i am encountering similar problem with 5.7.23(master) to 5.7.23(slave) as well.

Hi Syed Rizwan Hashmi,
from my understanding of this bug, it could also happen in the case you describe: with 5.7.23(master) to 5.7.23(slave).
Basically and also from my understanding, 5.7.23 is a bad version to deploy when you use LOAD DATA and replication.
Good luck upgrading, JFG