CODEWITHSUNDEEP
phpmysqleventsphpmyadmin
AnUser1
AnUser1

Reputation: 79

Mysql event error using php

I'm trying to initiate a MySQL Event using a PHP script. It works using phpMyAdmin (although I get the same error) but not using the script. I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER' at line 1 

DELIMITER |
CREATE EVENT myevent21222
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
DO
  BEGIN
    UPDATE `team` SET `reg` = '0' WHERE `id` = '1';
  END |
  # MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).
DELIMITER ;

Can anyone figure out the problem?

Is there any alternative for changing data in a database after 5 minutes after a user had done something?

Upvotes: 2

Views: 442

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

you must set the *DELIMITER** first:

see for phpmyadmin : Creating functions in phpMyAdmin - Error: access denied you need the super privilege for this operation

DELIMITER |

CREATE EVENT myevent21222
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
DO
  BEGIN
    UPDATE `team` SET `reg` = '0' WHERE `id` = '1';
  END |
  # MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).
DELIMITER ;

The first Post i have seen

Upvotes: -1

Drew
Drew

Reputation: 24959

Create the Event:

drop event if exists `myevent21222`;
DELIMITER |
CREATE EVENT myevent21222
  ON SCHEDULE EVERY 5 MINUTE STARTS '2016-01-01 00:00:00'
  ON COMPLETION PRESERVE
DO
  BEGIN
    UPDATE `team` SET `reg` = '0' WHERE `id` = '1';
  END |
  # MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).
DELIMITER ;

Turn on the event handler:

SET GLOBAL event_scheduler = ON;  -- turn her on and confirm below

Confirm it is on:

show variables where variable_name='event_scheduler';

Check out event info:

show events from so_gibberish2; -- note so_gibberish2 is my database name

-- obviously use your database name above

enter image description here

Look at the manual page for what ON COMPLETION PRESERVE means as well as other things.

Disable or enable it:

ALTER EVENT myevent21222 disable;
ALTER EVENT myevent21222 enable;

Upvotes: 3

Faizan Younus
Faizan Younus

Reputation: 803

Try this.

     DELIMITER |

     CREATE EVENT myevent21222
     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
     DO
        BEGIN
        UPDATE `team` SET `reg` = '0' WHERE `id` = '1';
        END ;
       # MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze).
     DELIMITER |

Upvotes: -1

Related Questions