Sample Header Ad - 728x90

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