“mysql 8.0” local infile fails I tried and display the settings. Could it be permission issues?

The following is my session I have the data located in the dir with owner/group of mysql
mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.18 MySQL Community Server – GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global local_infile = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> use SSC;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> load data local infile '/storage/temp/myest1.csv' into table SSC fields terminated by ',' lines terminated by '\n' ignore 1 lines;
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql> show variables like "local_infile";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> load data local infile '/storage/temp/myest1.csv' into table SSC;
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql> load data local infile 'myest1.csv' into table SSC;
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /storage/temp/            |

1 Comment

Team CodeForHost (November 5, 2019)

If you’re using the LOCAL option with LOAD DATA INFILE, then the secure_file_priv isn’t used for the location of the file you load. The file is loaded by the client, so the path you use is not on the server, it’s on the client. If you give a relative path, it’s relative to the current working directory when you started the client.

However, both the server and the client must enable the --local-infile option.

You’ve set the option in the server, but you also need to set it in the client like this:

mysql --local-infile -u root -p

I think the error message you got is pretty unhelpful. I logged a bug about it:

Bug #94396 Error message too broad: The used command is not allowed with this MySQL version

Please vote up that bug report if you agree!

Leave A Comment

What’s happening in your mind about this post !