-- CONFLICT DETECTED -- between "DESCRIBE table_name" and "FLUSH PRIVILEGES"

General Questions
1

Transactions locks should be only on DDL commands.
GRANT and “FLUSH PRIVILEGES” are DCL coomands. Theys should’nt cause cluster conflict?
Our app very often uses “DESCRIBE table_name”, and we sometimes granting permissions. On granting we get an error.log. How to avoid this?

2023-03-10T10:58:16.625799-00:00 14 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2023-03-10T10:58:16.625821-00:00 14 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:

2023-03-10T10:58:16.625843-00:00 14 [Note] [MY-000000] [WSREP] Winning thread:
   THD: 14, mode: toi, state: exec, conflict: committed, seqno: 1104217939
   SQL: FLUSH PRIVILEGES

2023-03-10T10:58:16.625865-00:00 14 [Note] [MY-000000] [WSREP] Victim thread:
   THD: 6628131, mode: local, state: exec, conflict: executing, seqno: -1
   SQL: DESCRIBE `table_1`

2023-03-10T10:58:24.041941-00:00 17 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2023-03-10T10:58:24.042019-00:00 17 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:

2023-03-10T10:58:24.042050-00:00 17 [Note] [MY-000000] [WSREP] Winning thread:
   THD: 17, mode: toi, state: exec, conflict: committed, seqno: 1104218951
   SQL: GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES  ON `wrg_%`.* TO 'devuser35'@'%'

2023-03-10T10:58:24.042077-00:00 17 [Note] [MY-000000] [WSREP] Victim thread:
   THD: 6628673, mode: local, state: exec, conflict: executing, seqno: -1
   SQL: DESCRIBE `table_2`
2

Hello @Modestas_Mockus,
While GRANT and FLUSH PRIVILEGES would be considered DCL commands, I do believe they replicate within Galera since they modify the privileges tables and force an update of the grant cache on each node. In fact they do, you can see “mode: toi” in the error log messages above.
DESCRIBE is local, but requires checking permissions so there seems to be some race-condition/conflict when these are ran concurrently.
Why does your application need to DESCRIBE tables all the time? Is your schema not static? A constantly changing schema is typically considered a bad practice in a production environment.

1 Like
3

Hi There

We have the same issue on cluster formed by 2 nodes and and Arbiter.

Our case in a little different :slight_smile:
The confilct deteced is ON SELECT and also causes acrash on the proxysql running on another host.

2023-11-14T10:32:25.943182Z 2 [Note] [MY-000000] [WSREP] Winning thread:
THD: 2, mode: toi, state: exec, conflict: committed, seqno: 142641
SQL: flush privileges

2023-11-14T10:32:25.943189Z 2 [Note] [MY-000000] [WSREP] Victim thread:
THD: 60975, mode: local, state: exec, conflict: executing, seqno: -1
SQL: SELECT u.mail as mail, u.id as id_titolare, IF(IFNULL(ppua.status, 0) != 1 OR IFNULL(ppda.status, 0) != 1, 0, COALESCE(ppua.conversion_strategy, 0)) as conversion_strategy, ur.ident_method as ident_method, COALESCE(st.label, ‘NotIdentified’) as ident_status, ur.ident_state_last_modify_ts as ident_state_last_modify_ts, COALESCE(tcm.status_2fa, 0) as status_2fa

How is it possible that a CONFLICT happens on SELECT on the node dedicated only for READ?
Also, the most tragic event is the crash on the proxy from which the query was sent.

flush privileges is done on the WRITE NODE.