Mysql event not working in the server. Same code worked in local

Question

DROP EVENT `game_insert`; CREATE DEFINER=`numberBetting_user`@`%` EVENT `game_insert` ON SCHEDULE EVERY 5 MINUTE STARTS '2021-08-18 17:25:29' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN UPDATE `game` set `status` =0 where `status`=1; INSERT INTO `game`(`startTime`, `endTime`,`status`,`betting`) VALUES (now(),NOW() + INTERVAL 5 minute,1,1); END

This event would insert a record every 5 minutes. It was working fine in local(xampp) but in the live server, it is not inserting data. Though event scheduler is on.

Everything looks fine but the event is not doing its job.

score 1 · Answer 1 · answered Aug 18 '21 at 12:31

1

Add DELIMITER like below

DELIMITER $$
DROP EVENT `game_insert`; 
CREATE DEFINER=`numberBetting_user`@`%` EVENT `game_insert` ON 
SCHEDULE EVERY 5 MINUTE STARTS '2021-08-18 17:25:29' ON COMPLETION NOT PRESERVE ENABLE DO 
BEGIN 
UPDATE `game` set `status` =0 where `status`=1;
 INSERT INTO `game`(`startTime`, `endTime`,`status`,`betting`) VALUES (now(),NOW() + INTERVAL 5 minute,1,1); 
END$$
DELIMITER ;

Only when you are in mysql workbench, you don't need thejm because they are added automatically

answered Aug 18 '21 at 12:31

nbk

45,398
8
30
47

Now I am getting the following error message "Unrecognized statement type. (near "END" at position 352)" – Selsons Software Aug 18 '21 at 12:48
have you copied the text or only added text to your code? my code throws no error – nbk Aug 18 '21 at 13:03
"I removed the DROP EVENT `game_insert`; " and created the event. But still not working – Selsons Software Aug 18 '21 at 13:07
it runs on m< side and on dbfiddle in mysql 5.7 and mysql 8.0 see https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=742eec780a1410388afeabbb94231586 – nbk Aug 18 '21 at 13:10
dbfiddle doens't need any DELIMTER it adds it automatically too – nbk Aug 18 '21 at 13:10
I am using mariaDB 10.3 and in dbfiddle my code runs @nbk – Selsons Software Aug 18 '21 at 13:26
there maybe a character or something please colpy the code from the answer diretly in phpmyadmin or whatever you are using – nbk Aug 18 '21 at 13:28
Tried it. The event got created but it is not doing its job – Selsons Software Aug 19 '21 at 05:21
i can' see why not, have you updated the column manually and see if it is changed back and check with https://dev.mysql.com/doc/refman/5.7/en/show-events.html – nbk Aug 19 '21 at 07:35

score 0 · Answer 2 · answered Aug 19 '21 at 08:12

0

SOLVED the issue. The problem was with my insert query that I was missing a value for a not null column. After I added the value in the insert query the event works fine. Thank you

answered Aug 19 '21 at 08:12

Selsons Software

23
6

Mysql event not working in the server. Same code worked in local

2 Answers2