Skip to content

Instantly share code, notes, and snippets.

@wederbrand
Last active May 28, 2018
Embed
What would you like to do?
mysql 5.7.22 XA Error
#!/bin/bash -x
# clear out the old stuff
docker rm -f master
docker rm -f slave
cat > my.cnf <<CONFIG
[client]
user=root
password=root
CONFIG
cat > master.cnf <<MASTER
[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
binlog-format = STATEMENT
general-log = on
general_log_file = query.log
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'
MASTER
cat > slave.cnf <<SLAVE
[mysqld]
server-id = 2
log-bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog-format = STATEMENT
general-log = on
general_log_file = query.log
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'
SLAVE
# create two mysqls in docker, let the second one be the slave of the first one
cat <<DOCKER-COMPOSE |
version: '3.2'
services:
master:
container_name: master
image: mysql:5.7.22
environment:
- MYSQL_ROOT_PASSWORD=root
volumes:
- "./master.cnf:/etc/mysql/conf.d/master.cnf"
- "./my.cnf:/my.cnf"
slave:
container_name: slave
image: mysql:5.7.22
environment:
- MYSQL_ROOT_PASSWORD=root
volumes:
- "./slave.cnf:/etc/mysql/conf.d/slave.cnf"
- "./my.cnf:/my.cnf"
DOCKER-COMPOSE
/usr/local/bin/docker-compose -f - up -d
# wait for both instances to run (look for "ready for connections" on stderr)
docker logs -f master 2>&1 | grep -q 'mysqld: ready for connections'
docker logs -f slave 2>&1 | grep -q 'mysqld: ready for connections'
# configure and start the slave
until docker exec slave mysql --defaults-file=/my.cnf -e "change master to MASTER_HOST='master', MASTER_USER='root', MASTER_PASSWORD='root';"
do echo "waiting for slave to be ready"; sleep 1; done
sleep 1
docker exec slave mysql --defaults-file=/my.cnf -e "start slave;"
# create a table to test on
docker exec master mysql --defaults-file=/my.cnf -e "create database slask;"
docker exec master mysql --defaults-file=/my.cnf -e "create table slask.insert_error (id int, name varchar(255), primary key (id));"
# make the MASTER pre-test state, 3 rows => 2 gaps
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (1, 'dude');"
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (2, 'dude');"
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (3, 'dude');"
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (4, 'dude');"
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (5, 'dude');"
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (6, 'dude');"
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (7, 'dude');"
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (8, 'dude');"
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (9, 'dude');"
docker exec master mysql --defaults-file=/my.cnf -e "insert into slask.insert_error values (10, 'dude');"
# make the SLAVE pre-test state, create a gap
until docker exec slave mysql --defaults-file=/my.cnf -e "delete from slask.insert_error where id = 7;"
do echo "waiting for table to appear on slave"; sleep 1; done
# do two parallell XA transactions that insert into the two gaps (that's only one gap on the slave)
docker exec master mysql --defaults-file=/my.cnf -e "XA START 'one'; insert ignore into slask.insert_error values (7, 'dude'); XA END 'one'; XA PREPARE 'one';"
docker exec master mysql --defaults-file=/my.cnf -e "XA START 'two'; insert ignore into slask.insert_error values (7, 'dude'); XA END 'two'; XA PREPARE 'two';"
# commit them in order
docker exec master mysql --defaults-file=my.cnf -e "XA COMMIT 'one';"
docker exec master mysql --defaults-file=my.cnf -e "XA COMMIT 'two';"
sleep 1
# watch it blow up!
echo "slave not catching up"
docker exec slave mysql --defaults-file=/my.cnf -e'show slave status\G' | grep Master_Log_Pos
echo "two processes, slave sql thread and select on slave"
docker exec -it slave mysql --defaults-file=/my.cnf -e'show processlist'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment