Re: [percona-group] XtraBackup Failing with Lock wait timeout exceeded

[Bill Karwin]

The most likely explanation for this is that you have a long-running statement (even a SELECT).

innobackupex uses FLUSH TABLES WITH READ LOCK to ensure exclusive access to the non-transactional tables before it backs them up.  But FTWRL requires that there be no statements running, and it will wait until any current statements complete.  If you have a SELECT that is running for 100 seconds for example, then FTWRL may give up before it succeeds.  And therefore innobackupex won't be able to complete backing up the non-transactional tables (e.g. mysql.*).

The workaround if you are invoking innobackupex yourself is to use the --no-lock option on the command-line.  But as far as I know, this is not possible when innobackupex is invoked as an SST method by PXC.

The other workaround is to ensure that no queries are running so long that they exceed the timeout for FTWRL, at the time that you invoke the SST for PXC.

On Jun 4, 2013, at 9:26 PM, anu...@emarketingeye.com wrote:

We have set up 3 node percona xtradb cluster on amazon ec2. When we tried to backup thought innobackupex backing up failed most of the times with following errors. The main error is "Lock wait timeout exceeded" so i have increased the lockout time but error stays same. I also checked for locks from "SHOW ENGINE INNODB STATUS" and nothing found.

130604 18:57:24  innobackupex: Continuing after ibbackup has suspended

130604 18:57:24  innobackupex: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --

130604 18:57:24  innobackupex: Connected to database with mysql child process (pid=7478)

130604 18:57:26  innobackupex: Starting to lock all tables...

>> log scanned up to (1654877011)

>> log scanned up to (1654877011)

>> log scanned up to (1654877011)

>> log scanned up to (1654877011)

>> log scanned up to (1654877011)

>> log scanned up to (1654877011)

>> log scanned up to (1654877011)

>> log scanned up to (1654877011)

innobackupex: Error: mysql child process has died: ERROR 1205 (HY000) at line 10: Lock wait timeout exceeded; try restarting transaction

 while waiting for reply to MySQL request: 'COMMIT;' at /usr/bin/innobackupex line 381.

 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

130605 09:35:26  innobackupex: Finished backing up non-InnoDB tables and files

130605 09:35:26  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1666234265'

xtrabackup: Stopping log copying thread.

.>> log scanned up to (1666234265)

.>> log scanned up to (1666234265)

xtrabackup: Transaction log of lsn (1666167213) to (1666234265) was copied.

innobackupex: Error: mysql child process has died: ERROR 1205 (HY000) at line 15: Lock wait timeout exceeded; try restarting transaction

 while waiting for reply to MySQL request: 'UNLOCK TABLES;' at /usr/bin/innobackupex line 381.

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Bill Karwin
Principal Instructor, Percona

Join us in London for Percona Live!
http://www.percona.com/live/london-2013/