How to Work Around the \'The used command is not allowed with this MySQL version\' Error in LOAD DATA LOCAL INFILE?

Workaround for "The used command is not allowed with this MySQL version" Error in LOAD DATA LOCAL INFILE

The LOAD DATA LOCAL INFILE command provides a convenient way to import data from a local CSV file directly into a MySQL database. However, in certain configurations, users may encounter the following error:

The used command is not allowed with this MySQL version

This error suggests that the server variable local_infile is disabled, which prevents the execution of LOAD DATA LOCAL INFILE statements.

To work around this issue without modifying server settings, consider the following steps:

1. Login to MySQL with the relevant user: If you are currently encountering the error, try logging into the MySQL console with the same user and attempting to execute the LOAD DATA command again.
2. Start MySQL with --local-infile=1: If the error persists, close the console and restart MySQL using the following command, where USER is your MySQL username and DATABASE is the database you wish to load data into:

mysql -u USER -p --local-infile=1 DATABASE

3. Retry the LOAD DATA command: After restarting MySQL with the --local-infile option, attempt to execute the LOAD DATA command once more.

If this workaround is successful, you will need to either modify the server configuration to permanently enable local infile imports or reinstall MySQL with the --local-infile option.

Additional Notes:

• The phpMyAdmin utility may use a different method for importing CSV files that does not involve LOAD DATA INFILE, which could explain why it appears to work correctly while direct LOAD DATA commands fail.
• The references provided in the answer refer to MySQL versions 5.0 and 5.5, but the solution has been confirmed to work with version 5.5 as well.

The above is the detailed content of How to Work Around the \'The used command is not allowed with this MySQL version\' Error in LOAD DATA LOCAL INFILE?. For more information, please follow other related articles on the PHP Chinese website!