# PXC8.0 MDL conflict during operations CREATE/DROP USER or GRANT **URL:** \ **Category:** Percona XtraDB Cluster 8.x\ **Tags:** percona\ **Created:** [June 13, 2024, 12:29pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008 "2024-06-13T12:29:22Z")\ **Posts on this page:** 9\ **Page:** 2
**Author:** ![Kamil\_Holubicki](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kamil_holubicki/32/5693_2.png) [@Kamil\_Holubicki](https://forums.percona.com/u/Kamil_Holubicki)\ **Post date:** [July 24, 2024, 7:46am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/21 "2024-07-24T07:46:23Z")
Hi, [https://perconadev.atlassian.net/browse/PXC-4315](https://perconadev.atlassian.net/browse/PXC-4315) Fixed in 8.0.35 I’m afraid the OOM error was not related to this message. If you have steps to reproduce OOM, please create a Jira ticket. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [July 24, 2024, 8:15am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/22 "2024-07-24T08:15:39Z")
Hello, @Kamil_Holubicki ```sql Error: [MY-000000] [WSREP] MDL conflict db= table= ticket=10 solved by abort ``` still exists in version 8.0.36 and appeared in 8.0.33, but in the messages above there was an answer that a fix is ​​expected in 8.0.37. The OOM is not related to the ticket, but the same error appeared during SST, which is strange, this did not happen before in 8.0.33. The log was filled with several thousand entries. On cluster 8.0.33, the only thing that was added was the use of roles and privileges. I recently picked up a new cluster with version 8.0.36 and restored a full backup on it using xtrabackup. Then I checked the `CREATE/DROP USER` or `GRANT` commands, no errors or freezes. But the cluster was not under traffic, we will check this. If the new cluster does not have such problems with `MDL conflict` and freezing, then we will switch to it. So I’ll come back later with the result. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [August 9, 2024, 9:43am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/23 "2024-08-09T09:43:59Z")
Hello, > [@shigaev.s](#): > > I recently picked up a new cluster with version 8.0.36 and restored a full backup on it using xtrabackup. > Then I checked the `CREATE/DROP USER` or `GRANT` commands, no errors or freezes. But the cluster was not under traffic, we will check this. If the new cluster does not have such problems with `MDL conflict` and freezing, then we will switch to it. Today we switched to a new cluster with a full xtrabackup backup restore, switched traffic to ProxySQL. Then I executed the `GRANT` command for one of the users, it went through instantly, no errors or freezes occurred. For now we are leaving it for testing and will also check for other commands. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [August 16, 2024, 3:15pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/24 "2024-08-16T15:15:54Z")
Hello, Alas, another command: ```sql GRANT SELECT, INSERT ON `db_orders`.purchased_product TO `user_name`@`%`; ``` caused problems: 1. execution was slow ```sql Query OK, 0 rows affected (3 min 48.00 sec) ``` 1. there were `Aborted Connections` and `MDL conflict` errors on the cluster: ```auto 2024-08-16 10:43:05 2024-08-16T07:43:05.407201Z 632643 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads: 2024-08-16 10:43:05 2024-08-16T07:43:05.407192Z 632643 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED -------- 2024-08-16 10:43:05 2024-08-16T07:43:05.407174Z 632643 [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort 2024-08-16 10:43:05 2024-08-1 6 10:43:05 SQL: START TRANSACTION 2024-08-16 10:43:05 THD: 634148, mode: local, state: exec, conflict: aborted, seqno: -1 2024-08-16 10:43:05 2024-08-16T07:43:05.407058Z 632643 [Note] [MY-000000] [WSREP] Victim thread: 2024-08-16 10:43:05 2024-08-16 10:43:05 SQL: FLUSH PRIVILEGES 2024-08-16 1 0:43:05 THD: 632643, mode: toi, state: exec, conflict: aborted, seqno: 43603881 2024-08-16 10:43:05 2024-08-16T07:43:05.406954Z 632643 [Note] [MY-000000] [WSREP] Winning thread: 2024-08-16 10:43:05 2024-08-16 10:43:05 2024-08-16T07:43:05.406939Z 632643 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads: 2024-08-16 10:43:05 2024-08-16T07:43:05.406848Z 632643 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED ------- ``` 1. one node is stuck, but the cluster is still up 2. MySQL log was filled with errors: ```auto [MY-010926] [Server] Access denied for user 'usr_nomenclature'@'10.9.2.146' (using password: NO) ... [Server] Aborted connection 632226 to db: 'db_stocks' user: 'usr_stocks' host: '10.9.2.146' (Got an error reading communication packets). ``` Despite the users having privileges, everyone lost access. The problem was solved by restarting the frozen node. There is a suspicion that it may be related to the use of roles and privileges for them. I will test the hypothesis - I will delete all created roles and assign privileges to each user as it was before. This is the only thing that changed in the cluster and raises suspicion. ---
**Author:** ![Kamil\_Holubicki](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kamil_holubicki/32/5693_2.png) [@Kamil\_Holubicki](https://forums.percona.com/u/Kamil_Holubicki)\ **Post date:** [August 26, 2024, 8:35am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/25 "2024-08-26T08:35:23Z")
Hi @shigaev.s , The issue should be solved in upcoming 8.0.37 ([PXC-4385](https://perconadev.atlassian.net/issues/PXC-4385)) ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [August 26, 2024, 8:40am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/26 "2024-08-26T08:40:16Z")
@Kamil_Holubicki Hi! When is the deadline? ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [August 26, 2024, 3:17pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/27 "2024-08-26T15:17:06Z")
> [@shigaev.s](#): > > There is a suspicion that it may be related to the use of roles and privileges for them. > I will test the hypothesis - I will delete all created roles and assign privileges to each user as it was before. > This is the only thing that changed in the cluster and raises suspicion. So, I managed to do it. What was done: 1. disabled ProxySQL (2 nodes in K8S) by setting the scale to 0 for the duration of the work to eliminate traffic and any load 2. manually deleted roles with the `DROP ROLE` command, and the very first command: ```sql mysql> DROP ROLE `db_bookwatch_full`; Query OK, 0 rows affected (6 min 19.98 sec) ``` took an insanely long time to execute, and in this group there was only one user with access to 1 DB with FULL privileges At this time, all cluster nodes froze, but the cluster itself was alive. After executing the command, the cluster became operational and all subsequent commands were executed instantly and without affecting the operation of the cluster nodes Most likely it has nothing to do with granting privileges through roles or to the user. 3) launched Ansible role with a task for users: create/delete user and privileges. I repeated it 5 times. During this time there were several: ```sql [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort ``` from the log: ```auto 2024-08-26 17:34:16 2024-08-26T14:34:16.707145Z 17 [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort 2024-08-26 17:34:16 2024-08-26 17:34:16 LIMIT 1 2024-08-26 17:34:16 AND column_name IN ('status', 'enabled') 2024-08-26 17:34:16 AND table_name = 'INNODB_METRICS' 2024-08-26 17:34:16 WHERE table_schema = 'information_schema' 2024-08-26 17:34:16 FROM information_schema.columns 2024-08-26 17:34:16 column_name 2024-08-26 17:34:16 SQL: SELECT 2024-08-26 17:34:16 THD: 973244, mode: local, state: exec, conflict: executing, seqno: -1 2024-08-26 17:34:16 2024-08-26T14:34:16.707126Z 17 [Note] [MY-000000] [WSREP] Victim thread: 2024-08-26 17:34:16 2024-08-26 17:34:16 SQL: FLUSH PRIVILEGES 2024-08-26 17:34:16 THD: 17, mode: toi, state: exec, conflict: committed, seqno: 116670811 2024-08-26 17:34:16 2024-08-26T14:34:16.707117Z 17 [Note] [MY-000000] [WSREP] Winning thread: 2024-08-26 17:34:16 2024-08-26 17:34:16 2024-08-26T14:34:16.707105Z 17 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads: 2024-08-26 17:34:16 2024-08-26T14:34:16.707039Z 17 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED -------- 2024-08-26 17:34:15 2024-08-26T14:34:15.572126Z 16 [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort 2024-08-26 17:34:08 2024-08-26T14:34:08.425718Z 19 [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort 2024-08-26 17:34:05 2024-08-26T14:34:05.348976Z 21 [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort 2024-08-26 17:34:03 2024-08-26T14:34:03.280406Z 13 [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort 2024-08-26 17:31:37 2024-08-26T14:31:37.753089Z 19 [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort 2024-08-26 17:31:36 2024-08-26T14:31:36.710479Z 20 [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort 2024-08-26 17:31:36 2024-08-26 17:31:36 LIMIT 1 2024-08-26 17:31:36 AND column_name IN ('status', 'enabled') 2024-08-26 17:31:36 AND table_name = 'INNODB_METRICS' 2024-08-26 17:31:36 WHERE table_schema = 'information_schema' 2024-08-26 17:31:36 FROM information_schema.columns 2024-08-26 17:31:36 column_name 2024-08-26 17:31:36 SQL: SELECT 2024-08-26 17:31:36 THD: 973072, mode: local, state: exec, conflict: executing, seqno: -1 2024-08-26 17:31:36 2024-08-26T14:31:36.710447Z 20 [Note] [MY-000000] [WSREP] Victim thread: 2024-08-26 17:31:36 2024-08-26 17:31:36 SQL: FLUSH PRIVILEGES 2024-08-26 17:31:36 THD: 20, mode: toi, state: exec, conflict: committed, seqno: 116670475 2024-08-26 17:31:36 2024-08-26T14:31:36.710428Z 20 [Note] [MY-000000] [WSREP] Winning thread: 2024-08-26 17:31:36 2024-08-26 17:31:36 2024-08-26T14:31:36.710417Z 20 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads: 2024-08-26 17:31:36 2024-08-26T14:31:36.710407Z 20 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED -------- 2024-08-26 17:31:36 2024-08-26T14:31:36.710343Z 20 [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=1 solved by abort ``` 1. returned ProxySQL to work 2. checked metrics and logs - no errors It is not clear from this why one command out of about 45 took so long to execute? All the others were executed quickly and without errors or freezes. Also, executing Ansible role with task for users went without problems. I leave it for testing until the next `CREATE/DROP`, `GRANT/REVOKE` commands. We are also waiting for a fix and its release date. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [September 3, 2024, 11:56am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/28 "2024-09-03T11:56:21Z")
Hello, > [@shigaev.s](#): > > I leave it for testing until the next `CREATE/DROP`, `GRANT/REVOKE` commands. I ran several `REVOKE` and `GRANT` commands. They were executed quickly and without errors/freezing. No `MDL conflicts`. This time there were no roles, all privileges were provided at the user level. And it looks like there are problems with **using roles** in a cluster when nodes are **under traffic**. Or such an unfixed bug that it does not always appear. Is there a fix date? ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [January 21, 2025, 10:59am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/29 "2025-01-21T10:59:09Z")
I forgot to write about the result: updating to 8.0.37 and removing roles solved the problem. [Previous page](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008.md?page=1)