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 |
+----+------+-----------+------+---------+------+-------+---------------------------------------+
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 | +----+------+-----------+------+---------+------+-------+---------------------------------------+