[MDEV-19703] Dead lock at CREATE USER Created: 2019-06-06  Updated: 2021-04-05  Resolved: 2021-04-05

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Locking
Affects Version/s: 10.1.37
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: BRULE Herman Jacques Roger Assignee: Sergei Golubchik
Resolution: Incomplete Votes: 0
Labels: None
Environment:

AArch64, Odroid C2, Debian 9


 Description   

Hi,

When I do: CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'b692RzMDSKjTg7AypUMEbhlx'

I dead lock some time:

SHOW FULL PROCESSLIST:
 
|  12 | root | localhost | mysql | Query   | 43178 | Waiting for table level lock    | UPDATE user LEFT JOIN db USING (Host,User) SET Create_user_priv='Y'
 
  WHERE @hadCreateUserPriv = 0 AND
 
        (user.Grant_priv = 'Y' OR db.Grant_priv = 'Y') |    0.000 |
 
|  19 | root | localhost | mysql | Query   | 43178 | Waiting for table metadata lock | ALTER TABLE user MODIFY Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv                                  |    0.000 |
 
|  23 | root | localhost | NULL  | Query   | 43178 | Waiting for table metadata lock | CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'XXXXXXXXXXXXXXXXXXXXXXX'

Cheers,

 Comments   
Comment by Sergey Vojtovich [ 2019-06-06 ]

Feels like it may relate to MDEV-10245.

Comment by BRULE Herman Jacques Roger [ 2019-06-06 ]

But here is create user... nobody create user with this version?
How by pass it? Any work around?

Comment by Sergey Vojtovich [ 2019-06-06 ]

I can imagine many installations had to create user. But I doubt anybody is running `mysql_upgrade` concurrently.

Comment by BRULE Herman Jacques Roger [ 2019-06-06 ]

My unique query started is CREATE user, no more query.
The other query is created when I call CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'XXXXXXXXXXXXX'.

Comment by Oleksandr Byelkin [ 2020-07-10 ]

Why it is "critical"? IMHO it is out of normal use pattern to update privilege tables directly and via commands.

Comment by BRULE Herman Jacques Roger [ 2020-07-10 ]

Hi, any creation account (by phpmyadmin or cli) was failed. I don't see any reason about change into commands or via php in this part.
Cheers,

Comment by Sergei Golubchik [ 2020-07-11 ]

Debian starts mysql_upgrade in background. You have two mysql_upgrade's running concurrenty, while even one is already too many.

The ideal fix would be not to run mysql_upgrade in background at all. At least we'd better figure out how did you manage to make two running at the same time. otto, any ideas?

May be phpmyadmin starts a second mysql_upgrade?

Comment by BRULE Herman Jacques Roger [ 2020-07-11 ]

Hi,

No mysql_upgrade in background I think because it's on freshly started VM (start few second ago).
Since I have updated my kernel and disable zram, all seam worked.

Cheers,

Comment by Sergei Golubchik [ 2020-07-11 ]

yes, both

UPDATE user LEFT JOIN db USING (Host,User) SET Create_user_priv='Y'
 
  WHERE @hadCreateUserPriv = 0 AND
 
        (user.Grant_priv = 'Y' OR db.Grant_priv = 'Y')

and

ALTER TABLE user MODIFY Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv

are queries that mysql_upgrade runs. See https://github.com/MariaDB/server/blob/10.1/scripts/mysql_system_tables_fix.sql#L379

and you have both these queries running at the same time, so you must have mysql_fix_privilege_tables.sql (the sql script that mysql_upgrade uses) running, in two copies at the same time.

Comment by Otto Kekäläinen [ 2021-04-04 ]

> The ideal fix would be not to run mysql_upgrade in background at all. At least we'd better figure out how did you manage to make two running at the same time. Otto Kekäläinen, any ideas?

No idea. It is called from debian-start:

debian/additions/debian-start:# Don't run full mysql_upgrade on every server restart, use --version-check to do it only once
 
debian/additions/debian-start:MYUPGRADE="/usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf --version-check"

This in turn is called from either the init script (apparently not the systemd script).

I don't fully grasp why the mariadb-upgrade must be triggered by external scripts, why does not mariadbd automatically check the data and update its data structures by itself? Without this understanding I don't think I should touch the mariadb-upgrade scripts or what calls them.

Generated at Thu Feb 08 08:53:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.