Is there a way for SLEEP() in a stored procedure?

mysql stored-procedures sleep
Don Wool picture Don Wool · May 12, 2012 · Viewed 18.7k times · Source

I have a stored procedure I'd like to run forever, but sleep for one second in a loop. When it wakes up it would poll a table to see if it should do some work. Work only needs to be done every minute, so there is no worry about the poll table getting hit with updates from two writers at the same time.

What is the best way to SLEEP() for an interval in a stored procedure? It would be nice, actually, if it could sleep for 200 milliseconds, but one second would work too.

Answer

Roger Ray picture Roger Ray · Oct 26, 2012

I've encountered the same problem. After googling a lot, I found out that we can use 

SELECT SLEEP(<seconds>);

to delay our procedures for this many seconds. In your case, using

SELECT SLEEP(0.2);

would be just fine.

Related questions

List of Stored Procedures/Functions Mysql Command Line

How can I see the list of the stored procedures or stored functions in mysql command line like show tables; or show databases; commands.

Read More
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

Error message on MySql: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' I have gone through several other posts and was not able to solve this problem. The part affected is …

Read More
How do you debug MySQL stored procedures?

My current process for debugging stored procedures is very simple. I create a table called "debug" where I insert variable values from the stored procedure as it runs. This allows me to see the value of any variable at a …

Read More