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?

Transpadane

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}

Swain

This does a different thing. It uploads your file to the server in a temporary directory. This is necessary sometimes, but if the infile is on the MySQL server already, you're just making redundant work. – Flossie

yep this did the trick for me, I was forwarding the database server port over ssh and I guess it was looking for the file on the remote database server without the LOCAL part – Chifley

@Swain for me this produces the following error: Error Code: 1148 The used command is not allowed with this MySQL version. I tried some answers for this problem such as modifying the mysql file to local-infile=1 and that failed as well. – Mcgaha

an update of mySQL to 6.2.5 solved this problem for me – Mcgaha

you may also have to call mysql with the --local-infile option. – Mccorkle

I wish for two things: (a) that Google found this question when I was looking for an answer, and (b) that MySQL supplied a more specific error message for this, as it is insanely annoying to waste half a day on something so simple... – Pahang

this did the trick to me! once my server and mysql are on different machines the LOCAL statement to upload the file to mysql server works like a charm! thanks – Petersham

Important: If file gets uploaded to server with LOAD DATA LOCAL INFILE and there are constrains conflicts on insert, then it won't return any error/exception and will execute and returns true. – Waggoner

and also use absolute path for file : /home/[currentuser]/filename – Tirpitz

ERROR 1148 (42000): The used command is not allowed with this MySQL version – Fugacious

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)

Labrecque

+1: This worked for me: 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). I hadn't changed permissions on all my directories – Walford

User Tatiana points out that you can't grant the FILE privilege per database, only for the whole server. The grant command would be "GRANT FILE on . to user@'localhost' IDENTIFIED BY 'password');" – Sofiasofie

@Sofiasofie I think you mean "GRANT FILE ON *.* to user ..." -- probably the asterisk characters were stripped – Kymric

as @Eugene M said this gives error Incorrect usage of DB GRANT and GLOBAL PRIVILEGES – Tourism

Answer 3

Try using this command:

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

This should work. It worked in my case.

Chaotic

ERROR 1148 (42000): The used command is not allowed with this MySQL version – Fugacious

@Stewart, please remove 'local' from the above command. Later mysql versions do not seem to support this flag when global variable 'local_infile' variable is set to 'ON' (as below). The command will therefore, be; mysql> load data infile 'home/data.txt' into table customer; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | ON | +---------------+-------+ – Glasser

@KamranHyder Sounds like you have a good answer to me. Why not add it as a full answer? – Fugacious

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.

Tecumseh

On a shared web hosting: would it be helpful to use "LOAD DATA LOCAL INFILE"? – Jilt

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.

Ism

love the answer since it has the screenshot as well as specifies restarting – Lives

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;

Cush

Adding of --local-inline helped me. However, local_infile global variable was already set to ON in my case. I think users better to figure out first what value they have written in this variable before modifying it. – Penitentiary

For RDS users => Adding the --local-infile helped me on RDS, however SET GLOBAL local_infile = 1; doesn't seem to work in RDS, but anyway without that the --local-infile did the trick – Ecphonesis

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

Besom

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

Barranquilla

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

Experimental

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

Draper

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

Isomagnetic

Answer 12

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

Mourant

I don't think so. I can do other queries with the same password. – Transpadane