Packet error

Hey.

Today I tried to import an database and it works great so far. Only one table with posts of the website forums doesn't work.

I tried to import directly to my local webserver and via a file. In both cases the import seems to end in a never-ending story. I guess it is too much data for the database at once.

Some minutes ago I got the following error message and I don't know what to do:
"/* SQL Error: Got a packet bigger than 'max_allowed_packet' bytes */"

Heidi SQL can import tables with many thousands of small entries without problem but the forums posts table is a pain. What can I do? Is there a possibility to store the backup in small steps? For example 500 entries per query only?

The table is about 3MB and has about 3800 entries. HeidiSQL seems to use two steps to import the data and fails.


Hope someone can help me. Thank you for every usefull answer.

Version? Build revision? See the "About" dialog

Rev 2369 and a rev build some days ago.

With 3.2 stable it worked finally, but I had to try several times. I use xampp as local server and would guess, that it is not configured to insert such great amounts of data at the same time. But this can't be a reason for the error.

Btw: With the rev builds I had two or three syntax errors in the last days, but I can't describe them any more. I restarted the backup und the errors disappeared, so I ignored them (as I used a test build...).

"/* SQL Error: Got a packet bigger than 'max_allowed_packet' bytes */"

This is an error from the server. See also: http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html .

HeidiSQL tries to detect the maximum packet size and does its best to produce INSERT queries which are smaller than that, but as big as they can to optimize performance. A huge number of short queries produce a large overhead and have a big impact on performance, so HeidiSQL creates grouped INSERTs:

INSERT INTO table (...) VALUES (...), (...), (...)

However, the detection of the maximum allowed packet size can only work correctly if you're either doing a live export (using option "another server" or "another database") or if you import the SQL file on the same server as it was exported from. In all other situations, the INSERTs can grow to the size of what is set as max_allowed_packet on the source server which can be too large for the target server.

The error above means: Your source server has a larger max_allowed_packet variable as your target server. Fix that by starting the target server with max_allowed_packet to what the SQL header says:

# HeidiSQL Dump 
#
# --------------------------------------------------------
# Host:                         localhost
# Database:                     test3
# Server version:               5.1.22-rc-community
# Server OS:                    Win32
# Target compatibility:         Same as source (5.1.22)
# Target max_allowed_packet:    1048576   <<<<<<<<<
# HeidiSQL version:             x.y
# Date/time:                    2009-04-07 23:41:45
# --------------------------------------------------------

shell> mysql --max_allowed_packet=32M

Thank you very much. :)