Clint Thomson's picture

When restoring a server using TKLBAM the following error is displayed when TKLBAM startes to restore a database:

 

mysql got a packet bigger than max_allowed_packet

Any guidance would be appreciated.

Forum: 
Clint Thomson's picture

I used Webmin to increase the size of the max_allowed_packet value to 50M and it fixed this problem.

Liraz Siri's picture

Glad you got it worked out and thanks for sharing the solution. This way anyone who runs into this problem will search the site (or Google it) and bump into the solution.
Jeremiah's picture

I ran into the same problem.  I have a TurnKey LAMP 11.1 server running on VMware and I have been using TKLBAM to back it up to an in-house backup server.  The backups work great but when I tested the restore on a new TurnKey LAMP 11.1 vm the restore crashed with the same "Got a packet bigger than 'max_allowed_packet' bytes" error mentioned above.

If you don't want to permanently reconfigure your mysql server to do the restore you can login to your TKL server as root and run the following commands before running tklbam-restore.

# the following will set max_allowed_packet to 100MB
mysql -p -e 'SET GLOBAL max_allowed_packet=100*1024*1024;'

the following will verify the mysql server accepted the new setting
mysql -p -e 'SELECT @@global.max_allowed_packet;'

This setting will only last until the mysql server is restarted.

Liraz, I wonder if this can be incorporated into TKLBAM mysql.py somehow so this can be done without the end user knowing about it?  Thanks for looking into it.

Liraz Siri's picture

It seems it's quite common for more serious users to run into this problem, so I plan on looking into fixing this in a way that is transparent to the user. I had this on my todo list before but I'm bumping up the priority.
L. Arnold's picture

I started having this problem in Magento, now in Joomla as well.  In Joomla just now it did not "break" the restore, but actually thought it was successful, even though when I looked at it, I did have a max-allowed-packets error on one file.

In Magento that would completely break the Restore process.  Joomla version thought all was good.

The lesson likely is to simply increase the packets permanently but I think there is some danger there as well.

Liraz Siri's picture

MySQL seems to be dying on you in the middle of a restore. I haven't seen that happen before but maybe if you look in the logs you can figure out what is going on. Maybe you are running out of memory?
Liraz Siri's picture

I can see you're in a pickle so I'll try to help and hopefully we can get to the bottom of this and prevent it from happening to others in the future.

First we'll need to hack TKLBAM so it keeps around the "mysqlfs" file structure your MySQL backup is stored in. Normally this is deleted. Apply this patch to tklbam's source code:


diff --git a/restore.py b/restore.py
index 8094d11..576486d 100644
--- a/restore.py
+++ b/restore.py
@@ -87,6 +87,7 @@ class Restore:

         extras_path = TempDir(prefix="tklbam-extras-")
         os.rename(backup_archive + backup.Backup.EXTRAS_PATH, extras_path)
+        shutil.copytree(extras_path, "/tmp/extras")

         self.extras = backup.ExtrasPaths(extras_path)
         self.rollback = Rollback.create() if rollback else None
Now restore the backup with --skip-database. After the restore your data should be in /tmp/extras/myfs, but it is encoded so we'll need to convert it back to mysqldump using this script:

#!/usr/bin/python
"""
Map a filesystem created by mysql2fs back to MySQL

Options:
    --tofile=PATH           Write mysqldump output to file (- for STDOUT)
    -v --verbose            Turn on verbosity

    --skip-extended-insert  Skip extended insert (useful in debugging)
    --add-drop-database     Drop databases and then recreate them

Supports the following subset of mysql(1) options:

    -u --user=USER 
    -p --password=PASS

       --defaults-file=PATH
       --host=HOST

"""
import sys
import getopt

import mysql

def usage(e=None):
    if e:
        print >> sys.stderr, "error: " + str(e)

    print >> sys.stderr, "Syntax: %s [-options] path/to/myfs [ -?database/table ... ] " % sys.argv[0]
    print >> sys.stderr, __doc__.strip()
    sys.exit(1)

def main():
    try:
        opts, args = getopt.gnu_getopt(sys.argv[1:], 'u:p:v', 
                                       ['verbose', 'tofile=',
                                        'skip-extended-insert',
                                        'add-drop-database',
                                        'user=', 'password=', 'defaults-file=', 'host='])
    except getopt.GetoptError, e:
        usage(e)

    opt_verbose = False
    opt_tofile = None
    opt_skip_extended_insert = False
    opt_add_drop_database = False
    myconf = {}
    for opt, val in opts:
        if opt in ('-v', '--verbose'):
            opt_verbose = True
        elif opt == '--tofile':
            opt_tofile = val
        elif opt == '--skip-extended-insert':
            opt_skip_extended_insert = True
        elif opt == '--add-drop-database':
            opt_add_drop_database = True
        elif opt in ('-u', '--user'):
            myconf['user'] = val
        elif opt in ('-p', '--password'):
            myconf['password'] = val
        elif opt == "--defaults-file":
            myconf['defaults_file'] = val
        elif opt == "--host":
            myconf['host'] = val
        else:
            usage()

    if not args:
        usage()

    myfs = args[0]
    limits = args[1:]

    if opt_tofile:
        if opt_tofile == '-':
            fh = sys.stdout
        else:
            fh = file(opt_tofile, "w")
    else:
        fh = mysql.mysql(**myconf)

    callback = None
    if opt_verbose:
        print "destination: " + fh.name
        callback = mysql.cb_print()

    if opt_verbose:
        pass

    mysql.fs2mysql(fh, myfs, limits, callback, 
                   opt_skip_extended_insert,
                   opt_add_drop_database)

if __name__ == "__main__":
    main()
Save this script to the same directory where tklbam's source code lives (e.g., as fs2mysql.py). Now you can run the following command:
./fs2mysql.py /tmp/extras/myfs --tofile=/tmp/mymysqldump
When you get to the bottom of this, please try to isolate the issue and report back after you figure what is going on.
Liraz Siri's picture

TKLBAM should just be pumping data into mysql. It shouldn't be consuming too much memory. MySQL on the other hand probably is. I'm very curious what kinds of scale causes MySQL to consume so much memory.
Chris Geswein's picture

I'm getting the same error when I try and restore.  It says out of memory, but no amount of RAM I throw at it helps.  Did anyone ever try manually restoring the mysqldump?  Did it work?

Liraz Siri's picture

zabbix/zabbix/auditlog is not a legal limitation. You probably meant zabbix/auditlog. Also I don't recommend trying to exclude the mysql database. You'll need it for permissions to work.
Foo's picture

I have an urgent issue, and cannot restore; the above suggestions don't appear to work.  The max_allowed_packet value keeps getting reset to 16M, then the restore dies.  I tried adding "--skip-files" to see if it would leave /etc/my.cnf alone, but I still see max_allowed_packet errors, on the same table in the same database.

Does anyone have any suggestions?  Can I do restores in pieces?  Can I directly access the backup data and do parts of it manually? Are there good examples of the tklbam-restore commands to do so, like restore a particular backup, from a particular tiem, skipping certain files, like /etc/my.cnf?

Jeremy Davis's picture

I would use nano to edit your my.cnf and make sure you save. Perhaps if you stop MySQL first and then edit it, save it and restart MySQL. So something like this:

service mysql stop
nano /etc/mysql/my.cnf
<edit the max_allowed_packet><Ctrl><X>
service mysql start

You can also exclude the MySQL DB (check the TKLBAM docs for details) if you wish which will at least allow you to get your other data back, but obviously won't solve your issue.

Foo's picture

Thanks for the suggestion, Jeremy.  I ended just restoring everything but the databases with tklbam, and was lucky enough to have a pretty recent SQL dump on another system.  Using the mysql commandline tools worked fine, even when tklbam continued to complain about packet size (I'd tried editing both via webmin and vim'ing my.cnf directly, then restarting).

So far, every time I've really been in a jam, tklbam hasn't worked for me.  Twice I've had systems run out of disk space, and there wasn't enough room to restore, and this time it was mysql and the packet business.  I guess I'll switch back to snapshots/rsync/manual mysql dumps.

Relatedly, root can no longer log into phpmyadmin, even though it works fine on the command line (mysql -u root -p).  Dunno what happened there, or what tklbam may not have restored.  Maybe the mysql management tables themselves are still fubar, but tklbam won't restore them, so I don't know what to do about that.  I'm perfectly comfortable using the command line mysql client, but I'm concerned about data corruption.

I would love it if tklbam had some kind of extract command, where you could give it a backup id and date, then just pull specific files out, so I could nab just the mysql.* tables and restore them.

Jeremy Davis's picture

But I do like your idea of being able to download and extract your TKLBAM backup, so I registered it as a Blueprint.

Also you should be able to reset the MySQL admin password with the firstboot script (IIRC /usr/lib/inithooks/bin/mysql.py) although as you say, it should be that same for the commandline and for phpMyAdmin (and I thought that it always is...)

L. Arnold's picture

With version 11 I had ongoing problems with TKLBAM and max-packets etc..  What did work very well was to get my Version 11 Magento upgraded to Magento 1.6.2 then do a TKLBAM Restore to a new Debian Machine.  No hickups what soever.  I even ran the restore from within WebMin.

I do find that certain issues like my.cnf can get lost in such Resores and it is good to have your own internal documentation/checklist to see that you get items back to normal.

If wrestling with max_allowed_packet use Jeremy's recomendation (but likely go bigger than this, like 300mb)

# the following will set max_allowed_packet to 100MB
mysql -p -e 'SET GLOBAL max_allowed_packet=100*1024*1024;'

# the following will verify the mysql server accepted the new setting
mysql -p -e 'SELECT @@global.max_allowed_packet;'

This setting will only last until the mysql server is restarted.

I've only done one Jump to Debian but worked like a charm without setting any packet size.  Try that first.

Add new comment