1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

mySQL Datadumps - problem with xf_data_registry

Discussion in 'Server Configuration and Hosting' started by MGSteve, Feb 16, 2012.

  1. MGSteve

    MGSteve Well-Known Member

    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.

    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.
     
  2. MGSteve

    MGSteve Well-Known Member

    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.
     
  3. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
  4. MGSteve

    MGSteve Well-Known Member

    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.
     

Share This Page