Page 1 of 1

HELP NEEDED - performing a large insert query

Posted: Tue Apr 26, 2005 8:36 am
by RobertGonzalez
Hello all, I have a query in a sql file that contains an INSERT query of about 42,000 records. My problem is the file seems to be too large for MySQL to handle appropriately. The last time I did this type of thing (52,000 records) I broke the file down into files of 5,000 records each. I would really like to avoid that this time.

Does anyone have any suggestions on how I might be able to get this SQL file to run in MySQL? All help is appreciated.

Posted: Tue Apr 26, 2005 9:48 am
by malcolmboston
i see no reason why this is not working as i have inserted much bigger insert queries (1m+ records across 15+ fields)

i did it by copying (select all) and pasting the query into PHPmyADMIN and running it from there, be warned, it takes its time but it does work

Posted: Thu Apr 28, 2005 5:28 am
by rsmarsha
How are you inserting the file?

I have problems with phpmyadmin doing large files, but SSH seems to work fine.

Posted: Thu Apr 28, 2005 5:34 am
by n00b Saibot
I do it by a PHP Script with time limit = 0 ;) No problems then :D

Posted: Thu Apr 28, 2005 5:31 pm
by RobertGonzalez
My situation in this is kinda weird. I develop locally then upload when finished. I did that with a database table with about 42,000 records. I dumped the table from phpMyAdmin (local) then tried to open the sql file from phpMyAdmin (live server). Usually this is not problem but phpMyAdmin kept giving me this message:

"No SQL Query!"

So I tried to do a few other things and each time I would get an error. I tried copying and pasting the entire file but that froze my browser. So I ended up copying and pasting it bit by bit. 7,000 or so rows at a time until it was done. It is all good now, but I would really like to avoid doing it this way in the future.

Thanks for all your help and suggestions.

Posted: Thu Apr 28, 2005 5:43 pm
by nickvd
If you have console access to your host, just use the mysqlimport script, or the console
mysql -lusername -p < 42000records.sql

Posted: Thu Apr 28, 2005 5:52 pm
by RobertGonzalez
I do not believe my host allows console access (Telnet or Shell (?)). And in all honesty, I don't even know how to access the command line on my local machine (although I have wanted to be able to do this for some time).

Is there a way to hit your DB at the command line if your host does not allow Telnet or whatever it is that gets you to the console?

PS I hope I don't seem like too much of a novice with this post.

Posted: Thu Apr 28, 2005 6:25 pm
by nickvd
If they dont give you access to telnet (NONE SHOULD!) or ssh (MUCH BETTER) then afaik there's no decent way. Although I suppose that you could use exec() to do it, but there should be an easier way...

[SOLVED] - Help doing a large insert query

Posted: Sun May 01, 2005 10:15 am
by RobertGonzalez
Thanks for the help. After trying a few different techniques for getting this insert to work I ran across a MySQL 1153 error (max_allowed_packet size exceeded). After a little bit of investigation I found that the default MySQL server setting for file size execution of sequel scripts is 1.0MB. I changed the max_allowed_packet to 16M in the MySQL Server Variables and the 41,000 row insert ran in just under 4 seconds. I was able to do this on my local MySQL server only. My host will not change their setting.

Hope this helps someone. This problem really frustrated me.