Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

LOAD DATA LOCAL INFILE not working

by Anonymous Monk
on Nov 15, 2012 at 05:07 UTC ( #1003930=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I need to insert alot of data into the database. My webpage times out (Error:Problem loading page) if i use the INSERT command. LOAD DATA LOCAL INFILE works fine in mysql but it doesnt seem to be working when I call it from my cgi file.

example.cgi my $loc = "/var/temp.txt"; my $query="load data local infile '$loc' into table test fields termin +ated by ',' lines terminated by '\t'"; my $update = $DB->do($query) or die "prepare failed: " . $DB->errstr() +; print $update ." Records inserted";
I have already created a table 'test' in the database. I get '0E0 Records inserted' in the web browser when i execute example.cgi.
Any suggestions on what I am doing wrong or what I should be doing would be helpful. Thanks!! Note: cgi files are saved in a unix system.

Comment on LOAD DATA LOCAL INFILE not working
Download Code
Re: LOAD DATA LOCAL INFILE not working
by tobyink (Abbot) on Nov 15, 2012 at 07:09 UTC

    Are the temp file, the CGI script and the database all on the same computer?

    What user is the database running as? What are he permissions on the temp file?

    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'

      Yes, all the files are on the same unix server. For the database I do not have admin rights but have all other privileges. My file permission is -rw-r--r-- temp.txt

      user@localhost:~$ perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[call +er(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do' Monkey say Monkey do

        Hi, Its working now. I was opening temp.txt to write data and then load the contents into the database. LOAD DATA LOCAL INFILE failed to upload because I did not CLOSE temp.txt before uploading into the database. Thanks for the help!

Re: LOAD DATA LOCAL INFILE not working
by Anonymous Monk on Nov 15, 2012 at 17:31 UTC

    How much data are you talking about? The database has to sync after each transaction, and most hard drive setups can only do that about a hundred times a second. Note that every insert/update/other statement counts as a transaction unless otherwise specified. You should instead wrap the inserts in an explicit transaction, like this:

    # assumes RaiseError = 1 eval { $dbh->begin_work; # insert 10k records # ... $dbh->commit; } or do { print "failed to insert records!\n"; $dbh->rollback; };

    See also DBI's section titled Transactions.

      Around 2 million rows of data. Thanks for your suggestion, will try it!

Re: LOAD DATA LOCAL INFILE not working
by erix (Vicar) on Nov 15, 2012 at 17:47 UTC

    Just a few thoughts:

    Keep an eye on the database log. Are there any errors?

    Try loading just a handful of rows first to make sure the whole setup works.

    Your statement reeks of mysql: does it run your 'query' in a transaction? Perhaps you just need to COMMIT?

    Googling show sthat not all mysql server setups allow load data local. Perhaps you're out of luck?

    mysql_local_infile

    As of MySQL 3.23.49, the LOCAL capability for LOAD DATA may be disabled in the MySQL client library by default. If your DSN contains the option "mysql_local_infile=1", LOAD DATA LOCAL will be enabled. (However, this option is *ineffective* if the server has also been configured to disallow LOCAL.)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1003930]
Approved by tobyink
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (9)
As of 2014-09-02 22:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (32 votes), past polls