1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.
  2. Better Analytics for WordPress Get It Free

Mysql LOAD DATA INFILE

Discussion in 'MySQL' started by rederick, Mar 13, 2006.

  1. #1
    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.
     
    rederick, Mar 13, 2006 IP
  2. TheHoff

    TheHoff Peon

    Messages:
    1,530
    Likes Received:
    130
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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.
     
    TheHoff, Mar 13, 2006 IP
  3. skimmy

    skimmy Peon

    Messages:
    138
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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.
     
    skimmy, Mar 13, 2006 IP
  4. rederick

    rederick Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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.
     
    rederick, Mar 13, 2006 IP
  5. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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.
     
    exam, Mar 13, 2006 IP
  6. rederick

    rederick Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #6
    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
     
    rederick, Mar 13, 2006 IP
  7. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #7
    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)
     
    exam, Mar 13, 2006 IP
  8. rederick

    rederick Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #8
    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.
     
    rederick, Mar 14, 2006 IP
  9. rederick

    rederick Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #9
    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.
     
    rederick, Mar 14, 2006 IP
  10. Immorta

    Immorta Peon

    Messages:
    379
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #10
    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.
     
    Immorta, Mar 14, 2006 IP
  11. rederick

    rederick Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #11
    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
     
    rederick, Mar 17, 2006 IP
  12. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #12
    Try putting a mysql_error (); call right after your LOAD query and see if it prints any errors.
     
    exam, Mar 18, 2006 IP
  13. Immorta

    Immorta Peon

    Messages:
    379
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #13
    Yes you have to mess with the priv's, by default the permission you need is turned off...
     
    Immorta, Mar 19, 2006 IP
  14. rederick

    rederick Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #14
    $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.
     
    rederick, Mar 19, 2006 IP
  15. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #15
    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):
     
    exam, Mar 20, 2006 IP
  16. rederick

    rederick Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #16
    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 :)
     
    rederick, Mar 20, 2006 IP
  17. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #17
    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 :)
     
    exam, Mar 21, 2006 IP
  18. rederick

    rederick Peon

    Messages:
    128
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #18
    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 :D
     
    rederick, Mar 21, 2006 IP
  19. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #19
    You're welcome. Good luck with your endeavours
     
    exam, Mar 21, 2006 IP
  20. wfhnow

    wfhnow Active Member

    Messages:
    133
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #20
    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.
     
    wfhnow, May 28, 2006 IP