MySQL Bugs: #67191: ERROR 1571 (HY000): Error during starting/stopping of the scheduler. Error code

Bug #67191	ERROR 1571 (HY000): Error during starting/stopping of the scheduler. Error code
Submitted:	11 Oct 2012 10:01	Modified:	12 Mar 2013 2:49
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.5.23	OS:	Linux (CentOS release 6.2)
Assigned to:	Arnaud Adant	CPU Architecture:	Any
Tags:	windmill

Description:
This may not be the right category. Please correct if wrong.

I have a secondly occurring event which updates a timestamp and this table is replicated to slaves. With this I can more accurately check the replication delay of a slave even if there are intermediate masters between. (SECONDS_BEHIND_MASTER does not accurately give this information.)

This has been running fine on a number of maters for some time. Most run 5.5.16 or 5.5.23.
Today I noticed a replication delay on slaves and even though the event_scheduler was supposedly enabled the changes were not happening. I recreated the event and noticed the error below.

So right now the event scheduler appears not to be working, and my only guess is that a bounce of mysqld will be necessary.

How to repeat:
root@myserver [db]> show global variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

root@myserver [db]> set global event_scheduler = 0;
Query OK, 0 rows affected (0.00 sec)

root@myserver [db]> show global variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

root@myserver [db]> set global event_scheduler = 1;
ERROR 1571 (HY000): Error during starting/stopping of the scheduler. Error code 0
root@myserver [db]> 

Suggested fix:
The event scheduler should work corrrectly.
Note: there is no logging in log-error for this issue.
Suggestion: please log any issues with the event scheduler to log-error for easier diagnosis.

Hi,

I had the same problem and managed to fix it.

it turns out mysql was at its max number of processes, I upded that and was able to start the event scheduler again.

You can check your max processes for mysql by doing cat /proc/<pid of mysql>/limits and see how many processes mysql has started by doing ps axo nlwp,pid,cmd | grep mysql and looking at the first number in front of the mysqld process.

Hi Antoine,

Yes, thank you for your feedback.

This issue is due to a bad error handling (the message should be more explicit when the scheduler thread is created). But the root cause is :

 1) some Linux distributions such as CentOS, for some reason, decided to set a very low default system wide
 limit for RLIMIT_NPROC in /etc/security/limits.d/90-nproc.conf  (new in CentOS 6)
 2) RLIMIT_NPROC does apply to threads as well on Linux :
 
 see
 http://yetanothermysqlblog.blogspot.co.uk/
 
 Extract from : man getrlimit :

 RLIMIT_NPROC
              The maximum number of threads that can be created for the real
 user ID of the calling process.  Upon encountering this limit, fork()  fails  
 with the error EAGAIN.

see also
http://dimitrik.free.fr/blog/archives/2010/11/mysql-performance-hitting-error-cant-create-...

So the solution is either :

- higher ulimit -u (requires a server restart)

- on recent kernels, use prlimit to increase the limit.

In the previous command : requires a mysqld server restart ...

Using prlimit, you do not need to restart the mysql server.

Noted in 5.7.2 changelog.

For failure to create a new thread for the event scheduler, event
execution, or new connection, no message was written to the error
log. This could lead to the impression that the event scheduler was
running normally when it was not.