List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 16 2005 7:19pm
Subject:Re: Multiple record Insert limit
View as plain text  
"Ed Reed" <EReed@stripped> wrote on 02/16/2005 02:13:40 PM:

> Can anyone tell me the limit for the number of records that can be
> inserted in a single call?
> 
> I'm trying to insert multiple records like this,
> 
> INSERT INTO t1 (f1, f2, f3, f4) VALUES
> ("test", 1, "some data", "Done"),
> ("testing", 21, "some more data", "Still Done"),("tested", 50, "no more
> data", "Not Done"),("tester", 201, "Lots of data", "Finished");
> 
> This works when I've done 10 records but fails when I do 30000. The
> error I get when it fails is 
> [MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]MySQL server has gone away.
> So what's the max number that I can insert in a single statement?
> 
> Thanks!
> 

In my experience, the max# of inserts depends on the setting of the 
MAX_ALLOWED_PACKET variable. If you create a packet that is too large 
(exceeds that value) you will run into problems like you are seeing.

To check the current value for your server, you can do

SHOW VARIABLES like 'max%';

Make sure when you are creating your INSERT statements that you do not 
exceed that value for each statement. If these are generated by mysqldump, 
it has an option where you can set the maximum length of a generated 
INSERT statement. Check the docs (
http://dev.mysql.com/doc/mysql/en/mysqldump.html) or run: mysqldump --help 
for specifics.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine





Thread
Multiple record Insert limitEd Reed16 Feb
  • Re: Multiple record Insert limitSGreen16 Feb
Re: Multiple record Insert limitEd Reed17 Feb