innodbbackupex reaches Servers wait_timeout

[Thomas Boehme]

Hi,

during --backup innodbbackupex holds an open mysql session on the source which reaches the wait_timeout setting of the server if the backup takes longer.

Error Message:

>> log scanned up to (10 2391690023)
>> log scanned up to (10 2391690023)
>> log scanned up to (10 2391690023)
>> log scanned up to (10 2391690023)
innobackupex: Error: mysql child process has died: ERROR 2006 (HY000) at line 19: MySQL server has gone away

Here is my fix:

[root@lnxp-1158:/tmp]$ diff -u /tmp/old/innobackupex-1.5.1 /tmp/new/innobackupex-1.5.1
--- /tmp/old/innobackupex-1.5.1 2009-08-04 13:19:48.000000000 +0200
+++ /tmp/new/innobackupex-1.5.1 2009-08-04 13:07:55.000000000 +0200
@@ -1022,6 +1022,9 @@
     $now = current_time();
     print STDERR "$now $prefix Connected to database with mysql child process (pid=$mysql_pid)\n";

+ print MYSQL_WRITER "SET SESSION wait_timeout = 1800;\n"
+ or die "Connection to mysql child process failed: $!";
+
     mysql_check();
 }

KR,
Thomas

[Thomas Boehme (tboehme)]

wait_timeout = 1800 is maybe a bit short. A large dataset will probably take longer. Would be useful to have a commandline option therefore or a bigger timeout like 60.000.

[Gu Lei (bhrum)]

I modified /usr/bin/innobackupex-1.5.1 like this:

    if (compare_versions($mysql_server_version, '4.0.22') == 0
        || compare_versions($mysql_server_version, '4.1.7') == 0) {
        # MySQL server version is 4.0.22 or 4.1.7
        mysql_send "COMMIT;";
        mysql_send "set interactive_timeout=28800;"; #added by gu lei
        mysql_send "set wait_timeout=28800;"; #added by gulei
        mysql_send "FLUSH TABLES WITH READ LOCK;";
    } else {
        # MySQL server version is other than 4.0.22 or 4.1.7
        mysql_send "set interactive_timeout=28800;"; #added by gu lei
        mysql_send "set wait_timeout=28800;"; #added by gu lei
        mysql_send "FLUSH TABLES WITH READ LOCK;";
        mysql_send "COMMIT;";
    }

Xtrabackup is successfully now.

[Valentine Gostev (longbow)]

I suggest to add option to innobackupex which will allow to customize timeout value

[Stewart Smith (stewart)]

Why not periodically ping server (e.g. "SELECT 1;") so that as long as backup is running we're okay? Just prolonging the problem doesn't really look like a good solution to me.

[Alexey Kopytov (akopytov)]

https://blueprints.launchpad.net/percona-xtrabackup/+spec/rsync-for-non-innodb-files depends on this bug.

[Alexey Kopytov (akopytov)]

Bug #405602 was marked as a duplicate of this one.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-266