| Bug #10374 | GET_LOCK does not let connection to close on the server side if it's aborted | ||
|---|---|---|---|
| Submitted: | 5 May 2005 3:02 | Modified: | 23 Jul 2010 12:59 |
| Reporter: | Ledion B | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 4.1.10a,5.0.36BK | OS: | Any (*) |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
| Tags: | bfsm_2007_03_01 | ||
[5 May 2005 13:44]
Geert Vanderkelen
Hi, Reproduced your example using 4.1.11 and 5.0 from source. Not really bug, but might be nice to have some check when connection is gone, to avoid reaching max_connection.. But it is more a application site issue.. I'll let the developers decide here :) Thanks, Geert
[27 Sep 2005 9:39]
Adam Newby
This would seem to me to be a bug, rather than a feature. On our system, we've had this happen when a machine which is connected and is holding a user lock reboots. In that circumstance, the MySQL server doesn't drop the connection, and the lock is then held for an extremely long period (presumably as long as wait_timeout, default 28800 seconds), blocking all other threads from acquiring it, and requiring intervention (by killing the thread holding the lock) to rectify matters. It seems nonsensical for it to be possible for a lock to be held by a process which was running on a machine which is now offline!
[21 Jan 2006 0:25]
Paul DuBois
I have documented this behavior as a bug in the description for GET_LOCK(). I have left this report as Verified and have unassigned myself from it so that it can be reassigned to a developer to be fixed.
[3 Jul 2007 18:59]
Kolbe Kegel
As soon as the original lock is released, the dead connection will "get" the lock, and when that happens it will be immediately released and the connection will disappear. Are you seeing something different? The connection will also disappear as soon as the timeout value is reached. A solution in this case is to avoid using an extraordinarily high wait time for the GET_LOCK() function. A dead connection will wait no longer and cause no problems than a live connection, as far as I can tell. If I am mistaken, please clarify.
[19 Mar 2008 0:49]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/44210 ChangeSet@1.2604, 2008-03-18 21:49:09-03:00, davi@mysql.com +8 -0 Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted The problem is that the server doesn't detect aborted connections which are waiting on a lock or sleeping (user sleep), wasting precious system resources and also a potential DOS vector. The solution is to peek at the connection every five seconds to verify if the connection is not aborted.
[19 Mar 2008 14:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/44227 ChangeSet@1.2601, 2008-03-19 11:04:50-03:00, davi@mysql.com +8 -0 Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted The problem is that the server doesn't detect aborted connections which are waiting on a lock or sleeping (user sleep), wasting system resources for a connection that is already dead. The solution is to peek at the connection every five seconds to verify if the connection is not aborted. A aborted connection is detect by polling the connection socket for available data to be read or end of file. In case of eof, the wait is aborted and the connection killed.
[27 Mar 2008 20:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/44535 ChangeSet@1.2608, 2008-03-27 17:22:36-03:00, davi@mysql.com +7 -0 Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted The problem is that the server doesn't detect aborted connections which are waiting on a lock or sleeping (user sleep), wasting system resources for a connection that is already dead. The solution is to peek at the connection every five seconds to verify if the connection is not aborted. A aborted connection is detect by polling the connection socket for available data to be read or end of file and in case of eof, the wait is aborted and the connection killed.
[28 Mar 2008 12:36]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/44581 ChangeSet@1.2608, 2008-03-28 09:36:26-03:00, davi@mysql.com +7 -0 Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted The problem is that the server doesn't detect aborted connections which are waiting on a lock or sleeping (user sleep), wasting system resources for a connection that is already dead. The solution is to peek at the connection every five seconds to verify if the connection is not aborted. A aborted connection is detect by polling the connection socket for available data to be read or end of file and in case of eof, the wait is aborted and the connection killed.
[28 Mar 2008 12:39]
Davi Arnaut
Queued to 6.0-runtime
[20 Apr 2008 13:01]
Bugs System
Pushed into 6.0.6-alpha
[23 Apr 2008 17:26]
Paul DuBois
Noted in 6.0.6 changelog. If a connection was waiting for a GET_LOCK() lock or a SLEEP() call, and the connection aborted, the server did not detect this and thus did not close the connection. This caused a waste of system resources allocated to dead connections. Now the server checks such a connection every five seconds to see whether it has been aborted. If so, the connection is killed (and any lock request is aborted).
[17 Jun 2009 19:28]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:11)
[29 Oct 2009 8:32]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/88563 2900 Alexander Nozdrin 2009-10-29 Backporting patch for Bug#10374 from 6.0. Original revision: ------------------------------------------------------------ revno: 2597.37.3 revision-id: sp1r-davi@mysql.com/endora.local-20080328123626-16430 committer: davi@mysql.com/endora.local timestamp: Fri 2008-03-28 09:36:26 -0300 message: Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted The problem is that the server doesn't detect aborted connections which are waiting on a lock or sleeping (user sleep), wasting system resources for a connection that is already dead. The solution is to peek at the connection every five seconds to verify if the connection is not aborted. A aborted connection is detect by polling the connection socket for available data to be read or end of file and in case of eof, the wait is aborted and the connection killed. ------------------------------------------------------------
[10 Nov 2009 19:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/89998 2920 Davi Arnaut 2009-11-10 Backport of Bug#10374 to mysql-next-mr ------------------------------------------------------------ revno: 2597.37.3 revision-id: sp1r-davi@mysql.com/endora.local-20080328123626-16430 parent: sp1r-anozdrin/alik@quad.opbmk-20080327125300-11290 committer: davi@mysql.com/endora.local timestamp: Fri 2008-03-28 09:36:26 -0300 message: Bug#10374 GET_LOCK does not let connection to close on the server side if it's aborted The problem is that the server doesn't detect aborted connections which are waiting on a lock or sleeping (user sleep), wasting system resources for a connection that is already dead. The solution is to peek at the connection every five seconds to verify if the connection is not aborted. A aborted connection is detect by polling the connection socket for available data to be read or end of file and in case of eof, the wait is aborted and the connection killed. @ include/violite.h Export vio_peek_read function. @ mysql-test/r/dirty_close.result Add test case result for Bug#10374 @ mysql-test/t/dirty_close.test Add test case for Bug#10374 @ sql/item_func.cc While waiting for a condition to be signaled, check if the connection is not broken every INTERRUPT_INTERVAL seconds. @ sql/sql_class.cc Add function which checks if the client connection was aborted. @ sql/sql_class.h Add function prototype. @ vio/viosocket.c Add poll and peek functions for Windows and Unix.
[10 Nov 2009 19:38]
Davi Arnaut
Queued to mysql-next-mr-runtime
[20 Nov 2009 12:54]
Bugs System
Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:13)
[20 Nov 2009 12:58]
Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:13)
[26 Nov 2009 15:05]
Paul DuBois
Noted in 5.6.0 changelog. Already fixed in 6.0.x.
[6 Mar 2010 11:08]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:16)
[6 Mar 2010 18:23]
Paul DuBois
Moved 5.6.0 changelog entry to 5.5.3.
[23 Jul 2010 12:26]
Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:35]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
Description: If an application level lock is in use and another thread (connection) wants to get that lock (and is willing to wait a long time), the thread will block. Now, if the connection (of the waiting thread) is closed either gracefully or not the server does not seem to close the connection. When doing a show processlist the query is still active. I think this can lead into max_connections. How to repeat: Two connections to the database, and a lock lock1 that is free Connection1 issues Select get_lock("lock1", 10000000); <-- returns immediately Connection2 issues Select get_lock("lock1", 10000000); <-- will block for 115 days close connection2 (assuming you're not running in a single threaded evironment) on the server run show processlist Suggested fix: I have not looked at mysql source, but I have extensive experience with multithreaded environment. If the close of the connection is detected in another thread but the one that runs queries, then an interruption would suffice. Otherwise there needs to be some other mechanism that determines when a connection closes and interrupt the query thread of that connection. Again I don't know how mysql is organized, so feel free to ignore this piece of advice.