0

I have created an event in HeideSQL. i just wanna change a value in a table if the value is 2 to 0 after 3 minutes of data insertion. I can execute the query. But i wanna make it automatic. so I created an event. but it is not working automatically. Could someone please help.

Settings as follows:

Name: changeValueEvent Definer: root@localhost Drop event after expiration : checked ( tried unchecked as well) state: enable

Timing:

every: 2 Minutes starts at : 04/01/2019 2:00:00 PM

executing body:

BEGIN
    UPDATE data SET data.VALUE = 0
        WHERE data.TIME <= date_sub(NOW(), interval 3 MINUTE)
          and data.VALUE = 2;

END


    //data is the name if table
    //wanna update VALUE column. 
    //TIME column carries the time of data 
    BEGIN
        UPDATE data SET data.VALUE = 0
            WHERE data.TIME <= date_sub(NOW(), interval 3 MINUTE)
              and data.VALUE = 2;

    END
Rick James
  • 135,179
  • 13
  • 127
  • 222
dragon123
  • 303
  • 1
  • 3
  • 13
  • Why you are not creating sql job and execute that job in regular interval? – Kevin Shah Apr 26 '19 at 07:02
  • but why isn't working. but i can execute manually and it works! – dragon123 Apr 26 '19 at 07:07
  • Check if event scheduler is turned on. `SHOW VARIABLES LIKE '%event_scheduler%'`. If it says `OFF`, then your scheduler isn't running. @KevinShah - what is *"sql job"* that you mentioned? – Mjh Apr 26 '19 at 12:05
  • @Mjh CREATE DEFINER=`root`@`localhost` EVENT `changeValueEvent` ON SCHEDULE EVERY 2 MINUTE STARTS '2019-04-01 02:00:00' ON COMPLETION PRESERVE ENABLE COMMENT 'somecomment' DO BEGIN UPDATE data SET data.VALUE = 0 WHERE data.TIME <= date_sub(NOW(), interval 3 MINUTE) and data.VALUE = 2; END – dragon123 Apr 26 '19 at 12:29
  • @Mjh sql provides option to create a job that we can execute in regular interval. https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-job?view=sql-server-2017 – Kevin Shah Apr 26 '19 at 13:49
  • 1
    @KevinShah why are you linking documentation for Microsoft SQL Server? This question is about MySQL. In MySQL we have an Event Scheduler that runs events on predefined intervals. I have no idea how you plan to use *"sql job"* that's meant for Microsoft SQL server, but I am interested to read about it. Can you please explain? – Mjh Apr 26 '19 at 13:54
  • @KevinShah any chance for an answer to my question? – Mjh Apr 26 '19 at 17:43
  • @Mjh Yes one thing that I miss that MySql tag. :( – Kevin Shah Apr 27 '19 at 12:53
  • still, I haven't got any answer for my question :( – dragon123 Apr 29 '19 at 02:21
  • @dayDreamer I asked you whether your event scheduler is turned on and I gave you the query that checks it. You didn't read what I wrote and replied with the same query you used in question. What exactly did you expect? Could you please **read** first? – Mjh Apr 29 '19 at 12:03
  • @Mjh sorry!. I just checked it. it says OFF. Dunno how to to turn it ON. Tried to change value to ON. it gives warning : Access denied for user 'root@localhost to databse 'information_schema' !! any help? – dragon123 May 09 '19 at 14:11
  • Here's how, [follow the white rabbit :)](https://stackoverflow.com/a/23487522/1133682) – Mjh May 09 '19 at 14:24
  • Do `SET event_scheduler = ON;`, then run `SELECT @@event_scheduler;` Meanwhile, add it to `my.cnf` so it will be turned on upon the next restart. – Rick James May 17 '19 at 21:24

0 Answers0