When trying to load a local file data to your MySQL table, you may see an error saying that The used command is not allowed with this MySQL version.
For example, I want to load an infile.txt file to a table named students with the following data:
Sarah Math 9
Christ English 7
Natalia Math 6
When I run the LOAD DATA LOCAL INFILE statement, MySQL throws the following error:
mysql> LOAD DATA LOCAL INFILE './infile.txt' INTO TABLE students;
ERROR 1148 (42000): The used command is not allowed with this MySQL version
This error happens because loading data from a local file has now been disabled by default.
Note that when you update to the latest MySQL version, the error message may have been changed to a more descriptive one as follows:
mysql> LOAD DATA LOCAL INFILE './infile.txt' INTO TABLE students;
ERROR 3948 (42000): Loading local data is disabled;
this must be enabled on both the client and server sides
Both errors can be resolved in the same way.
You need to allow local file loading by enabling the feature from both client and server sides. Let’s learn how to do that next.
Enabling local data load on MySQL server and client
First, you need to enable local data load from the server side by setting the local_infile global variable value to ON.
You can find the variable with the SHOW VARIABLES statement as shown below:
SHOW VARIABLES LIKE 'local_infile';
-- The response:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
In my case, the local_infile variable is still OFF, so I need to use the SET statement to turn it ON as follows:
SET GLOBAL local_infile = true;
Now local_infile should have the value ON in the MySQL server.
Next, you need to enable local data load from the client.
Exit your mysql command-line client and add the --local-infile option when you connect to the server as shown below:
mysql -uroot -proot --local-infile=1
Now you should be able to execute the LOAD DATA with local file statement as shown below:
mysql> LOAD DATA LOCAL INFILE './infile.txt' INTO TABLE students;
# response:
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
With that, new data should be stored in your MySQL table.
And that’s how you can resolve The used command is not allowed with this MySQL version error from your MySQL database server. Nice work! 👍
