Bug #31418 User locks misfunctioning after mysql_change_user()
Description:
When a client acquires a user lock in a connection, calls mysql_change_user() prior to releasing the user lock with RELEASE_LOCK() [and relies on the implicit release of the user lock], no other client using a new connection opened after the mysql_change_user() can use user locks any more. In the new connection, the clients will get timeouts even for unrelated user locks.
As it's difficult to put in word, here's a list of the steps for reproducing the problem:
Connection 1:
- Connect --> OK
- SELECT GET_LOCK('change_user') --> OK
- mysql_change_user(same server/user/pass) --> OK
- SELECT IS_USED_LOCK('change_user') --> OK
- SELECT IS_FREE_LOCK('change_user') --> OK
- Close --> OK
Connection 2 (opened after Connection 1 has been closed):
- Connect --> OK
- SELECT IS_USED_LOCK('different_name') --> BLOCKS
*NOTE* This user lock has never been set before
*NOTE* Other functions (GET_LOCK()/IS_FREE_LOCK()) are affected as well
A C test case is attached. It's not nice C code, but it does expose the bug. The bug can also be verified using PHP (with libmysql AND mysqlnd) and this is where we first saw it, actually (SVN: php-mysqlnd/trunk/tests/ext/mysqli/mysqli_change_user_get_lock.phpt).
According to my testing several 5.x versions are affected. However, I did not follow up on this. I can at least reproduce the bug with 5.1.21-beta-debug on Linux.
The fact that the bug can be reproduced with PHP compiled against libmysql and the new MySQL native driver for PHP (mysqlnd => new implementation of the C/S protocol) makes me guess that its rather Server than Client related.
How to repeat:
1) Restart your MySQL Server to clean up your MySQL Servers processlist.
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 1 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
2) Compile and run the attached C test code:
> rm ./change_user
> gcc -c `/usr/local/mysql/bin/mysql_config --cflags` -Wall change_user.c
> gcc -o change_user change_user.o `/usr/local/mysql/bin/mysql_config --libs`
> ./change_user
It will print debug output and instructions:
Connecting...
Server: 5.1.21-beta-debug
Protocol: 10
Client: 5.1.21-beta
SELECT IS_USED_LOCK('lock_never_set') - lock gets never set, ergo this should never block...
SELECT GET_LOCK('change_user_lock', 10) - checking if change_user will release it implicitly...
SELECT GET_LOCK('change_user_lock') --> 1 ...
Calling change_user() ...
Checking if the user lock has been released due to change_user() ...
SELECT IS_USED_LOCK('change_user_lock') --> NULL ...
IS_FREE_LOCK('change_user_lock')? ...
SELECT IS_FREE_LOCK('change_user_lock') --> 1 ...
... done!
Re-run the program and check if the very first statement
SELECT IS_USED_LOCK('lock_never_set') will block.
As at no point a user lock 'lock_never_set' has been set,
statement must not block. If it blocks, check SHOW PROCESSLIST.
3) Run SHOW PROCESSLIST again
Note that you now have a killed connection in the process list.
> /usr/local/mysql/bin//mysql -uroot -proot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.21-beta-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | root | localhost | test | Killed | 70 | NULL | NULL |
| 3 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.01 sec)
4) Re-run the test program
The first thing that the test program does is call IS_USED_LOCK().
> ./change_user
Connecting...
Server: 5.1.21-beta-debug
Protocol: 10
Client: 5.1.21-beta
SELECT IS_USED_LOCK('lock_never_set') - lock gets never set, ergo this should never block...
5) Monitor SHOW PROCESSLIST again
mysql> show processlist
-> ;
+----+------+-----------+------+---------+------+-------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+---------------------------------------+
| 2 | root | localhost | test | Killed | 286 | NULL | NULL |
| 4 | root | localhost | test | Query | 153 | init | SELECT IS_USED_LOCK('lock_never_set') |
| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+---------------------------------------+
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.065 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: When a client acquires a user lock in a connection, calls mysql_change_user() prior to releasing the user lock with RELEASE_LOCK() [and relies on the implicit release of the user lock], no other client using a new connection opened after the mysql_change_user() can use user locks any more. In the new connection, the clients will get timeouts even for unrelated user locks. As it's difficult to put in word, here's a list of the steps for reproducing the problem: Connection 1: - Connect --> OK - SELECT GET_LOCK('change_user') --> OK - mysql_change_user(same server/user/pass) --> OK - SELECT IS_USED_LOCK('change_user') --> OK - SELECT IS_FREE_LOCK('change_user') --> OK - Close --> OK Connection 2 (opened after Connection 1 has been closed): - Connect --> OK - SELECT IS_USED_LOCK('different_name') --> BLOCKS *NOTE* This user lock has never been set before *NOTE* Other functions (GET_LOCK()/IS_FREE_LOCK()) are affected as well A C test case is attached. It's not nice C code, but it does expose the bug. The bug can also be verified using PHP (with libmysql AND mysqlnd) and this is where we first saw it, actually (SVN: php-mysqlnd/trunk/tests/ext/mysqli/mysqli_change_user_get_lock.phpt). According to my testing several 5.x versions are affected. However, I did not follow up on this. I can at least reproduce the bug with 5.1.21-beta-debug on Linux. The fact that the bug can be reproduced with PHP compiled against libmysql and the new MySQL native driver for PHP (mysqlnd => new implementation of the C/S protocol) makes me guess that its rather Server than Client related. How to repeat: 1) Restart your MySQL Server to clean up your MySQL Servers processlist. mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 1 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) 2) Compile and run the attached C test code: > rm ./change_user > gcc -c `/usr/local/mysql/bin/mysql_config --cflags` -Wall change_user.c > gcc -o change_user change_user.o `/usr/local/mysql/bin/mysql_config --libs` > ./change_user It will print debug output and instructions: Connecting... Server: 5.1.21-beta-debug Protocol: 10 Client: 5.1.21-beta SELECT IS_USED_LOCK('lock_never_set') - lock gets never set, ergo this should never block... SELECT GET_LOCK('change_user_lock', 10) - checking if change_user will release it implicitly... SELECT GET_LOCK('change_user_lock') --> 1 ... Calling change_user() ... Checking if the user lock has been released due to change_user() ... SELECT IS_USED_LOCK('change_user_lock') --> NULL ... IS_FREE_LOCK('change_user_lock')? ... SELECT IS_FREE_LOCK('change_user_lock') --> 1 ... ... done! Re-run the program and check if the very first statement SELECT IS_USED_LOCK('lock_never_set') will block. As at no point a user lock 'lock_never_set' has been set, statement must not block. If it blocks, check SHOW PROCESSLIST. 3) Run SHOW PROCESSLIST again Note that you now have a killed connection in the process list. > /usr/local/mysql/bin//mysql -uroot -proot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.21-beta-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 2 | root | localhost | test | Killed | 70 | NULL | NULL | | 3 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 2 rows in set (0.01 sec) 4) Re-run the test program The first thing that the test program does is call IS_USED_LOCK(). > ./change_user Connecting... Server: 5.1.21-beta-debug Protocol: 10 Client: 5.1.21-beta SELECT IS_USED_LOCK('lock_never_set') - lock gets never set, ergo this should never block... 5) Monitor SHOW PROCESSLIST again mysql> show processlist -> ; +----+------+-----------+------+---------+------+-------+---------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+---------------------------------------+ | 2 | root | localhost | test | Killed | 286 | NULL | NULL | | 4 | root | localhost | test | Query | 153 | init | SELECT IS_USED_LOCK('lock_never_set') | | 5 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+---------+------+-------+---------------------------------------+