Hello Community
I set up a backup of a 4TB MySQL-5.7.21 instance using percona-xtrabackup 2.4.21, The log shows the process started but got terminated with below error
210801 08:17:19 [01] …done
210801 08:17:19 >> log scanned up to (3368741918185)
Error: failed to execute query ‘SET SESSION lock_wait_timeout=31536000’: 2006 (HY000) MySQL server has gone away
I checked the global lock_wait_timeout variable and it is set to the exact value that the log shows
mysql> select @@lock_wait_timeout;
±--------------------+
| @@lock_wait_timeout |
±--------------------+
| 31536000 |
±--------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_lock_wait_timeout;
±---------------------------+
| @@innodb_lock_wait_timeout |
±---------------------------+
| 50 |
±---------------------------+
1 row in set (0.00 sec)
mysql>
If anyone can provide a solution to resolve this would be great.
1 Like
MySQL is killing PXB connection. Can you please share the full backup log from start until the issue occurs ?
Also, please adjust your mysql log_error_verbosity to 3 and upload your mysql error log too.
1 Like
Hi @Marcelo_Altmann
PFA xtrabackup log file for your reference.
This is already in place
mysql> select @@log_error_verbosity;
±----------------------+
| @@log_error_verbosity |
±----------------------+
| 3 |
±----------------------+
1 row in set (0.00 sec)
mysql.err log corresponding to xtrabackup issue shows below entry
2021-08-01T12:31:12.564446Z 473607 [Note] Aborted connection 473607 to db: ‘unconnected’ user: ‘dbbackup’ host: ‘localhost’ (Got an error reading communication packets)
percona-xtrabackup-2.4.21-logs-Error.txt (139.4 KB)
1 Like
Hi @Rudrojit
Here is the timeframe of events:
210801 11:08:11 Connecting to MySQL server host: localhost, user: dbbackup, password: set, port: 3306, socket: /bmp_test/config/mysql.sock
Xtrabackup connected to your db and started to do the backup of your files
2021-08-01T12:31:12.564446Z 473607 [Note] Aborted connection 473607 to db: ‘unconnected’ user: ‘dbbackup’ host: ‘localhost’ (Got an error reading communication packets)
It seems to have a TZ difference between the log events, but this happened around 23 minutes after PXB connected. MySQL closed the connection (probably due to inactivity)
Error: failed to execute query 'SET SESSION lock_wait_timeout=31536000': 2006 (HY000) MySQL server has gone away
PXB tried to execute a query but the connection has already been closed.
Xtrabackup when connecting, sets the wait_timeout to 2147483 (24+ days). This is the variable that controls how long an IDLE connection will remain open before been closed due to inactivity.
I suspect that you have some external tool like pt-kill that is killing idle connections.
1 Like
@Marcelo_Altmann
Thanks, I will change the wait_timeout parameter and see if it helps.
No, we are not using any tool like pt-kill, in fact, we are pitching to start using Percona MySQL instead of community server, this whole setup is a trial run on one of the test servers.
I must say I am quite intrigued now to use Percona tool kit as well.
1 Like