innobackupex: Error executing 'SET SESSION lock_wait_timeout=31536000': DBD::mysql::db do failed

[Muhammad Irfan]

I have a daily backup procecss using Xtrabackup v2.2.9 on a 5.5.42 Community MYSQL server which has just started failing with the following error message:

/db/opt/percona-xtrabackup/bin/innobackupex --defaults-file=/db/mysql/3306/etc/my.cnf --user=dbs_backup --password=xxx --stream=tar ./) | pigz -p 8 -5 > /backups/dbs/2015-05-14/backup.tar.gz

>> log scanned up to (1903249685582)
xtrabackup: Creating suspend file '/db/mysql/3306/tmp/xtrabackup_suspended_2' with pid '16768'
>> log scanned up to (1903250264417)

150514 10:40:44 innobackupex: Continuing after ibbackup has suspended
DBD::mysql::db do failed: MySQL server has gone away at /db/opt/percona-xtrabackup/bin/innobackupex line 3045.
innobackupex: got a fatal error with the following stacktrace: at /db/opt/percona-xtrabackup/bin/innobackupex line 3048
main::mysql_query('HASH(0x24eb208)', 'SET SESSION lock_wait_timeout=31536000') called at /db/opt/percona-xtrabackup/bin/innobackupex line 3456
main::mysql_lock_tables('HASH(0x24eb208)') called at /db/opt/percona-xtrabackup/bin/innobackupex line 1991
main::backup() called at /db/opt/percona-xtrabackup/bin/innobackupex line 1601
innobackupex: Error:
Error executing 'SET SESSION lock_wait_timeout=31536000': DBD::mysql::db do failed: MySQL server has gone away at /db/opt/percona-xtrabackup/bin/innobackupex line 3045.
150514 10:40:44 innobackupex: Waiting for ibbackup (pid=16768) to finish

NOTE: Same error appears with xtrabackup version 2.2.10.

# rpm -qa | grep xtrabackup
percona-xtrabackup-2.2.10-1.el6.x86_64

# xtrabackup --version
xtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )

# rpm -qa|egrep 'perl-5|perl-libs|perl-DBI'
perl-5.10.1-136.el6.x86_64
perl-libs-5.10.1-136.el6.x86_64
perl-DBIx-Simple-1.32-3.el6.noarch
perl-DBI-1.609-4.el6.x86_64
perl-libwww-perl-5.833-2.el6.noarch

# rpm -qa | grep MySQL
perl-DBD-MySQL-4.013-3.el6.x86_64
MySQL-shared-compat-5.5.44-1.el6.x86_64

# cat /etc/issue
Red Hat Enterprise Linux Server release 6.1 (Santiago)
Kernel \r on an \m

# uname -a
Linux pchiaquadb01.hq.navteq.com 2.6.32-131.17.1.el6.x86_64 #1 SMP Thu Sep 29 10:24:25 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

my.cnf:
[client]
port = 3306
socket = /db/mysql/3306/var/mysql.sock

[mysql]
default-character-set=utf8

[mysqldump]
quick
max_allowed_packet = 16M

[mysqld]
# generic configuration options
port = 3306
socket = /db/mysql/3306/var/mysql.sock
basedir = /db/opt/mysql
datadir = /db/mysql/3306/data
character_set_server = utf8

tmpdir = /db/mysql/3306/tmp
log_error = /db/mysql/3306/logs/mysql.err
back_log = 50
#skip-networking
max_connections = 1024
max_connect_errors = 10
max_allowed_packet = 1G
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
sync_binlog = 0
log_warnings
slow_query_log
long_query_time = 2
event_scheduler = 1
lower_case_table_names = 1
expire_logs_days = 14

server_id = 12750
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 128M
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 64G
innodb_data_file_path = ibdata1:10M:autoextend
# innodb_data_home_dir = /db/mysql/3306/data
innodb_write_io_threads = 8
innodb_read_io_threads = 8
#innodb_force_recovery=1
innodb_thread_concurrency = 23
innodb_flush_log_at_trx_commit = 0
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
# innodb_log_group_home_dir = /db/mysql/3306/data
innodb_max_dirty_pages_pct = 90
# innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 100000
wait_timeout=129600
innodb_file_per_table = 1

# Standard Replication

#innodb_recovery_update_relay_log = 0
log_bin = mysql-bin
log_slave_updates
binlog_format = MIXED

# Currently only InnoDB storage engine is supported
default-storage-engine = innodb

# to avoid issues with 'bulk mode inserts' using autoinc
innodb_autoinc_lock_mode = 2

# This is a must for paralell applying
innodb_locks_unsafe_for_binlog = 1

# Query Cache is not supported with wsrep
query_cache_size = 0
query_cache_type = 0

[Valerii Kravchuk (valerii-kravchuk)]

Any ideas on how to reproduce this? Maybe something useful from the error log of server failed or anything that may help to create a test case for this?

[Jacob Godin (jacobgodin)]

I'm seeing the same thing.

# xtrabackup --version
xtrabackup version 2.3.1beta1 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )

# mysql --version
mysql Ver 15.1 Distrib 5.5.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

innobackupex --user=backup --password=xxxx --stream=xbstream --extra-lsndir=/tmp /tmp | xbcloud put --storage=Swift --swift-container=$SWIFT_CONTAINER --swift-user=$SWIFT_USER --swift-auth-url=$SWIFT_URL --swift-key=$SWIFT_KEY --swift-auth-version=1 --swift-storage-url $SWIFT_STORAGE_URL --insecure --verbose $NOW

< HTTP/1.1 100 Continue
< HTTP/1.1 201 Created
< Last-Modified: Fri, 24 Jul 2015 14:11:11 GMT
< Content-Length: 0
< Etag: 8e146b8441a9dda7d4ced449b9238303
acked chunk 8e146b8441a9dda7d4ced449b9238303
< Content-Type: text/html; charset=UTF-8
< X-Trans-Id: txef40145b8d3e444e86780-0055b2477e
< Date: Fri, 24 Jul 2015 14:11:10 GMT
<
* Connection #3 to host 10.100.51.254 left intact
8e146b8441a9dda7d4ced449b9238303 is done
Error: failed to execute query SET SESSION lock_wait_timeout=31536000: MySQL server has gone away

[Shahriyar Rzayev (rzayev-sehriyar)]

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