Comment 5 for bug 1516933

Dom (launchpal) wrote :

So I've just invoked deadlock following guide here:
 https://www.percona.com/blog/2012/08/17/percona-xtradb-cluster-multi-node-writing-and-unexpected-deadlocks/

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;
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> 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%';
+----------------------------+-------+
| 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

# MyISAM
key-buffer-size = 24M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZER
O,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
innodb = FORCE
innodb-strict-mode = 1

# DATA STORAGE #
datadir = /var/lib/mysql/

# CACHES AND LIMITS #
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-definition-cache = 4096
table-open-cache = 4096

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 200M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 2G
#innodb-stats-on-metadata=0
default-storage-engine=innodb
innodb_print_all_deadlocks = 1

# LOGGING #
#log_output = table
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

######################
# PXC/Galera options
######################

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
default_storage_engine=InnoDB

binlog_row_image=minimal # 5.6 only

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# galera settings
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_name=phoenixdev-sqlcluster
wsrep_cluster_address=gcomm://xxx1,xxx2,xxx3?pc.wait_prim=no
wsrep_node_address=xxx1
wsrep_provider_options="gcs.fc_limit=500; gcs.fc_master_slave=YES; gcs.fc_factor=1.0; gcache.size=128M; evs.keepalive_period=PT3S; evs.in
active_check_period=PT15S; evs.suspect_timeout=PT60S; evs.inactive_timeout=PT2M; evs.install_timeout=PT2M; evs.send_window=1024; evs.user
_send_window=512;"
wsrep_sst_method=xtrabackup-v2 # can also be mysqldump, xtrabackup, xtrabackup-v2
wsrep_sst_auth="sstuser:sstpasswordshouldbesecret"
#wsrep_retry_autocommit=5 # this is to auto-retry deadlocks
wsrep_certify_nonPK=1
wsrep_replicate_myisam=1