access denied for load data infile in MySQL
MysqlLoad Data-InfileMysql Error-1045Mysql Problem Overview
I use MySQL queries all the time in PHP, but when I try
LOAD DATA INFILE
I get the following error
> #1045 - Access denied for user 'user'@'localhost' (using password: YES)
Does anyone know what this means?
Mysql Solutions
Solution 1 - Mysql
I just ran into this issue as well. I had to add LOCAL
to my SQL statement.
For example, this gives the permission problem:
LOAD DATA INFILE '{$file}' INTO TABLE {$table}
Add LOCAL
to your statement and the permissions issue should go away. Like so:
LOAD DATA LOCAL INFILE '{$file}' INTO TABLE {$table}
Solution 2 - Mysql
I had this problem. I searched around and did not find a satisfactory answer. I summarise below the results of my searches.
The access denied error could mean that:
- 'user'@'localhost' does not have the FILE privilege (
GRANT FILE on *.* to user@'localhost'
); or, - the file you are trying to load does not exist on the machine running mysql server (if using LOAD DATA INFILE); or,
- the file you are trying to load does not exist on your local machine (if using LOAD DATA LOCAL INFILE); or,
- the file you are trying to load is not world readable (you need the file and all parent directories to be world-readable: chmod 755 directory; and, chmod 744 file.dat)
Solution 3 - Mysql
Try using this command:
load data local infile 'home/data.txt' into table customer;
This should work. It worked in my case.
Solution 4 - Mysql
Ensure your MySQL user has the FILE privilege granted.
If you are on shared web hosting, there is a chance this is blocked by your hosting provider.
Solution 5 - Mysql
I found easy one if you are using command line
Login asmysql -u[username] -p[password] --local-infile
then SET GLOBAL local_infile = 1;
select your database by use [db_name]
and finally LOAD DATA LOCAL INFILE 'C:\\Users\\shant\\Downloads\\data-1573708892247.csv' INTO TABLE visitors_final_test FIELDS TERMINATED BY ','LINES TERMINATED BY '\r \n' IGNORE 1 LINES;
Solution 6 - Mysql
The string from Lyon gave me a very good tip: On Windows, we need to use slahes and not backslashes. This code works for me:
File tempFile = File.createTempFile(tableName, ".csv");
FileUtils.copyInputStreamToFile(data, tempFile);
JdbcTemplate template = new JdbcTemplate(dataSource);
String path = tempFile.getAbsolutePath().replace('\\', '/');
int rows = template.update(MessageFormat
.format("LOAD DATA LOCAL INFILE ''{0}'' INTO TABLE {1} FIELDS TERMINATED BY '',''",
path, tableName));
logger.info("imported {} rows into {}", rows, tableName);
tempFile.delete();
Solution 7 - Mysql
I ran into the same issue, and solve it by folowing those steps :
- activate load_infile variable
- grand file permission to my custom mysql user
- deactivate secure_file_priv variable (my file was uploaded by the webserver to the /tmp folder which is of course not the secured directory of myslq /var/lib/mysql-file)
For this 3rd point, you can refer to : https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv
BR,
AD
Solution 8 - Mysql
This happened to me as well and despite having followed all the steps described by Yamir in his post I couldn't make it work.
The file was in /tmp/test.csv with 777 permissions. The MySQL user had file permissions, LOCAL option was not allowed by my MySQL version, so I was stuck.
Finally I was able to solve the problem by running:
sudo chown mysql:mysql /tmp/test.csv
Solution 9 - Mysql
If you are trying this on MySQL Workbench,
Go to connections -> edit connection -> select advanced tab
and add OPT_LOCAL_INFILE=1
in the 'Others' text field.
Now restart the connection and try.
Solution 10 - Mysql
I discovered loading MySQL tables can be fast and painless (I was using python / Django model manager scripts):
- create table with all columns VARCHAR(n) NULL e.g.:
mysql> CREATE TABLE cw_well2( api VARCHAR(10) NULL,api_county VARCHAR(3) NULL);
2) remove headers (first line) from csv, then load (if you forget the LOCAL, you’ll get “#1045 - Access denied for user 'user'@'localhost' (using password: YES)”):
mysql> LOAD DATA LOCAL INFILE "/home/magula6/cogswatch2/well2.csv" INTO TABLE cw_well2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' -> ; Query OK, 119426 rows affected, 19962 warnings (3.41 sec)
3) alter columns:
mysql> ALTER TABLE cw_well2 CHANGE spud_date spud_date DATE;
mysql> ALTER TABLE cw_well2 CHANGE latitude latitude FLOAT;
voilà!
Solution 11 - Mysql
It probably means that the password you supplied for 'user'@'localhost'
is incorrect.