LOAD DATA INFILE fails with 'Access denied' for a normal MySQL user
ERROR 1045 (28000): Access denied for userShort answer
A server-side LOAD DATA INFILE needs the global FILE privilege on top of the usual table rights. An account that can INSERT into the table is still refused. Grant FILE to the account you really connect as, or read the file from the client with LOCAL, which does not need FILE but must be enabled on both sides.
Fix it fast
1Find out which cause you have
See exactly who the server thinks you are and what that account may do.
SELECT CURRENT_USER(); SHOW GRANTS;2Match what you see, then jump to its fix
- 1
SHOW GRANTS has no FILE, and the statement uses LOAD DATA INFILE without LOCALThe account has no FILE privilegeFix - 2
FILE is granted to 'user'@'localhost' but CURRENT_USER() shows another host partThe grant went to a different account than the one you connect asFix - 3
You are loading from a laptop to a remote server and cannot or should not give out FILEThe file is on your machine, not on the serverFix
- 1
What does the error look like?
ERROR 1045 (28000): Access denied for userLOAD DATA INFILE works as root but not as the application user
Cause 1 of 3
The account has no FILE privilege
1Check
For a load that is not LOCAL, the server itself reads the file, so the manual requires the FILE privilege. It is an administrative privilege that can only be granted globally.
SHOW GRANTS;2Fix
Grant FILE globally, as an administrator, to the account that runs the load. Grant it only to accounts that really need it: it allows reading any file the server can read.
GRANT FILE ON *.* TO 'loader'@'localhost';3Verify
Reconnect as that account and run SHOW GRANTS; FILE is listed and the load runs.
SHOW GRANTS FOR 'loader'@'localhost';Confirmed by dev.mysql.com
Cause 2 of 3
The grant went to a different account than the one you connect as
1Check
An account is a user name plus a host. A grant for one host does not apply to a connection that matches a different account.
SELECT CURRENT_USER();
SHOW GRANTS FOR CURRENT_USER();2Fix
Grant FILE to the exact account that CURRENT_USER() returned.
GRANT FILE ON *.* TO 'loader'@'%';3Verify
SHOW GRANTS for that account lists FILE.
SHOW GRANTS FOR 'loader'@'%';Confirmed by dev.mysql.com
Cause 3 of 3
The file is on your machine, not on the server
1Check
A LOCAL load reads the file on the client and sends it over the connection, so the FILE privilege is not needed. It works only if both the server and the client allow it; with local_infile disabled it returns an error.
SHOW GLOBAL VARIABLES LIKE 'local_infile';2Fix
Use LOCAL, and enable local_infile on the server and in the client if it is off.
LOAD DATA LOCAL INFILE '/path/on/client/data.csv' INTO TABLE t1 FIELDS TERMINATED BY ',' IGNORE 1 LINES;3Verify
The statement reports the number of rows loaded.
SELECT COUNT(*) FROM t1;Confirmed by dev.mysql.com
How do I stop it coming back?
- Give FILE only to a dedicated import account, not to the application user.
- Prefer LOCAL loads for files that live on the client, and keep local_infile off elsewhere.
- Keep secure_file_priv pointed at one import directory.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
LOAD DATA INFILE or INTO OUTFILE blocked by --secure-file-priv
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
The server only reads and writes files inside the folder set in secure_file_priv.
3 causes, each with a fixDatabases
LOAD DATA LOCAL INFILE is refused: loading local data is disabled
ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side
LOAD DATA LOCAL works only when both the server and the client allow it.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fix