MySql one time event never runs

extended-eventsjobsMySQL

Please have a look at below events

1) create EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO ... 

2) create EVENT Test2 ON SCHEDULE EVERY 20 SECOND STARTS CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO ...

I expect event Test1 to run one time after 20 seconds but it never runs.
Event Test2 is working fine.

Any idea? Thanks.

Ok sorry it is the alter that is not working

At first I did

CREATE EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO

Then shortly after I did

ALTER EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO

I expected event Test1 to run again in another 20 secs but it didn't.

Best Answer

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?

Related Solutions

MySQL, optimize and replication

Your First Question

why does a OPTIMIZE query block the replication catching up ?

The SQL thread in MySQL Replication processes SQL commands from the Relay Logs as a FIFO queue (i.e. first-come, first-server basis). Once the SQL thread see a command, it processes it to completion. Meanwhile, the Seconds_Behind_Master just keeps creeping up.

While the SQL thread on the Slave is running OPTIMIZE TABLE, once you run STOP SLAVE; it SQL thread has complete the running OPTIMIZE TABLE before the SQL thread can terminate. That's why STOP SLAVE; hangs. To verify this, open another mysql session and run SHOW PROCESSLIST;, You should see a line that has OPTIMIZE TABLE with the status Killing slave....

Your Second Question

can I prevent those OPTIMIZE queries ? I didn't put any cron to do that, and as far as I can see, nothing in /etc/cron.{d,daily,hourly,weekly} does this.

Someone or something is running the OPTIMIZE TABLE. You have to hunt it down in all your jobs or PHP apps. Use the mysqlbinlog utility to dumps all the binary logs into a text file. Then, grep the text file for OPTIMIZE TABLE or optimize table. The timestamp of when it was submitted into the binlogs will be a few lines about the command.

When you do find that OPTIMIZE TABLE command in your jobs or PHP code, simply run this:

SET SQL_LOG_BIN=0; OPTIMIZE TABLE tblname; SET SQL_LOG_BIN=1;

This will not register the OPTIMIZE TABLE in the binlogs, thus preventing a Slave from replicating it.

As was just pointed out in the comments below, You can also replace

OPTIMIZE TABLE tblname;

with one of the following:

OPTIMIZE NO_WRITE_TO_BINLOG TABLE tblname;
OPTIMIZE LOCAL TABLE tblname;

to restrict the OPTIMIZE TABLE command from landing in the binlogs and thus prevent replication of it.

MySQL Event Timing – Adding Time to Weekly Events

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;

Best Answer

Related Solutions

MySQL, optimize and replication

MySQL Event Timing – Adding Time to Weekly Events

CAVEAT

Give it a Try !!!

Related Question