0

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?

0

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

The one caveat is that there seems to be a bug relating to this feature in MySQL 5.6.x which I'm now debugging due to missing foreign keys during some other inserts as the slave catches up in the binlog. Not sure if related. At least the first error was dealt with and I could get the server up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.