Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Perhaps it does run? How do you know it doesn't? –  ypercube Jun 3 '13 at 14:23
    
Sorry, i updated the question, it is the alter that is not working. –  MySql_Newbie Jun 4 '13 at 6:39
add comment

1 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?

share|improve this answer
    
Sorry, i updated the question, it is the alter that is not working. –  MySql_Newbie Jun 4 '13 at 6:39
    
Can you run a SHOW EVENTS; before the first event runs, after the first event runs, and after the ALTER EVENT is run? –  kormoc Jun 7 '13 at 18:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.