Cannot load data to MySQL database with LOAD DATA INFILE

Question

I am using LAMP in Ubuntu 12.04. I created a new user in MySQL (myserver@localhost) and granted ALL on a database to that user. There is a text file the permission of which is set to read for everyone. But when I try to load the data from that text file to the database, it says "Access denied for user 'myserver'@'localhost' (using password: YES)"

The query I used is:

LOAD DATA INFILE "~/text/member_info.txt" INTO TABLE member FIELDS TERMINATED BY '|';

I can think of a workaround with some loops in PHP but why doesn't the 'LOAD DATA INFILE' work?

Thanks.

Which password? I logged in to MySQL with the password and it was correct no problem.@Devart — CluelessNoob
– CluelessNoob, Commented Jun 13, 2012 at 13:09
Please see this post: forums.mysql.com/read.php?30,12298,12298#msg-12298. And do a bit of googling — Simon
– Simon, Commented Jun 13, 2012 at 13:20

gpojd · Accepted Answer · 2013-11-14 15:06:06Z

1

This is due the MYSQL 5.5 version you are using in Ubuntu LTS 12.04 : mysql doc

From mysql doc comment :

You can use LOAD DATA LOCAL with recent versions of PHP without recompiling PHP.

Passing 128 (the value of the CLIENT_LOCAL_FILES constant) as the fifth parameter to mysql_connect () enables LOAD DATA LOCAL on the client side.

Example: $dbh = mysql_connect($server, $user, $pass, false, 128);

For PHP 4.3 and above.

But in fact, mysql_connect() is obsolete, and I can't find a solution using mysqli.

That's why I finally use this who solve the problem.

grant file on *.* to mydatabase@localhost identified by 'myuser';

Now $mysqli->query("LOAD DATA INFILE '".$proxy_file_name_path."' IGNORE INTO TABLE mytable (myfield)"

edited Nov 14, 2013 at 15:06

gpojd

23.2k8 gold badges45 silver badges71 bronze badges

answered Nov 16, 2012 at 18:45

Mike Castro Demaria

7109 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot load data to MySQL database with LOAD DATA INFILE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related