Packet bigger than max allowed

[Renada]

Hi,

My forum is on shared hosting and I used their system to do a database backup. When I try to restore the backup I get this error:

ERROR 1153 (08S01) at line 3268: Got a packet bigger than 'max_allowed_packet' bytes

My host is telling me to reduce the size of the packets and they won't increase the packet limit. Yes I should probably look for a new host now but in the meantime is there any way around this?

Regards,
Renada

 

[Paul B]

Unless you can increase the max packet site, there's not a lot you can do.

You could upload the .sql dump to your server via FTP and ask them to restore it for you.

 

[Renada]

Unless you can increase the max packet site, there's not a lot you can do.

You could upload the .sql dump to your server via FTP and ask them to restore it for you.

Thanks Brogan, looks like I need to find a new host. Is there no way to split the packet into smaller packets? I know which block of code it is. It has "LLOCK TABLES `xf_template` WRITE;" at the start and "UNLOCK TABLES;" at the end. In between there are 5 lines of INSERT INTO

I'm not a php person and copying one line at a time results in a mysql error so I'm surmising each line must have a start/end code. Is that the LLOCK TABLES and UNLOCK TABLES? Could I make these packets smaller by adding the LLOCK/UNLOCK to the beginning and end of each line and uploading them separately?

 

[Jake Bunce]

Did your host tell you what the limit is? It must be pretty small to generate that error on template inserts.

I don't know of any way to "reduce the size of the packets." The packet size has to do with the length of the data being inserted. I don't think you can affect this by rearranging the locks. Perhaps your host can provide instructions for their recommendation.

The better fix is to have your host increase the limit.

For reference:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_allowed_packet

 

[Renada]

Did your host tell you what the limit is? It must be pretty small to generate that error on template inserts.

I don't know of any way to "reduce the size of the packets." The packet size has to do with the length of the data being inserted. I don't think you can affect this by rearranging the locks. Perhaps your host can provide instructions for their recommendation.

The better fix is to have your host increase the limit.

For reference:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_allowed_packet

Hi Jake,

Apparently it's set at 1meg and they won't even entertain the idea of increasing it.

This is what their answer to the error was:

This is because the packet site is over the default 1M for our systems. You should retake the backup from where it was retrieved from setting a lower packet size and this should then work for you.

Trouble is since I've already half overwritten it 3 times the whole thing needs reinstalling from the complete backup to be honest or I'm going to keep running into problems I think. When I told them this I got a reply saying they had restored a backup to my root directory for me to import. Tried that - same error. Ludicrous to get the same error on their own backups.

Their answer as to how I can backup the database was this:

This is possible using phpmyadmin, when performing the export select the custom export method. At the bottom of the options is a section called "Maximal length of created query" if you set this to under 1MB then this will create a valid dump for you to use for an import.

This doesn't help me import the one I already have though lol! Their reply to that was :

I have discussed this matter with our system administrators, and it appears that as there is one or more packet in your database that is over the maximum packet size allowed, although it may be possible for you to take a backup of your database, it is not possible to upload the backup to the database after the backup has been taken, due to the way our systems work. I'm really sorry but there is no way for me to increase the maximum allowed packet size, sorry.

If I can't reduce the packet size of the bits that are breaking it I'll just start again but I think it's time to find a new host first.

I'm hoping that there will soon be a XenForo to XenForo import available.

Thanks for looking at it anyway

Regards,
Renada

 

[Luke F]

Change host, they clearly don't have a clue

If you can't be bothered changing, at least try threatening them with leaving

 

[Renada]

Change host, they clearly don't have a clue

If you can't be bothered changing, at least try threatening them with leaving

I agree Dark, I'm just scouring around for a new host now