Node leaves cluster when tying to grant table specific permissions to user

[AlexZIX]

Got the problem described at Percona Forums: https://forums.percona.com/t/how-to-grant-permission-in-a-galera-cluster/28942/5

When I tries to create new user with table specific privileges or change the existing user - applier thread exists. And this node can't connect back to cluster until I remove user from user tables. Database specific privileges works fine. Log from node attached.

How to reproduce this issue? Just execute following query on any node:

CREATE DATABASE my-database;
CREATE TABLE my-database.tablenamefoo (id int);
CREATE USER 'user'@'%' IDENTIFIED BY 'foobarbaz';
GRANT INSERT, UPDATE, DELETE ON my-database.tablenamefoo TO 'user'@'%';

This way you can stop entire cluster if execute such request on each node.

[ayurchen]

Hi. Does not seem to be reproducible on 8.0.37 or HEAD. Please provide software version, full configuration and error log.

[AlexZIX]

Hi, @ayurchen

Required data below. I can reproduce this as many times as neeeded.

--- SOFTWARE VERSIONS ---

MySQL with Galera Cluster
Server: Bazis MySQL Cluster DEV (192.168.254.253 via TCP/IP)
Server type: Percona Server
Server connection: SSL is not being used Documentation
Server version: 8.0.36-28.1 - Percona XtraDB Cluster (GPL), Release rel28, Revision bfb687f, WSREP version 26.1.4.3
Protocol version: 10
User: [email protected]
Server charset: UTF-8 Unicode (utf8mb3)

Operating System
Distributor ID: Ubuntu
Description: Ubuntu 22.04.4 LTS
Release: 22.04
Codename: jammy

---

--- CONFIGURATION ---

3 MySQL nodes on Ubuntu Server 22.04.
Configuration files from one node in attachments:
my.cnf
mysqld.cnf
my.cnf.txt
mysqld.cnf.txt

---

--- LOGS ---

Logs from node after leaving cluster
mysqld.log
mysqld.log

---

--- STEPS TO REPRODUCE ---

1. Create new or select existing user.
2. Try to grant table specific privileges, for ex. only SELECT for few colums.

---

--- RESULT ---

#1047 - WSREP has not yet prepared node for application use

---

[ayurchen]

Still does not reproduce on our 8.0.36 release. So it must be PXC-specific. No can do!

[AlexZIX]

What "PXC-specific" means? I have two clusters - one of 3 nodes and of 5. And both have this issue.
I've setup it using Cluster Control from SeveralNines (https://severalnines.com).

[ayurchen]

@AlexZIX What Codership develops is called "Galera cluster for MySQL", so we patch upstream MySQL to use Galera library for replication. Percona does the same with Percona XtraDB Server (their heavily modified version of MySQL) and calls it Percona XtraDB Cluster or PXC for short. Similarly MariaDB does the same with MariaDB Server.

When you were creating the cluster using Cluster Control it likely offered you a choice of variants (PXC, MariaDB, MySQL-Galera), and probably PXC was the default. So you ended up running PXC software.

The issue you have lies firmly inside PXC (not Galera library), so Codership can't help you here. Our software does not seem to have that issue. It is also may be that a later version of PXC has it fixed, so I'd suggest you research how Cluster Control can help you upgrade the cluster to a newer version.

Or better file a bug with Percona: https://perconadev.atlassian.net/

[AlexZIX]

Ok, thanks for the detailed explanation.