innodbbackupex reaches Servers wait_timeout

Reported by Thomas Boehme on 2009-08-04
38
This bug affects 12 people
Affects Status Importance Assigned to Milestone
Percona XtraBackup
High
Alexey Kopytov
1.6
High
Alexey Kopytov
2.0
High
Alexey Kopytov

Bug Description

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) wrote :

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.

Changed in percona-xtrabackup:
assignee: nobody → Yasufumi Kinoshita (yasufumi-kinoshita)
Changed in percona-xtrabackup:
importance: Undecided → Medium
Gu Lei (bhrum) wrote :

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.

Percona (percona-team) on 2010-11-25
Changed in percona-xtrabackup:
assignee: Yasufumi Kinoshita (yasufumi-kinoshita) → Alexey Kopytov (akopytov)
milestone: none → 1.6
status: New → Confirmed
Changed in percona-xtrabackup:
status: Confirmed → New
assignee: Alexey Kopytov (akopytov) → Valentine Gostev (core-longbow)
Valentine Gostev (longbow) wrote :

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

tags: added: cr innobackupex
Changed in percona-xtrabackup:
importance: Medium → High
Changed in percona-xtrabackup:
status: New → Fix Committed
Changed in percona-xtrabackup:
milestone: 1.6 → 1.7
Stewart Smith (stewart) wrote :

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.

Changed in percona-xtrabackup:
assignee: Valentine Gostev (longbow) → nobody
status: Fix Committed → Triaged
Stewart Smith (stewart) on 2011-09-20
Changed in percona-xtrabackup:
assignee: nobody → Alexey Kopytov (akopytov)
status: Triaged → Fix Released
Alexey Kopytov (akopytov) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers