--secure-file-priv Causes MySQL to not Accept LOAD DATA INFILE since Update

Question

After upgrading from mysql 5.6 to mysql 5.7 (due to update from Ubuntu 15.10 to Ubuntu 16.04 and using purge before, because of a bug in the update: https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1573279 ), every LOAD DATA INFILE from my application results in:

Caused by: java.sql.SQLException: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The option seems to have changed during the update:

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

Unfortunately, I can not find where this option may be specified:

root@reichelt-ThinkPad-T440s:/etc/mysql# fgrep "secure-file-priv" * -r

does not return anything. The default value seems to has changed: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv .

Setting it myself in /etc/mysql/mysql.conf.d/mysqld.cnf to the old value /var/lib/mysql does solve the problem, but nevertheless, when I not set it, it prints the error above, which should not be printed as the value is set by default. Is this a bug in the behaviour of mysql or do I miss something in the behaviour?

Answer 1

Here are a couple options to get around this.

-Identify the directory specified from which you can import your file with a command like:

1.SHOW VARIABLES LIKE "secure_file_priv"; -Or, you can use LOCAL in your query. This way the file is imported by the client (not the server) and provided through the existing client connection.

2.LOAD DATA LOCAL INFILE "text.txt" INTO TABLE mytable;