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 12:01	Modified:	26 Mar 19:01
Reporter:	Philip Stoev
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	6.0.4, 5.1.23	OS:	Any
Assigned to:	Alexander Nozdrin	Target Version:	5.1+
Triage:	D1 (Critical) / R2 (Low) / E2 (Low)

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.