ERROR 2020: Got packet bigger than 'max_allowed_packet'

[Farid]

I just switched webhosts (Infinology to VO) and I got a full dump of my database (90 Mb). The attachment table is around 25 Mb, the post around 21 Mb and the serachindex around 20.

Since I have no Telnet access (virtual hosting at VO), the VO guys have been helping me to restore the database... but with no success :(

First, it seemed like the ERROR 2020 (Got packet bigger than 'max_allowed_packet') appears at a line corresponding to the attachment table. I emptied the attachment table and got a new dump. They were able to restore all the table but not the "post" one. Error 2020 again...

The guys told me they changed the max_allowed_packet to 128 Mb under the mysqld in the my.cnf file... And still it didn't work.

That's the last news from one of the tech guys:

I was not able to restore the post table, nor the whole database after dropping and recreating it. We have restored hundreds of databases for clients over the years and this is the first time I have received this error message:

root@zelda [/home/dtourism/public_html/mysql_dump]# mysql dtourism_dtourism < dtourism.sql
ERROR 2020 at line 3800: Got packet bigger than 'max_allowed_packet'

If you wish for us to investigate further please authorize support charges. One of our technicians will need to investigate a solution to this for you.

If you know the exact settings requires in the my.cnf file please let us know and we'll gladly try it again.

Please help!!! :(

[Steve Machol]

The VO support guys are very good. I don't know of anything we could do that they haven't already tried.

How was the database backup made? Do you still have access to it on the old server so you can make another one?

[Farid]

Yes the VO guys have been very quick and courteous, but I am surprised they are not able to restore the dump and are now asking me to pay $65/hour for support :(

The backup was made through PHPMyAdmin. I selected "structure and data" as well as extended inserts, complete inserts, and I saved to file. In the past, when my server at Infinology went kaputt, the Infinology guys were able to restore the database easily...

I still have access to the existing database at Infinology and can try to do another dump... Any recommendations for that?

And what about the my.cnf file? Any recommendations for fine tuning it to fix this ERROR 2020 problem?

Thanks!

[Steve Machol]

Use Step 1 here to make the backup and try again:

http://www.vbulletin.com/manual/movingservers.html

[eva2000]

what's the wait_timeout value in mysql set to as well ? are they using mysql 4.0.13 or 3.23.57 ? are max_allowed_packet values set for both client and mysql server groups ?

[Farid]

The MySQL version VO is using is 3.23.56... same as Infinology.

Steve, I don't have telnet access to the Infinology server, so the only way I can do a dump is through PHPMyAdmin... :(

Eva, I don't know about the setup of the my.cnf file, nor the wait_timeout value... Do you recommend I suggest to them increasing all the values?

Thanks for your help!

PS: I am asking them to provide me the my.cnf file... Will get back to you as soon as I have it!

[Farid]

Eva, here's what the VO guys gave me:

The current settings for /etc/my.cnf are as follows:

[mysqld]
set-variable = max_connections=512
set-variable = max_user_connections=45
set-variable = wait_timeout=360
set-variable = key_buffer=16M
set-variable = join_buffer=2M
set-variable = record_buffer=2M
set-variable = sort_buffer=3M
set-variable = table_cache=1024
set-variable = thread_cache_size=128
set-variable = max_allowed_packet=64M

[Farid]

I am really getting desperate... :(

I tried to do inserts using PHPMyAdmin but I get timeouts and errors even for a hundred lines...

Please help!