MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Ed Reed Date:February 17 2005 12:33am
Subject:Re: Multiple record Insert limit
View as plain text  
That did it.
Thanks for the quick response!

>>> <SGreen@stripped> 2/16/05 11:19 AM >>>
"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
> 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
> 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
it has an option where you can set the maximum length of a generated 
INSERT statement. Check the docs ( ) or run: mysqldump
for specifics.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

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