"Ed Reed" <[EMAIL PROTECTED]> 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
