Bug #33507 Event scheduler creates more threads than max_connections = user lockout
Submitted: 25 Dec 2007 11:01 Modified: 26 Mar 2008 18:01
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.23, 6.0.4 OS:Any
Assigned to: Alexander Nozdrin CPU Architecture:Any
Triage: D1 (Critical) / R2 (Low) / E2 (Low)

[25 Dec 2007 11:01] Philip Stoev
Description:
The event scheduler creates a thread for each executed statement. Once the number of scheduler threads goes over max_connections, all users, *including the root user* are locked out from the server.

In my opinion, this is a potential denial-of-service.

How to repeat:
set global event_scheduler = ON;
set global max_connections = 10;
delimiter |
CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
CREATE EVENT e3 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
CREATE EVENT e4 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
CREATE EVENT e5 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
CREATE EVENT e6 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
CREATE EVENT e7 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
CREATE EVENT e8 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
CREATE EVENT e9 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
CREATE EVENT e10 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN SELECT SLEEP(10); END|
delimiter;
show processlist; # Observe that there are now 100 threads
quit;

[philips@philips bin]$ ./mysql --socket=/build/6.0.4/mysql-test/var/tmp/master.sock -uroot
ERROR 1040 (00000): Too many connections

The only recovery possible is to restart the server with event scheduler disabled from the start.

Suggested fix:
The manual states that there is always a reserved connection for the root account. Instead, create_new_thread() returns ER_CON_COUNT_ERROR even before checking for user credentials, because thread_count is more than max_connections + 1.

It appears that this calculation also does not respect the presence of any replication, ndb threads, etc.
[25 Dec 2007 12:29] Philip Stoev
This bug be a major problem for servers running long-lived events very frequently. Any event can be long-lived due to a deadlock or general server slowliness. As more event incarnations are spawned and join the deadlock game and add to server load, the smaller the chances that future event incarnations will complete before new ones are spawned.

The ON COMPLETION PRESERVE clause is not required.
[25 Dec 2007 16:54] Giuseppe Maxia
Verified as described also in version 5.1.23.
[12 Mar 2008 14:44] 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/43851

ChangeSet@1.2561, 2008-03-12 17:44:40+03:00, anozdrin@quad. +5 -0
  Fix for Bug#33507: Event scheduler creates more threads
  than max_connections -- which results in user lockout.
  
  The problem was that the variable thread_count that contains
  the number of active threads was interpreted as a number of
  active connections.
  
  The fix is to introduce a new counter for active connections.
[12 Mar 2008 14:51] Alexander Nozdrin
Pushed into 5.1-runtime.
[26 Mar 2008 14:13] Bugs System
Pushed into 5.1.24-rc
[26 Mar 2008 18:01] Paul Dubois
Noted in 5.1.24 changelog.

Threads created by the event scheduler were incorrectly counted
against the max_connections thread limit, which could lead to client
lockout.
[27 Mar 2008 17:51] Bugs System
Pushed into 6.0.5-alpha
[2 Apr 2008 16:11] Jon Stephens
Fix also noted in the 5.1.23-ndb-6.3.11 and 6.0.5 changelogs.