Bug #6098 get_lock lock's live too long
Submitted: 14 Oct 2004 13:25 Modified: 18 Oct 2004 14:49
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.17 OS:Windows (Windows NT 4.0 service pack 6a)
Assigned to: CPU Architecture:Any

[14 Oct 2004 13:25] [ name withheld ]
Description:
When a windowsclient holding a "get_lock" lock dies, the locks lives on for a very long 
time.

How to repeat:
Connect a windows XP (possibly other windows) client to the server

   C:\MySQL\bin\mysql.exe -uroot -p -hmy_sql_sever_ip
   Password:

Select a get_lock:

   mysql> SELECT GET_LOCK( "TEST", 0 );
   +---------------------------+
    | GET_LOCK("TEST", 0) |
   +---------------------------+
   |                               1 |
   +----------------------------+
    1 row in set (0.00 sec)

Now, pull out the net cable to the windows xp client!

And try to get the lock on another machine, you cannot.  now, on the disconnected machine, close the mysql window, put back in the netcable, and reconnect to MySQL server.  It is impossible to get the lock for a long time from any machine.

The reason is that the connection to the windows xp machine never dies.  Kill off the process in winmysqladmin doesn't fix the problem either.

Suggested fix:
A variable to set a timeout for connections to the database, or simular.
[14 Oct 2004 15:55] Roberto Spadim
a KILL LOCK and SHOW LOCKS could help
[14 Oct 2004 16:51] MySQL Verification Team
Thank you for the bug report.
[18 Oct 2004 14:09] MySQL Verification Team
In my tests this only happens with Windows clients against Linux Server or
Windows Server.

However I will test again with short timeout with session values.
[18 Oct 2004 14:28] [ name withheld ]
[18 Oct 7:09am] Miguel Solorzano

>In my tests this only happens with Windows clients against Linux Server or
>Windows Server.

Yes, I have the same expirience, only windowsclients get treated this way, however, I wanted to 
produce a testcase I was sure would reproduce the bug.

>However I will test again with short timeout with session values.

OK, I am not sure how you do that, but I look forward to study your results!

Regards,
Morten .
[18 Oct 2004 14:49] MySQL Verification Team
Please use an appropriate timeout:

mysql> set wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GET_LOCK( "TEST", 0 );
+-----------------------+
| GET_LOCK( "TEST", 0 ) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

here I take off the network cable.

mysql> exit
Bye

c:\mysql\bin>mysql -h192.168.0.77 -umiguel -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.22-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT GET_LOCK( "TEST", 0 );
+-----------------------+
| GET_LOCK( "TEST", 0 ) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)