PXC8 locks when adding users to different nodes at the same time

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

I am using pxc8 (1:8.0.26-16-1.focal) in docker on two nodes.

OS Ubuntu 20.04

When I add users in DB in the different nodes at the same time my queries are not receive the result and hang.

In log i see the row:
[Note] [MY-000000] [WSREP] MDL conflict db=mysql table=columns_priv ticket=4 solved by abort

If I will see processlist for two nodes I see:
| 10 | system user | | | Sleep | 61 | Waiting for table metadata lock | CREATE USER IF NOT EXISTS ‘test’@‘%’ IDENTIFIED BY | 61227 | 0 | 0 |
| 264 | root | localhost | NULL | Query | 61 | wsrep: initiating TOI for write set (-1) | CREATE USER IF NOT EXISTS ‘test’@‘%’ IDENTIFIED BY | 61296 | 0 | 0 |

In this state I can not create database or alter tables.

How can I fix the situation?

Configs:
/etc/mysql/my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

collation_server        = utf8mb4_unicode_ci
character_set_server    = utf8mb4

bind-address           = 127.0.0.1
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

log_error = /var/log/mysql/error.log
log_error_verbosity = 3
binlog_expire_logs_seconds     = 864000

tls_version=TLSv1.2
pxc_encrypt_cluster_traffic = OFF

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

!includedir /etc/mysql/conf.d/

/etc/mysql/conf.d/node.cnf

[mysqld]

default_authentication_plugin=mysql_native_password
datadir=/var/lib/mysql/mysql_data
socket=/tmp/mysql.sock
skip-host-cache

default_storage_engine=InnoDB
binlog_format=ROW

innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_file_per_table           = 1
innodb_autoinc_lock_mode=2

bind_address = 0.0.0.0

wsrep_applier_threads=8
wsrep_cluster_address=gcomm://
wsrep_provider=/usr/lib/galera4/libgalera_smm.so

wsrep_cluster_name=Theistareykjarbunga

wsrep_log_conflicts
transaction_write_set_extraction = OFF
pxc_strict_mode=ENFORCING

wsrep_sst_method=xtrabackup-v2

[sst]
encrypt=0
progress=/var/lib/mysql/sst_in_progress
2 Likes
2

Hi @Mikhail_Ermolaev , welcome back to the Percona forums!

You should generally be writing to only one PXC instance at a time. What you’re hitting are network based deadlocks and they are not automatically resolving on their own. I would avoid this behaviour of writing to multiple instances.

1 Like
3

If we use proxysql to access the database and make queries from different connections, then the situation may repeat.

2 Likes
4

Even with ProxySQL you should set up your architecture so that writes are sent to a hostgroup which contains a single member.

1 Like