Can't bring up slave from ec2-consistent-snapshot due to uncommitted prepared transaction
0
votes
1
answer
296
views
I'm struggling with bringing up a slave instance using a snapshot created by
ec2-consistent-snapshot
, in my log it's describing the fact that an unprocessed transaction exists, but isn't that what ec2-consistent-snapshot is supposed to prevent?
My execution statement for creating snapshots is as follows... _(forgive the ansible variable placeholders)_
/usr/local/bin/ec2-consistent-snapshot-master/ec2-consistent-snapshot -q --aws-access-key-id {{ aws.access_key }} --aws-secret-access-key {{ aws.secret_key }} --region {{ aws.region }} --tag "Name={{ inventory_hostname }};Role={{ mysql_repl_role }}" --description "Database backup snapshot - {{ inventory_hostname_short }}" --freeze-filesystem /mnt/perconadata --percona --mysql-host localhost --mysql-socket /mnt/perconadata/mysql.sock --mysql-username root --mysql-password {{ mysql_root_password }} $VOLUME_ID
And the log resulting from the failed attempt to bring it up on the slave is as follows... (
InnoDB: Doing recovery: scanned up to log sequence number 64107621643
InnoDB: Transaction 1057322289 was in the XA prepared state.
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 0 row operations to undo
InnoDB: Trx id counter is 1057322752
2017-01-27 14:33:44 11313 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 33422772, file name mysql-bin.000011
2017-01-27 14:33:46 11313 [Note] InnoDB: 128 rollback segment(s) are active.
InnoDB: Starting in background the rollback of uncommitted transactions
2017-01-27 14:33:46 7f3a90c75700 InnoDB: Rollback of non-prepared transactions completed
2017-01-27 14:33:46 11313 [Note] InnoDB: Waiting for purge to start
2017-01-27 14:33:46 11313 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.34-79.1 started; log sequence number 64107621643
CONFIG: num_threads=8
CONFIG: nonblocking=1(default)
CONFIG: use_epoll=1
CONFIG: readsize=0
CONFIG: conn_per_thread=1024(default)
CONFIG: for_write=0(default)
CONFIG: plain_secret=(default)
CONFIG: timeout=300
CONFIG: listen_backlog=32768
CONFIG: host=(default)
CONFIG: port=9998
CONFIG: sndbuf=0
CONFIG: rcvbuf=0
CONFIG: stack_size=1048576(default)
CONFIG: wrlock_timeout=12
CONFIG: accept_balance=0
CONFIG: wrlock_timeout=12
CONFIG: accept_balance=0
CONFIG: wrlock_timeout=12
CONFIG: accept_balance=0
CONFIG: wrlock_timeout=12
CONFIG: accept_balance=0
CONFIG: wrlock_timeout=12
CONFIG: accept_balance=0
CONFIG: wrlock_timeout=12
CONFIG: accept_balance=0
CONFIG: wrlock_timeout=12
CONFIG: accept_balance=0
CONFIG: wrlock_timeout=12
CONFIG: accept_balance=0
CONFIG: num_threads=1
CONFIG: nonblocking=1(default)
CONFIG: use_epoll=1
CONFIG: readsize=0
CONFIG: conn_per_thread=1024(default)
CONFIG: for_write=1
CONFIG: plain_secret=
CONFIG: timeout=300
CONFIG: listen_backlog=32768
CONFIG: host=(default)
CONFIG: port=9999
CONFIG: sndbuf=0
CONFIG: rcvbuf=0
CONFIG: stack_size=1048576(default)
CONFIG: wrlock_timeout=12
CONFIG: accept_balance=0
handlersocket: initialized
2017-01-27 14:33:46 7f3dfe768840 InnoDB: Starting recovery for XA transactions...
2017-01-27 14:33:46 7f3dfe768840 InnoDB: Transaction 1057322289 in prepared state after recovery
2017-01-27 14:33:46 7f3dfe768840 InnoDB: Transaction contains changes to 1 rows
2017-01-27 14:33:46 7f3dfe768840 InnoDB: 1 transactions in prepared state after recovery
2017-01-27 14:33:46 11313 [Note] Found 1 prepared transaction(s) in InnoDB
2017-01-27 14:33:46 11313 [ERROR] Found 1 prepared transactions! It means that mysqld was not shut down properly last time and critical recovery informat$
2017-01-27 14:33:46 11313 [ERROR] Aborting
My two thoughts are that I've missed something while creating the snapshot, or I've missed something bringing up the slave from this type of snapshot, so my question is...
**Am I missing some important parameters that force mysql/percona to commit transactions prior to freezing the file system?**
-- OR --
**Is there a parameter I should be using to bring the slave up to force it to act as if it's recovering from a crash?**
Asked by oucil
(516 rep)
Jan 27, 2017, 08:30 PM
Last activity: May 13, 2025, 06:03 AM
Last activity: May 13, 2025, 06:03 AM