“ERROR 1148 (42000): The used command is not allowed with this MySQL version” when doing LOAD DATA LOCAL FILE with MySQL8.0.18

This writing is just in case you get stuck following this tutorial from MySQL -> https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html

When loading data into a table using a .txt file using mysql, the MySQL Command-Line Client that comes with the MySQL 8.0.18 package and executing this command:

mysql> LOAD DATA LOCAL INFILE 'path/file.txt' INTO TABLE table_name LINES TERMINATED BY '\r';

you may encounter this error:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

A possible solution is to ensure that the MySQL server and mysql Command-Line client is enabled to read files locally. By default the MySQL server and the mysql Command-Line client are disabled by default.

To enable the MySQL server to read local files, run the following in the mysql Command-Line client

mysql> SET GLOBAL local_infile=1;

1 means ‘ON’.

If you run

mysql>SHOW VARIABLES;

you will see local_infile is now ON.

Now you will need to restart your mysql Command-Line client session from the Terminal assuming to you are in the mysql directory

$sudo bin/mysql -u user -p --local-infile=1

Now if execute the LOAD DATA LOCAL INFILE command, you should now say your Query is OK.

What I write here is not my teaching, but my study; it is not a lesson for others, but for me. — Montaigne

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store