Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Product Page - Migration to MySQL Request SQLWays

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

Error Description

While importing data to MySQL version 8.0.2 and higher, an error “ERROR 1148 (42000): The used command is not allowed with this MySQL version” may occur.

Cause

Ispirer SQLWays Wizard tool performs data import using a mysql.exe utility and loads data from the file, which was generated by the tool on the same machine where you run the conversion. However, if you are running the migration process on the machine different from the one where your MySQL server is running, it means that you're trying to load data from a file located on a client side. Please take into account that MySQL versions 8.0.2 and higher doesn't allow to load data from a client side by default. This setting is controlled by MySQL system variable “local-infile”, which is set to “OFF” in the latest versions:

MySQL Version Default Value
>= 8.0.2 OFF
⇐ 8.0.1 ON

Solutions

In order to resolve this problem, all you need is to set this option to “ON” in MySQL Server. Otherwise you can use the option “Enable Local Load” in SQLWays Wizard tool. It will add an option “local-infile” in a command line that makes data load to MySQL database.

Please click the “Advanced” button on a”Choose a Target Database” page:


And then check the option “Enable Local Load”:

Please, note:

Both client and server should enable the local-file option. Otherwise it will not work. To enable it for files on the server side, add the following to the my.cnf configuration file:

 loose-local-infile = 1
 

Or if you have SUPER privileges, you can execute the following command on the server:

 SET GLOBAL local_infile = 1;

More information about “loose-local-infile” option can be found here: Security Issues with LOAD DATA LOCAL

Please, note:

If you don't see the “Advanced” button on this page, please switch the tool to the “Expert Mode”. If you don't know how to do it, please refer to this link: How to Use SQLWays in Expert Mode

If you have any other questions, please feel free to contact our support team: support@ispirer.com


Ispirer Home Page Ispirer SQLWays Product Page - Migration to MySQL Request SQLWays

sqlways/troubleshooting-guide/mysql/import/command-not-allowed-data-load.txt · Last modified: July 25, 2019, 08:12:59 AM by alexandr.kirpichny
 
© 1999-2019, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement