开发者

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.


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:

  • http://dev.mysql.com/doc/refman/5.1/en/mysqlimport.html


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜