Mysql LOAD DATA INFILE

Hello.

I am trying to import a 35 Meg Text file that is comma seperated and optionally enclosed by quotes. I was using php to parse through the text file and do the inserts, one per line. But turns out this Kills our server.
I decided to go with

$loadsql = 'LOAD DATA INFILE "'.$csvfile.'" INTO TABLE '.$table.' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\r" IGNORE 1 LINES';
mysql_query($loadsql) or die(mysql_error());

Code (markup):

This works on my testing server, but when I run it on the production server it gives me "Access denied for user: ' ... etc"
Is there some setting that I need to change? ( and yes I am using the right password)

It seems to be an issue with this LOAD DATA INFILE command...

please help

Red.

 

Hm I didn't even know you could do that.

Probably has to do with the permissions the user running the command has. Try GRANT ALL to the user on the prod server.

 

There's no particular permissions required just for that command but you will need write access. Do you know for sure that you have write access on that table? If not I suggest using the "GRANT ALL" command as said above.

 

Yeah, Neither did I. I was surprised that it worked on my testing server.
The user has "ALL PRIVILEGES" granted to the database that I am connecting to.

Must be a permission problem. Might need to use the mysql root user. I am not sure.

Thanks.

 

Use the keyword "LOCAL".

LOAD DATA LOCAL INFILE ...

Code (markup):

Oh, and I just wanted to add that if you ever do need to read through a text file and do a bunch of inserts (in php), be sure to use the DELAYED keyword, this will speed up the process and won't thrash your server as badly.

 

Thanks for the help guys. I almost got it figured out using the LOAD DATA INFILE I need to have the file located on the server. It is run from a script, so there is no way I can use LOCAL.
Now I get the error that mysql cannot "stat()" the file. Which probably means there is not correct permisions on the file, or the path to the file.

Thanks again

 

I use LOCAL in scripts all the time- for that matter phpMyAdmin is a script and you can you LOCAL in it Did you actually try it in your script? if so, what error did it generate?

Did you try putting your input file in the mysql directory or giving it universal read rights? (try a chmod 444 on unix)

 

here's my sitch-uation.

I download data from an FTP server every day using a php script. These files are downloaded to the server directly, so this is why I don't think I can use LOCAL.
Am I wrong to think that?

I also tried chmod 444 of the file and the directory it is in...
It might be that the directory that it is in from / doesn't have universal rights.
It is in a user directory (/home/username/datafile/35megcsv.csv)
I don't think it would be safe security wise to give read access to the entire "/home/username" direcory, nor I am sure how to get the file into the mysql direcory without being root .


I will keep trying, thanks for your help.

 

Update!
It worked. Needed to set the file permisions on the file and the directory it is in to chmod 755.

Now to get this to work from the script.

Thank you.

 

There are actually 2 options in phpMyAdmin for LOAD DATA INFILE, you can change it with a selection box at the bottom of the import data screen. The other option requires you to have full priviledges on your mysql account which is not given by default.

 

Hi, New Problem - Different data file :-(

I have got it working, except when It reads the file it's like the file is empty?
(says 0 rows inserted, but was able to read the file... just not the text inside the file?

I don't need to mess with the MySql Privleges in order to get this to work.. or do I?

I have the permissions set to 755 on the direcory and the file.

I would just use PHP to parse the data file in this case , except I have not been able to find/code a solution that mimics the Mysql LOAD DATA LOCAL INFILE command.... Especially the OPTIONALLY ENCLOSED by part.

Thanks again for help

 

Try putting a mysql_error (); call right after your LOAD query and see if it prints any errors.

 

Yes you have to mess with the priv's, by default the permission you need is turned off...

 

$loadsql = 'LOAD DATA LOCAL INFILE "'.$csvfile.'" INTO TABLE '.$table.' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\r" IGNORE 1 LINES';
mysql_query($loadsql) or die(mysql_error());

Code (markup):

I get no Error. :-(

I thought that using the "LOCAL" command made it so that I did not have to have the file_priv privledge.

I'll try changing the privledges , i suppose.

 

Try this: (If your file has Macintosh line endings use TERMINATED BY '\r' like you had.)

$loadsql = "LOAD DATA LOCAL INFILE '$csvfile' INTO TABLE `$table` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES";

Code (markup):

 

Thanks Exam.
It was my Query.
First I tried the code you provided, then ...
I don't exactly know what to make of this but When I removed the part LINES TERMINATED BY '\r'
This works Fine:

$sql = 'LOAD DATA LOCAL INFILE "'.$txtfile.'" INTO TABLE '.$table.' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" IGNORE 1 LINES';

Code (markup):

I hope it won't cause anyproblems for me removing the LINES TERMINATED BY bit...
Thank you for leading me in the right direction

 

If the LINES clause is missing, MySQL assumes LINES TERMINATED BY '\n'. It works because your input file has unix line endings and not Apple line endings as you were telling MySQL and as I mentioned in my post above

 

Ah.. That makes sence. This is great! Thank you again.
I had previously been parsing these large text files using php and it was thrashing on the server. With LOAD DATA LOCAL INFILE , you hardly notice it at all

 

You're welcome. Good luck with your endeavours

 

Yeah, Neither did I.... I must try to implement it though i'm no internet or program guru. Will give it a shoot hopefully it will work for me.