CODEWITHSUNDEEP
mysqlsqlmacosload-data-infilemysql-error-1045
djq
djq

Reputation: 15286

Why do I get an error when using LOAD DATA INFILE?

I'm trying to use SQL to upload a csv file from my laptop to a database, however I get a strange error. The code I am typing into the SQL window in phpMyAdmin is as follows:

LOAD DATA INFILE '/Users/myMac/testServerUpload.csv' 
INTO TABLE `testDatabase` 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

But I get the following error:

Error

SQL query:

LOAD DATA INFILE '/Users/myMac/testServerUpload.csv' INTO TABLE `testDatabase` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

MySQL said: Documentation
#1045 - Access denied for user 'ukld'@'%' (using password: YES)

Should I be using LOAD DATA LOCAL INFILE instead of my current command as the file is on my computer? Both give me an error, and I thought LOCAL might mean that it is on the server. I could not find relevant advice searching for this error.

Upvotes: 1

Views: 9935

Answers (2)

Ivana
Ivana

Reputation: 21

I had this same issue and fixed it by using LOCAL and giving the full file path: 

'C:/directory/file.csv'

It seems the 1045 error comes from not having access to files on the server, which is where mySQL looks for the file if LOCAL is not specified.

See also:

Upvotes: 2

AJ.
AJ.

Reputation: 28174

You need to login as root and run:

SHOW GRANTS FOR 'ukld'@'%';

Chances are you don't have the permissions that you think you do...you need INSERT privileges on the db and table(s) into which you are trying to LOAD DATA.

UPDATE:

To correct the GRANT issue, run these statements as root in your MySQL install:

REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE,
       DROP, INDEX, ALTER, CREATE TEMPORARY TABLES,
       LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
       CREATE ROUTINE, ALTER ROUTINE
       ON ukld.* FROM 'ukld'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,
       DROP, INDEX, ALTER, CREATE TEMPORARY TABLES,
       LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
       CREATE ROUTINE, ALTER ROUTINE
       ON ukld.* TO 'ukld'@'%' IDENTIFIED BY PASSWORD 'xxx';

FLUSH PRIVILEGES;

Make sure you set the correct password in the second statement.

Upvotes: 0

Related Questions