access denied for load data infile in MySQL

Question

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?

Answer 1

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}

Answer 2

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)

Answer 3

Try using this command:

load data local infile 'home/data.txt' into table customer;

This should work. It worked in my case.

Answer 4

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.

Answer 5

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.

Answer 6

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;

Answer 7

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();

Answer 8

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

Answer 9

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

Answer 10

I discovered loading MySQL tables can be fast and painless (I was using python / Django model manager scripts):

1) 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à!

Answer 11

I was trying to insert data from CSV to MYSQL DB using python. You can try the below method to load data from CSV to Database.

1. Make a connection with the Database using pymysql or MySQL.connector any library you want in python.
2. Make Sure you are able to use the in-line while connecting for that while providing host, user, and password try to add local_inline=True.

Skipping to load data part. sql = f'''LOAD DATA LOCAL infile "filename.csv" INTO TABLE schema.tablename FILED TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'''' Note: If you have column names in CSV, use IGNORE ROW 1 LINES. The execute the sql by: cursor.execute(sql) conn.commit() conn.close()

Answer 12

It probably means that the password you supplied for 'user'@'localhost' is incorrect.