MySQL 5.7.23 replica failed to execute load data infile

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hello,

Server migrated from Percona MySQL 5.7.19 to 5.7.23 failed to apply Load Data infile during replication.
Specs:

  • Master-master replication
  • Statement based replication
  • Percona MySQL 5.7.23 (both servers)
  • CentOS 6.9

Error:
Error ‘The MySQL server is running with the --secure-file-priv option so it cannot execute this statement’ on query. Default database: ‘xxxxxx’. Query: ‘LOAD DATA INFILE ‘/var/lib/mysql/SQL_LOAD-597abdb6-26dd-11e8-bac8-f403434e0298-206-1.data’ IGNORE INTO TABLE xxxxxx FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘’ ESCAPED BY ‘\’ LINES TERMINATED BY ‘\n’ (xxx,xxx) SET create_date= now(), create_user= ‘xxxxxxxxx’, update_user= ‘xxxxxxx’’

The systems were previously running Percona MySQL 5.7.19. We did not experience this problem with that version.

Is this a Percona MySQL 5.7.23 bug?

Thank you,
Alex Malberty
BabyCenter LLC
Johnson&Johnson

2

As a temporary fix, we setup secure_file_priv = ‘’, but I would like to revert that change.

3

Hi Alex,

I was able to reproduce your issue. This is indeed a bug and it affects MySQL upstream as well, here is the bug description:

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

The workaround as you mentioned is to set secure_file_priv but this might be a security issue. Another option is to set the binlog_format to ROW os MIXED.

4

Thank you Vinicious. We will change replication to ROW based.