MySQL Event Timing – Adding Time to Weekly Events

MySQL

I have this event that runs every Thursday

CREATE EVENT le_drop_database
ON SCHEDULE
  EVERY 1 WEEK
STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY   
 DO BEGIN
  drop database carbon;
    END */$$
DELIMITER ;

I was wondering how i would add some parameter that indicates the time this event should run on Thursday for example run at 11:00 a.m

Best Answer

There are three things you should do with this

Change DROP DATABASE to DROP DATABASE IF EXISTS
Add INTERVAL 11 HOUR to the START (as mentioned by @ypercube)
Add ON COMPLETION PRESERVE to make it a repeatable event so you don't have to create it again.

Here are the changes

DELIMITER $$    
CREATE EVENT le_drop_database
ON SCHEDULE
  EVERY 1 WEEK
STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY + INTERVAL 11 HOUR DO
ON COMPLETION PRESERVE
BEGIN
  DROP DATABASE IF EXISTS carbon;
END $$
DELIMITER ;

I hope this event is not in the carbon database

If you ever need to disable the event, just run

ALTER EVENT le_drop_database DISABLE;

to reenable

ALTER EVENT le_drop_database ENABLE;

CAVEAT

Please make sure you have this in my.cnf

[mysqld]
event_scheduler=1

You can enable this without restarting mysql by running

mysql> SET GLOBAL event_scheduler = 1;

Give it a Try !!!

Related Solutions

MySql one time event never runs

It appears to work for me

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(255) NOT NULL DEFAULT '',
  `subdata` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> CREATE EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO ALTER TABLE t1 DROP `subdata`;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SLEEP(20);
+-----------+
| SLEEP(20) |
+-----------+
|         0 |
+-----------+
1 row in set (20.01 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SHOW EVENTS;
+------+-------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db   | Name  | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+------+-------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| test | Test1 | root@localhost | SYSTEM    | ONE TIME | 2013-06-04 11:46:49 | NULL           | NULL           | NULL   | NULL | DISABLED |          0 | latin1               | latin1_swedish_ci    | utf8_general_ci    |
+------+-------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql>

So my question is what does SHOW EVENTS display before and after the set runtime of the event?

Mysql – How to optimize indexes on MySQL query with various sorts

WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()

Start with the "=" item, then do the range:

INDEX(playable_character, date_published);

"Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you don't need.