HELP NEEDED - performing a large insert query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

HELP NEEDED - performing a large insert query

Post 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.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

How are you inserting the file?

I have problems with phpmyadmin doing large files, but SSH seems to work fine.
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

I do it by a PHP Script with time limit = 0 ;) No problems then :D
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

If you have console access to your host, just use the mysqlimport script, or the console
mysql -lusername -p < 42000records.sql
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post 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...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

[SOLVED] - Help doing a large insert query

Post 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.
Post Reply