It is not possible to import data into MySQL using "LOAD DATA INFILE" query

Created:

2016-11-16 13:04:40 UTC

Modified:

2017-08-11 15:04:29 UTC

2

Was this article helpful?


Have more questions?

Submit a request

It is not possible to import data into MySQL using "LOAD DATA INFILE" query

Applicable to:

  • Plesk 12.5 for Linux
  • Plesk 12.5 for Windows

Symptoms

When trying to import data to MySQL table from a file using LOAD DATA INFILE one keeps getting the Access denied error in spite of using this database's owner user. It does not matter if PHPmyAdmin or MySQL CLI is being used.

Cause

Option CSV using LOAD DATA is not allowed without FILE privilege.

The FILE privilege gives a permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function.

Resolution

According to MySQL Reference Manual :

A user who has the `FILE` privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.)

Note : it might not be 100% secure to grant FILE privilege to a user since it gives the ability to access any file that has read for all permission and mysql or psaadm user has access to. Refer to MySQL documentation for more information.

To give File privilege to the user you should login into mysql database as admin :

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` mysql

and run the following query:

mysql> GRANT FILE ON *.* to "USER";

where USER should be replaced with the real MySQL user name.

Note : the file which is tried to import data from should be placed in the current database directory on the Plesk server or be readable for all. Otherwise, the following error will be observed:

_   ERROR 1085 (HY000): The file 'stabase.cvs' must be in the database
directory or be readable by all_

#- INTERNAL

Automatic import from KB #1780

Have more questions? Submit a request
Please sign in to leave a comment.