0

I'm trying to create an EVENT to fire up at schedule everyday to update data in table. I have set the event_scheduler ON in MySQL with SET GLOBAL event_scheduler = ON; command, and this is what I see when I run the following:

MariaDB [hr]> SHOW PROCESSLIST;
+-----+-----------------+-----------+------+---------+-------+--------------------------+------------------+----------+
| Id  | User            | Host      | db   | Command | Time  | State                    | Info             | Progress |
+-----+-----------------+-----------+------+---------+-------+--------------------------+------------------+----------+
|   1 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge coordinator | NULL             |    0.000 |
|   2 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker      | NULL             |    0.000 |
|   3 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker      | NULL             |    0.000 |
|   4 | system user     |           | NULL | Daemon  |  NULL | InnoDB purge worker      | NULL             |    0.000 |
|   5 | system user     |           | NULL | Daemon  |  NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 228 | event_scheduler | localhost | NULL | Daemon  | 10486 | Waiting on empty queue   | NULL             |    0.000 |
| 613 | root            | localhost | hr   | Query   |     0 | Init                     | SHOW PROCESSLIST |    0.000 |
+-----+-----------------+-----------+------+---------+-------+--------------------------+------------------+----------+
7 rows in set (0.000 sec)

So event_scheduler is indeed running. This is how I am creating the trigger:

CREATE EVENT update_attendance
  ON SCHEDULE
    EVERY 1 MINUTE
  DO
    UPDATE attendance SET markedAttendance = 1;

When I run this, it says Query OK, 0 rows affected (0.000 sec), but when I run SHOW EVENTS it returns an empty set. Also if I do DROP update_attendance it drops the event. The trigger is never executed even once. I am running XAMPP's MySQL on Windows 10. I do not understand what is going on. What am I doing wrong here?

10
  • i have a feeling you might need/want to add ON COMPLETION PRESERVE as well.. Commented Jul 9, 2019 at 10:18
  • @RaymondNijland I added it but it didn't make a difference. Same things from before. Would it even make a difference when the event never fired up? Commented Jul 9, 2019 at 10:19
  • "Would it make a difference if it never even ran once?" No it would not your question wasn't totally clear that the event never was executed once... Run a SHOW WARNINGS; directly after the CREATE EVENT maybe you get a hint why it is not working.. Commented Jul 9, 2019 at 10:22
  • There are no warnings after I create the trigger. Commented Jul 9, 2019 at 10:26
  • The only reason i can think off why it might fail is that the UPDATE query is "wrong" meaning wrong table name or column name.. MySQL does not check the validation of the UPDATE during CREATE EVENT -> db-fiddle.com/f/hNzXscoFWmibr69BV31fPc/0 notice i can create events with missing "objects" (in this case a missing table) Commented Jul 9, 2019 at 10:27

1 Answer 1

1

So it turned out to be corrupted files of MySQL under XAMPP. When I tried to run MySQL today on my PC, the files were corrupt and it wouldn't work until I reinstalled it. Now the event is working properly.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.