How to Solve Error Message ERROR 1148 (42000): The used command is not allowed with this MySQL version

By Posted on

Introduction

This error occurs upon loading a CSV file data into a single table exist in a database running in a MySQL database server. So, there is a MySQL Database Server with the version of 5.6.20. After successfully logging in to the database, the process for loading a CSV file ends in failure. The following is the exact execution of the command : 

C:\Users\Personal>mysql -uroot -hlocalhost -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> load data local infile 'C:\temp\csv_file.csv' into table tbl_data fields terminated by ',';
ERROR 1046 (3D000): No database selected
mysql> use db_temp;
Database changed
mysql>

Solution

Actually, as in the introduction part, although the database is selected through the last command above, there is still another error appear. That is because the path for loading the CSV file is not using a double backslash character. The command will not recognize the backslash character as a path separator. It will need a double backslash. The first backslash will act as an escape character. The second one is the actual path separator. Just read the article with the title of ‘How to Solve Error Message file not found (Errcode: 22 - Invalid argument) upon load CSV file into a MySQL Table’ in this link for more information. But executing the command after is still generating the same error as follows : 

mysql> load data local infile 'C:\\temp\\csv_file.csv' into table tbl_data fields terminated by ',';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql>

In order to solve the above output command execution so it will end in a success, the following is the attempt of it : 

mysql> load data infile 'C:\\temp\\csv_file.csv' into table tbl_data fields terminated by ',';
Query OK, 311 rows affected, 4402 warnings (0.36 sec)
Records: 311  Deleted: 0  Skipped: 0  Warnings: 4402
mysql>

So, after modifying the command it is actually a success. In MySQL Database server 5.6.20, just remove the ‘local’ keyword. So, the command pattern is in the following pattern : 

load data infile 'csv_file_path.csv' into table table_name fields terminated by ',';

At last, as in the above command execution, all data available in the CSV file is a success for the loading process to the table exist in MySQL Database Server.

Leave a Reply