Bug in Cluster-Voting on replicated Query's (probably DDL only) caused by language

[Keden92]

Server version: 10.5.18-MariaDB-0+deb11u1-log - Debian 11 
Galera version: 4.11(r7b59af73)

[galera]
wsrep_ignore_apply_errors=0

If a Query fails, the cluster starts a voting on the GUID, with a vote hash (probably generated by the Error Message).
The vote hashes between the nodes are different, if the session variable "lc_messages" of the initiating session is set to another value than the default.
The deviating vote hash causes an "Inconsistency Error" on the initiating node and kicks it unjustified out of the cluster.
(In this Example, the error is the same on all Nodes, only in different language)

Excerpt from the log file:

2023-01-16 14:01:05 0 [Note] WSREP: Member 2(mdb01) initiates vote on 8dde4cf6-89f1-11ed-b725-9b5abea0a643:419,b3743a0251eb2236: Für Benutzer 'user' auf Host 'XXX' gibt es keine solche Berechtigung, Error_code: 1141; Engine Aria does not support rollback. Changes were committed during rollback call, Error_code: 4173;
2023-01-16 14:01:05 0 [Note] WSREP: Votes over 8dde4cf6-89f1-11ed-b725-9b5abea0a643:419:
b3743a0251eb2236: 1/3
Waiting for more votes.
2023-01-16 14:01:05 0 [Note] WSREP: Member 1(mdb02) initiates vote on 8dde4cf6-89f1-11ed-b725-9b5abea0a643:419,acab2faffa61c03d: There is no such grant defined for user 'user' on host 'XXX', Error_code: 1141; Engine Aria does not support rollback. Changes were committed during rollback call, Error_code: 4173;
2023-01-16 14:01:05 0 [Note] WSREP: Votes over 8dde4cf6-89f1-11ed-b725-9b5abea0a643:419:
acab2faffa61c03d: 1/3
b3743a0251eb2236: 1/3
Waiting for more votes.
2023-01-16 14:01:05 0 [Note] WSREP: Member 0(mdb03) initiates vote on 8dde4cf6-89f1-11ed-b725-9b5abea0a643:419,acab2faffa61c03d: There is no such grant defined for user 'user' on host 'XXX', Error_code: 1141; Engine Aria does not support rollback. Changes were committed during rollback call, Error_code: 4173;
2023-01-16 14:01:05 0 [Note] WSREP: Votes over 8dde4cf6-89f1-11ed-b725-9b5abea0a643:419:
acab2faffa61c03d: 2/3
b3743a0251eb2236: 1/3
Winner: acab2faffa61c03d
2023-01-16 14:01:05 142 [ERROR] WSREP: Inconsistency detected: Inconsistent by consensus on 8dde4cf6-89f1-11ed-b725-9b5abea0a643:419
at ./galera/src/replicator_smm.cpp:process_apply_error():1351

Triggered by:
REVOKE ALL PRIVILEGES ON test.* FROM 'user'@'XXX';

Server Site Workaround: Limit the language directory to only one language (on ALL nodes!)

[mysqld]
lc-messages-dir = /usr/share/mysql/english

The e.g. "SET lc_messages='de_DE';" will be executed without any Errors, but the Error-Description will stay in the default language.

Client Site Workaround: Don't use the lc_messages Variable and let it default.

Issue triggered by PhpMyAdmin v5.1.1
Workaround: (in File "[...]/phpmyadmin/libraries/classes/DatabaseInterface.php")
Comment out Lines 1090-1096

[Keden92]

1 Year later...
Same Cluster, same Bug, only a different Trigger:

2024-01-11 13:38:50 0 [Note] WSREP: Member 1(mdb01.domain.tld) initiates vote on 8dde4cf6-89f1-11ed-b725-9b5abea0a643:2775431557,a706b1c11703f357: Cannot add or update a child row: a foreign key constraint fails (DB_A.#sql-alter-62a-f04f7e, CONSTRAINT #sql-alter-62a-f04f7e_ibfk_1 FOREIGN KEY (a) REFERENCES DB_B.testxxx (a) ON UPDATE CASCADE), Error_code: 1452;
2024-01-11 13:38:50 0 [Note] WSREP: Votes over 8dde4cf6-89f1-11ed-b725-9b5abea0a643:2775431557:
a706b1c11703f357: 1/3
Waiting for more votes.
2024-01-11 13:38:50 48 [Note] WSREP: Got vote request for seqno 8dde4cf6-89f1-11ed-b725-9b5abea0a643:2775431557
2024-01-11 13:38:50 18 [ERROR] Slave SQL: Error 'Cannot add or update a child row: a foreign key constraint fails (DB_A.#sql-alter-4ca-12, CONSTRAINT #sql-alter-4ca-12_ibfk_1 FOREIGN KEY (a) REFERENCES DB_B.testxxx (a) ON UPDATE CASCADE)' on query. Default database: ''. Query: 'ALTER TABLE DB_A.test ADD FOREIGN KEY (a) REFERENCES DB_B.testxxx(a) ON DELETE RESTRICT ON UPDATE CASCADE', Internal MariaDB error code: 1452
2024-01-11 13:38:50 18 [Warning] WSREP: Event 1 Query apply failed: 1, seqno 2775431557
2024-01-11 13:38:50 0 [Note] WSREP: Member 2(mdb02.domain.tld) initiates vote on 8dde4cf6-89f1-11ed-b725-9b5abea0a643:2775431557,df8a7d8c5a8d2dc6: Cannot add or update a child row: a foreign key constraint fails (DB_A.#sql-alter-4ca-12, CONSTRAINT #sql-alter-4ca-12_ibfk_1 FOREIGN KEY (a) REFERENCES DB_B.testxxx (a) ON UPDATE CASCADE), Error_code: 1452;
2024-01-11 13:38:50 0 [Note] WSREP: Votes over 8dde4cf6-89f1-11ed-b725-9b5abea0a643:2775431557:
a706b1c11703f357: 1/3
df8a7d8c5a8d2dc6: 1/3
Waiting for more votes.
2024-01-11 13:38:50 0 [Note] WSREP: Member 0(mdb03.domain.tld) initiates vote on 8dde4cf6-89f1-11ed-b725-9b5abea0a643:2775431557,874f4f523b719e77: Cannot add or update a child row: a foreign key constraint fails (DB_A.#sql-alter-4cd-d, CONSTRAINT #sql-alter-4cd-d_ibfk_1 FOREIGN KEY (a) REFERENCES DB_B.testxxx (a) ON UPDATE CASCADE), Error_code: 1452;
2024-01-11 13:38:50 0 [Note] WSREP: Votes over 8dde4cf6-89f1-11ed-b725-9b5abea0a643:2775431557:
874f4f523b719e77: 1/3
a706b1c11703f357: 1/3
df8a7d8c5a8d2dc6: 1/3
Winner: df8a7d8c5a8d2dc6

[ayurchen]

The fix will be released in the next release - 8.0.36