# 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:** 20\ **Page:** 1
**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:** [June 13, 2024, 12:29pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/1 "2024-06-13T12:29:22Z")
Hello! There is a cluster of 3 version nodes: ```auto Server version: 8.0.33-25.1 Percona XtraDB Cluster (GPL), Release rel25, Revision 0c56202, WSREP version 26.1.4.3 ``` - `wsrep_OSU_method | TOI` - `wsrep_log_conflicts | ON` When executing commands: `CREATE/DROP USER` or `GRANT` the server freezes and on all nodes there are errors in the logs: ```auto [Note] [MY-000000] [WSREP] MDL conflict db= table= ticket=10 solved by abort ``` In this case it helps: 1. stop nodes 2 and 3: `systemctl stop mysql` 2. start 2 nodes: `systemctl start mysql` 3. after SST 2 nodes start 3 nodes: `systemctl start mysql` 4. after SST 3 nodes, the cluster was restored In some cases the commands: `CREATE/DROP USER` or `GRANT` can be successfully completed or performed with a strong delay, for example, 1.5 minutes or more. The commands are executed on 1 node and there are no similar commands in the cluster, i.e. without parallel execution. This behavior has not previously been observed with such commands. Tell me what the problem is and how to fix it? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [June 13, 2024, 5:05pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/2 "2024-06-13T17:05:23Z")
If you switch to `wsrep_OSU_method=NBO`, do you get the same behavior? Are there any other table locks happening during the CREATE USER? During the stall, can you run SHOW ENGINE INNODB STATUS? ---
**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:** [June 14, 2024, 7:44am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/3 "2024-06-14T07:44:25Z")
> [@matthewb](#): > > If you switch to `wsrep_OSU_method=NBO`, do you get the same behavior? I haven’t tested it with the `wsrep_OSU_method=NBO` parameter. Will it be enough to do it at the **Session** level? And then execute the commands? > [@](#): > > During the stall, can you run SHOW ENGINE INNODB STATUS? I’ll check it out and come back again. Thanks for the answer! ---
**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:** [June 17, 2024, 7:15am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/4 "2024-06-17T07:15:15Z")
> [@matthewb](#): > > During the stall, can you run SHOW ENGINE INNODB STATUS? You can’t see the status because everything freezes until you turn off 2 nodes. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [June 17, 2024, 7:33pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/5 "2024-06-17T19:33:31Z")
> [@shigaev.s](#): > > I haven’t tested it with the `wsrep_OSU_method=NBO` parameter. > Will it be enough to do it at the **Session** level? And then execute the commands? Yes, SET SESSION wsrep\_osu\_method=NBO then run the CREATE USER. Any other information in the mysql error log during the stalls? ---
**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:** [June 25, 2024, 3:06pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/6 "2024-06-25T15:06:11Z")
```auto mysql> SET SESSION wsrep_osu_method=NBO; Query OK, 0 rows affected (0,00 sec) mysql> DROP USER `petrov.s`@`%`; ERROR 1235 (42000): This version of MySQL doesn't yet support 'this query in wsrep_OSU_method NBO' ``` log error.log: ```auto 2024-06-25 18:03:30 2024-06-25T15:03:30.088700Z 1327819 [ERROR] [MY-000000] [WSREP] Fail to replicate: DROP USER `petrov.s`@`%` ``` When deleting, conflicts arose again. You can’t see anything else, because… the server hangs. Very strange behavior. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [June 25, 2024, 5:56pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/7 "2024-06-25T17:56:30Z")
Can you put together a repeatable test case? I’d like to try it. If I can repeat it as well, then we can open a bug report. ---
**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:** [June 26, 2024, 8:20am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/8 "2024-06-26T08:20:47Z")
The test case is like this: There is a user table You need to do one of the following: ```auto CREATE USER 'test_user'@'%' IDENTIFIED BY 'password'; GRANT SELECT ON `db_test`.* TO 'test_user'@'%'; DROP USER `petrov.s`@`%`; ``` It doesn’t matter what user, privileges or database. When responding to such requests, the cluster on all nodes has errors and freezes. It helps to stop all nodes, bootstrap and connect one node at a time. Not long ago they started using role: [https://dev.mysql.com/doc/refman/8.0/en/roles.html](https://dev.mysql.com/doc/refman/8.0/en/roles.html) Could this be related? I’ll try to repeat this on a trust cluster of the same version. ---
**Author:** ![Modestas\_Mockus](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/modestas_mockus/32/9024_2.png) [@Modestas\_Mockus](https://forums.percona.com/u/Modestas_Mockus)\ **Post date:** [June 26, 2024, 8:53am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/9 "2024-06-26T08:53:21Z")
I have some hanging issues if i run all selects in one command with flush privelegies ant the end. No issues if run comand one be one with few seconds between them and before flush privelegies ---
**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:** [June 26, 2024, 8:57am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/10 "2024-06-26T08:57:30Z")
Yes, only one command is executed. Several are given, because on any of them leads to problems. This behavior did not exist before. When downloading the `pt-show-grants` utility, everything is identical. What else can you check? Can using roles have this effect? ---
**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:** [June 26, 2024, 1:07pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/11 "2024-06-26T13:07:27Z")
Could it be related to a bug [[PXC-4315] - Percona JIRA](https://perconadev.atlassian.net/browse/PXC-4315)? I am studying the updates and planning to upgrade. After that, check the commands. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [June 26, 2024, 5:38pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/12 "2024-06-26T17:38:55Z")
Hello @shigaev.s, I created a 3node PXC 8.0.35 and I was unable to repeat this. I ran the create/drop use as you provided and I did not experience any cluster stalls at all. Can you please upgrade to the latest PXC and see if your issue remains? ---
**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:** [June 27, 2024, 7:57am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/13 "2024-06-27T07:57:33Z")
Yesterday we upgraded to version 8.0.36-28 (04/03/2024). Today we tested several commands: `REVOKE`, `GRANT`, `DROP USER` - no more `MDL conflict` and cluster freeze. It seems that the bug fix in [[PXC-4315] - Percona JIRA](https://perconadev.atlassian.net/browse/PXC-4315) helped. We’ll leave it for testing for now. I’ll come back later and if it doesn’t happen again, I’ll mark the ticket as resolved. ---
**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 10, 2024, 7:58am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/14 "2024-07-10T07:58:47Z")
The problem repeated itself. On the `DROP USER` command, the entire cluster froze, no logs, no metrics. Moreover, the user was eventually deleted. I checked this after restoring the cluster. It is sad. The reason is not known and how to debug it. Any ideas? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [July 10, 2024, 2:38pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/15 "2024-07-10T14:38:12Z")
Hello @shigaev.s, [https://perconadev.atlassian.net/browse/PXC-4385](https://perconadev.atlassian.net/browse/PXC-4385) This fix will be in 8.0.37 ---
**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 10, 2024, 2:42pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/16 "2024-07-10T14:42:20Z")
Hello @matthewb , will wait. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [July 10, 2024, 3:58pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/17 "2024-07-10T15:58:05Z")
If you can create a coredump while the server is hung, that would help us. (kill -11) and create a jira ticket, uploading the compressed coredump file. ---
**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 10, 2024, 4:54pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/18 "2024-07-10T16:54:10Z")
[THIS](https://www.percona.com/blog/getting-mysql-core-file-on-linux/) blogpost can be helpful. When the server is stuck, just kill it with kill -11. It will cause core file creation. ---
**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 11, 2024, 7:16am UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/19 "2024-07-11T07:16:16Z")
Hello, I’ll try this if it fails. ---
**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 23, 2024, 2:33pm UTC](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008/20 "2024-07-23T14:33:18Z")
Hello, @matthewb when is version 8.0.37 expected? The other day there was this error: ```sql [MY-000000] [WSREP] MDL conflict db= table= ticket=10 solved by abort ``` arose in a production environment, but this time during SST, when one of the nodes fell with OOM. This didn’t happen before. The cluster version there is still 8.0.33. Stopping the frozen node also helped after bootstrap. [Next page](https://forums.percona.com/t/pxc8-0-mdl-conflict-during-operations-create-drop-user-or-grant/31008.md?page=2)