HELP NEEDED - performing a large insert query
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
HELP NEEDED - performing a large insert query
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.
Does anyone have any suggestions on how I might be able to get this SQL file to run in MySQL? All help is appreciated.
-
malcolmboston
- DevNet Resident
- Posts: 1826
- Joined: Tue Nov 18, 2003 1:09 pm
- Location: Middlesbrough, UK
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
"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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
[SOLVED] - Help doing a large insert query
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.
Hope this helps someone. This problem really frustrated me.