Mysql Event Not Working

Question

I have added the following simple test event on my mysql database via phpmyadmin:

CREATE DEFINER=`root`@`localhost` EVENT `my_event` 
ON SCHEDULE EVERY 1 MINUTE STARTS '2013-05-27 00:00:00' 
ON COMPLETION NOT PRESERVE ENABLE DO 
BEGIN
    UPDATE `test` SET `name`="z";
END

My environment is mac + MAMP Pro. I am expecting to change all rows on my 'test' table with name 'z' within a minute. But not happening so.

Do I have to something additional to get my events start working?

Output of "SHOW PROCESSLIST":

Thanks.

Stigmatize

Can you provide the results of the query SHOW PROCESSLIST; ? Maybe the scheduler is not started. – Disproof

just added the output to my question description. I didn't do anything to start the scheduler. How to start it please? – Stigmatize

Answer 1

Events are run by the scheduler, which is not started by default. Using SHOW PROCESSLIST is possible to check whether it is started. If not, run the command

 SET GLOBAL event_scheduler = ON;

to run it.

Disproof

Be careful: it's possible that SHOW PROCESSLIST won't show the running process even if it is in fact running - that happens in my Plesk based web hosting in an account where I don't have root privileges. The event_scheduler is running, but it doesn't appear in the list. For me it's better to use select @@event_scheduler; it will return ONin case it is. – Dagny

@Jac_opo i have follow your instruction but nothing happen, i have set global to be on and restart my server but nothing happen, by the if my event about one month how can i tested ? i tested by change date but nothing happend, – Pash

@Stigmatize how can i debug it ? i use phpmyadmin but doesn't work, please goes to here #35400640 – Pash

Answer 2

Verify if the event_scheduler is On - execute the following command:

SHOW PROCESSLIST;

It'll output a table/entries, you must look for an entry with User event_scheduler, and Command Daemon:

Id           User         Host      db  Command Time    State            Info
22870   event_scheduler localhost   \N   Daemon  23    Waiting for next activation  \N

OR, you can also verify using the following command:

SELECT @@global.event_scheduler;

The result should be ON, otherwise set it off (will get 0 for the command), as stated in the next section.

---

If you don't have any such entry (as above), you may start the event scheduler using the following command:

 SET GLOBAL event_scheduler = ON;

Once done, you can verify if it has been executed properly using the SHOW PROCESSLIST command, as mentioned above.

Beanpole

Answer 3

For those wondering how to enable it by default at startup, add the following to your config file (my.ini, my.cnf):

#Event scheduler can be set to 1 (On), 0 (Off), or Disabled
event_scheduler=1

Restart of the service is required in this case, so if you want minimal disruption, add this to the config file, and then run the SQL:

SET GLOBAL event_scheduler = ON;

That way, it will run for the current process, and if the server is restarted it will still work.

Note that this doesn't work if the event_scheduler was set to disabled. In that case the only option is to restart the service.

Insufficient

I would like to add that event_scheduler=1 goes under the [mysqld] section of the config file. – Pongid

It's very important to add the event_scheduler=1 (or =ON) line to the correct configuration file. I added it to my.cnf, restarted MySQL service and it worked for a while and then after 24 hours I couldn't start MySQL service until I removed it. It turns out that if my.cnf has references to mysql.conf.d/mysql.cnf that it is best to add the line into that configuration file and it works properly from there. – Toilsome

Answer 4

If you want your event_scheduler to startup automatically every time mysql server restarts, anywhere under the [mysqld] section of the my.ini or my.cnf file that you find in /etc/mysql you should place

[mysqld]

# turning on event_scheduler  
event_scheduler=ON

restart mysql to check if it is running (in command line terminal!)

sudo service mysql restart

then check your processlist

SHOW PROCESSLIST

you can check if your events are running by checking the last time they ran

SELECT * FROM INFORMATION_SCHEMA.events

Rhizocarpous

Though @Jacopofar's answer helps, your answer provides complete solution for me. Thank you, Up voting your answer. :) – Reeba

Answer 5

Temporal

SET GLOBAL event_scheduler = ON;

Will not work if event_scheduler is explicitly DISABLED, see the method below

Permanent (needs restart)

In your config file (In Ubuntu it's /etc/mysql/mysql.cnf):

[mysqld]
event_scheduler = ON

Notes:

The event_scheduler variable can have this possible states:

• OFF (or 0) (default)
• ON (or 1)
• DISABLED: you cannot use the temporal enabling, you can only change state through the config file and restarting the server

WARNING: Keywords ON / OFF are preferred over their numerical equivalents. And in fact Mysql Workbench doesn't recognize the configuration event_scheduler=1, it shows as OFF in the Options File section. Tested in Ubuntu with Mysql Workbench 8.0.17 and Mysql Server 5.7.27

Although ON and OFF have numeric equivalents, the value displayed for event_scheduler by SELECT or SHOW VARIABLES is always one of OFF, ON, or DISABLED. DISABLED has no numeric equivalent. For this reason, ON and OFF are usually preferred over 1 and 0 when setting this variable.

Source: https://dev.mysql.com/doc/refman/5.7/en/events-configuration.html

Lymphangitis

Answer 6

I just figured out that on MariaDB, after adding an event (in my case, it was the first one), you have to restart the event-scheduler

 SET GLOBAL event_scheduler = OFF;

and then

 SET GLOBAL event_scheduler = ON;

to make it actually bring the scheduler into "waiting for activation"-state.

Tantalous

Answer 7

I would just like to add to this thread. I dumped my database to another server and as a result the definer of my event had no such grant defined for the user. I updated my definer with

ALTER DEFINER='root'@'localhost' EVENT event.name COMMENT '';

Make sure your definer has the correct PRIVILEGES.

Jube

Answer 8

Remember to add in 'Commit', after 'DO BEGIN' or 'DO'. Works for me after that.

Creatural

Answer 9

Try

SET GLOBAL event_scheduler = ON;
DELIMITER $$
CREATE DEFINER=`root`@`db01` EVENT `PRICEALERT_STATUS` 
ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE) 
DO BEGIN
// Your Query
END $$
DELIMITER ;

Vshaped

This won't work because the event has to run first for that event_scheduler to be turned on. – Duky