Bug #92132 secure-file-priv breaks LOAD DATA INFILE replication in statement mode on 5.7.23
Submitted: 22 Aug 16:00 Modified: 26 Sep 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

[22 Aug 16:00] Nicolai Plum
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.
[23 Aug 6:17] Umesh Shastry
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
[23 Aug 6:21] Umesh Shastry
test results

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

[26 Sep 14:01] Margaret Fisher
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.