# Mysql – Can’t bring up slave from ec2-consistent-snapshot due to uncommitted prepared transaction

amazon ec2MySQLperconasnapshottransaction

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?

I was able to get past the transaction in prepared state errors by bringing up the server using the --tc-heuristic-recover argument set to COMMIT which deals with the prepared transactions at launch. The syntax was as follows (on centos)...
sudo service mysql start --tc-heuristic-recover=COMMIT