mysql> commit;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> show global status like '%Innodb_deadlocks%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_deadlocks | 0 |
+------------------+-------+
1 row in set (0.00 sec)
Nothing is logged to error log.
mysql> show status like 'wsrep_provider_version';
+------------------------+------------------+
| Variable_name | Value |
+------------------------+------------------+
| wsrep_provider_version | 3.12.2(rf3e626d) |
+------------------------+------------------+
1 row in set (0.00 sec)
mysql> show variables like '%innodb_print_all_deadlocks%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
I've since completely revamped my.cnf to inline with percona online wizard and galera docs, here is current my.cnf:
[mysqld]
######################
# General options
######################
datadir = /var/lib/mysql
tmpdir = /tmp
user = mysql
socket = /var/lib/mysql/mysql.sock
pid-file = mysqld.pid
port = 3306
sysdate-is-now = 1
memlock = 0
So I've just invoked deadlock following guide here: /www.percona. com/blog/ 2012/08/ 17/percona- xtradb- cluster- multi-node- writing- and-unexpected- deadlocks/
https:/
Here you can see the counter doesn't increase:
mysql> update autoinc set j='node1' where i=2
-> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; deadlocks% '; ------- -----+- ------+ ------- -----+- ------+ ------- -----+- ------+
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> show global status like '%Innodb_
+------
| Variable_name | Value |
+------
| Innodb_deadlocks | 0 |
+------
1 row in set (0.00 sec)
Nothing is logged to error log.
mysql> show status like 'wsrep_ provider_ version' ; ------- ------- ----+-- ------- ------- --+ ------- ------- ----+-- ------- ------- --+ version | 3.12.2(rf3e626d) | ------- ------- ----+-- ------- ------- --+
+------
| Variable_name | Value |
+------
| wsrep_provider_
+------
1 row in set (0.00 sec)
mysql> select @@version, @@version_ comment; ------- ------- +------ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ---+ ------- ------- +------ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ---+ ------- ------- +------ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ---+
+------
| @@version | @@version_comment |
+------
| 5.6.26-74.0-56-log | Percona XtraDB Cluster (GPL), Release rel74.0, Revision 1, WSREP version 25.12, wsrep_25.12 |
+------
1 row in set (0.00 sec)
mysql> show variables like '%innodb_ print_all_ deadlocks% '; ------- ------- ------- -+----- --+ ------- ------- ------- -+----- --+ print_all_ deadlocks | ON | ------- ------- ------- -+----- --+
+------
| Variable_name | Value |
+------
| innodb_
+------
1 row in set (0.00 sec)
I've since completely revamped my.cnf to inline with percona online wizard and galera docs, here is current my.cnf:
[mysqld]
####### ####### ####### # ####### ####### #
# General options
#######
datadir = /var/lib/mysql mysql/mysql. sock
tmpdir = /tmp
user = mysql
socket = /var/lib/
pid-file = mysqld.pid
port = 3306
sysdate-is-now = 1
memlock = 0
# MyISAM
key-buffer-size = 24M
myisam-recover = FORCE,BACKUP
# SAFETY # TRANS_TABLES, ERROR_FOR_ DIVISION_ BY_ZERO, NO_AUTO_ CREATE_ USER,NO_ AUTO_VALUE_ ON_ZER SUBSTITUTION, NO_ZERO_ DATE,NO_ ZERO_IN_ DATE,ONLY_ FULL_GROUP_ BY
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_
O,NO_ENGINE_
innodb = FORCE
innodb-strict-mode = 1
# DATA STORAGE #
datadir = /var/lib/mysql/
# CACHES AND LIMITS # n-cache = 4096
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definitio
table-open-cache = 4096
# INNODB # log-files- in-group = 2 log-file- size = 200M flush-log- at-trx- commit = 2 file-per- table = 1 buffer- pool-size = 2G stats-on- metadata= 0 storage- engine= innodb print_all_ deadlocks = 1
innodb-flush-method = O_DIRECT
innodb-
innodb-
innodb-
innodb-
innodb-
#innodb-
default-
innodb_
# LOGGING # mysql/mysql- error.log not-using- indexes = 1 mysql/mysql- slow.log
#log_output = table
log-error = /var/lib/
log-queries-
slow-query-log = 1
slow-query-log-file = /var/lib/
####### ####### ####### # ####### ####### #
# PXC/Galera options
#######
# In order for Galera to work correctly binlog format should be ROW storage_ engine= InnoDB
binlog_format=ROW
default_
binlog_ row_image= minimal # 5.6 only
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera autoinc_ lock_mode= 2
innodb_
# galera settings /usr/lib64/ libgalera_ smm.so name=phoenixdev -sqlcluster address= gcomm:/ /xxx1,xxx2, xxx3?pc. wait_prim= no address= xxx1 options= "gcs.fc_ limit=500; gcs.fc_ master_ slave=YES; gcs.fc_factor=1.0; gcache.size=128M; evs.keepalive_ period= PT3S; evs.in check_period= PT15S; evs.suspect_ timeout= PT60S; evs.inactive_ timeout= PT2M; evs.install_ timeout= PT2M; evs.send_ window= 1024; evs.user method= xtrabackup- v2 # can also be mysqldump, xtrabackup, xtrabackup-v2 auth="sstuser: sstpasswordshou ldbesecret" retry_autocommi t=5 # this is to auto-retry deadlocks nonPK=1 _myisam= 1
wsrep_provider=
wsrep_cluster_
wsrep_cluster_
wsrep_node_
wsrep_provider_
active_
_send_window=512;"
wsrep_sst_
wsrep_sst_
#wsrep_
wsrep_certify_
wsrep_replicate