MySQL Bugs: #33507: Event scheduler creates more threads than max

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

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.

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.

Verified as described also in version 5.1.23.

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.

Pushed into 5.1-runtime.

Pushed into 5.1.24-rc

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.

Pushed into 6.0.5-alpha

Fix also noted in the 5.1.23-ndb-6.3.11 and 6.0.5 changelogs.