MySQL Load Data infile Access Denied


#1

Hello,

I have been trying to load data from a file placed on the server using ftp, into a database I created, using MySql’s ‘Load data infile…’ query.

Each time I execute the query it errors out with the message -

“Access denied for ‘user’@xxx.xxx.xxx (using password) error: 1045”

Thing is I am already logged in. When I execute a second query as a test, it works fine so the connection is up and running. I have tried it using MySQl’s admin tool and from the command line and get the same result. I can’t figure this one out!

Second question, once I get the query to run, what is the path to the file ?

I’m guessing it would be: 'mydomain.com/folder/file …?

Thank your for your help.


#2

LOAD DATA INFILE doesn’t work unless the file exists on the same server as MySQL, which isn’t the case on our system.


#3

So how can upload large amounts of data into a database then?

Thanks.


#4

Easy - in a shell, type:

(Replacing mysql.example.com and mysqlusername as appropriate.)


#5

It worked! Learned something too… Thanks. :slight_smile:


#6

I have a similar but somewhat more complicated problem.
I have a third party uploading a compressed csv file to a folder i specificy and once that happens, I need to uncompress and import csv data into my sql server.
On top of that I would like to control which columns from the csv file I actually import into my table ( i know the format of the csv file in advance… no interface needed)
Plus I need to do some cleanup of the data imported discarding rows i don’t need.

I would like this process of importing and cleaning up to be scripted which I could hopefully trigger from a web interface.
Do you have any suggestions on how I can set this up?

thanks a lot


#7

Unless you’re using a private server, in which case it is.


#8

No, even a MySQL PS is on separate hardware.

The only case where you’ve got local MySQL is on a dedicated server. Even there, I don’t believe the permissions are set up such that you can use the local socket.


#9

Use LOCAL to load from the client

The LOCAL keyword, if specified, is interpreted with respect to the client end of the connection:

  • If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

  • If LOCAL is not specified, the file must be located on the server host and is read directly by the server.

http://dev.mysql.com/doc/refman/5.0/en/load-data.html


#10

Hi!
Yes! you get it. LOCAL is a big difference. and works very nice including in another providers.

see this good example show up here let me solve issues on shared servers.

other very important detail is that mysql command that is read command load data local infile …

on shell:

mysql -u your-db-user-name -p[your-db-password-with-no-spaces] -h your-host -D your-database-name

mysql prompt > LOAD DATA LOCAL INFILE ‘./vprepr1.csv’ INTO TABLE vprepr1 FIELDS TERMINATED BY ‘;’ IGNORE 1 LINES;

mysql prompt> quit

see that ./vprepr1.csv is with apostrofe and ./ tells that is on current folder where you issue mysql (client command).

i was missed this detail and mysql refuses syntax;

thanks.