mySQL Datadumps - problem with xf_data_registry

[MGSteve]

I've just come across this when I went to restore a backup made through phpMyAdmin - it appears that because its a blob, you can easily end up with an insert statement larger than the maximum allowed packet size in mySQL.

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

This one in particular came to 1.2mb, where as the maximum packet size is 1mb.

Other than changing the max allowed packet size, I don't know if there is a way around this?

The statement it croaked on was INSERT INTO `xf_data_registry` (`data_key`, `data_value`) VALUES ('styles', 0x613a363a7b693a353.....

I'll try turning off the option in phpMyAdmin to dump binary fields in hex and see if that creates any other issues, but as its on by default I thought I'd mention it incase it caught someone out at the worst time (having to recover the db!)

For those who can alter their mySQL configuration, just add

max_allowed_packet=2M
onto the end of the my.cnf file (usually located in /etc/)
If you still get the error keep upping the size until it works and then comment the line out again by putting a # at the start of the line.
Obviously you'll need to restart mysql after each change.

 

[MGSteve]

Just to answer my own question, unticking the option in phpMyAdmin to dump binary fields as hex fixes the issue (and results in a much smaller SQL file!) - but this needs to be done manually as by default it's ticked.

 

[Jake Bunce]

Looks like you have several styles installed. The size of that particular record is a function of the number of styles.

You can either increase the "max_allowed_packet" value in your MySQL configuration or remove some styles.

 

[MGSteve]

Removing styles isn't an option if all you have is the SQL though

As I said above, not dumping them out in hex mode solved it, but I thought I'd mention it to make people aware of it if they backup using that particular option.