Errors Using mysqlimport to Import Data Into MySQL

When using mysqlimport to import data from the command line into the popular open-source MySQL database server there are a few errors that can occur. This article covers some of these errors and solutions for them.

mysqlimport example usage

In the examples on this page it is assumed the mysqlimport command is in the search path, so the full path to the executable is not shown. We are importing data into the customer table in the test database. The basic usage for the mysqlimport command is:

mysqlimport -u username -p database /path/to/tablename.txt 

Note that the file must be the same as the tablename (and it is case-sensitive) but the extension part of the filename can be whatever you would like it to be.

In our examples we would be issuing the following command:

mysqlimport -u user -p test customers.txt 

Incorrect Username and/or Password

The first error you might experience when using mysqlimport from the command line is shown below. In this example we have entered either the username or password incorrectly.

The error text shows us we did enter the password (it would say "Using password: NO" if we had not) but does not tell us whether it was the username or password. Make sure you typed both in correctly and try again. Note that usernames and passwords are case sensitive.

mysqlimport: Error: Access denied for user: 'user@localhost' (Using password: YES) 

Insufficient Database Permissions

The error message in this example tells you a password was not entered, even though you actually may have entered one. What's really happened is that you don't have the MySQL "file" privileges to be able to load the data in the file into the database. This is a global privilege which must be set before you can use mysqlimport.

If you administer the MySQL database yourself it is easy to fix this (another article to be added this week will detail how to add file privileges to MySQL), otherwise you will need to get your hosting provider to make the change, assuming their policy lets you have file permissions.

mysqlimport: Error: Access denied for user: 'user@localhost' (Using password: NO), when using table: customers 

Note that this error supplies a little more information than the previous one; it tells you which table the data was attempted to be loaded into.

Database Table Does Not Exist

In this example you were successfully able to log into the MySQL database server but the table you are trying to import data into does not actually exist. Make sure you are importing into the correct table and that your filename matches that of a table in the database.

The case of the filename is important; the extension of the filename is not important, eg the .txt part could be .tab or .csv.

mysqlimport: Error: Table 'test.customer' doesn't exist, when using table: customer 

Database Import File Does Not Exist

This error means the file you are trying to import into MySQL does not exist. Check you have the path correct and the file actually exists. It can also mean that the MySQL server does not have sufficient permissions to access the file in the directory it is in. See below.

mysqlimport: Error: Can't get stat of '/path/to/customers.txt' (Errcode: 2), when using table: customers 

Insufficient Directory Permissions

The error in this example has resulted because the file you are trying to import is not in a directory which is readable by the user the MySql server is running as, or use the --local flag as shown below the error example.

Although this looks the same as the error above (about the file not existing) there is a minor
difference in that the error code number is 13 instead of 2 in the earlier example.

mysqlimport: Error: Can't get stat of '/path/to/customers.txt' (Errcode: 13), when using table: customers 

The simplest solution to dealing with the permissions issue is to use the --local flag like so:

mysqlimport --local -u user -p test customers.txt

Success!

test.customers: Records: 14   Deleted: 0   Skipped: 0   Warnings: 0 

This one is the success message. Note the format of information at the beginning of the line shows which database and table that were affected ie databasename.tablename