Mysql event error using php

Question

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?

Don't add the delimiter part to your code. phpMyadmin does that for you automatically — juergen d
– juergen d, Commented Jun 18, 2016 at 21:02
Oh, now it should work, I think. But I get the following message: "Errormessage: This command is not supported in the prepared statement protocol yet". Well, I'll try it using mysqli_query ... — AnUser1
– AnUser1, Commented Jun 18, 2016 at 21:06
You have 3 questions, none have accepted check marks, and no upvotes on those that help you? — Drew
– Drew, Commented Aug 4, 2016 at 19:34

Drew · Accepted Answer · 2016-06-18 21:17:57Z

3

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

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;

edited Jun 18, 2016 at 21:17

answered Jun 18, 2016 at 21:09

Drew

25k10 gold badges47 silver badges81 bronze badges

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

2 Comments

AnUser1 Over a year ago

Already works by deleting the Delimiter part. Nevertheless, thank you very much!

Drew Over a year ago

glad you got it working. Don't forget it is still running :p

Faizan Younus · Accepted Answer · 2016-06-18 21:09:57Z

-1

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 |

answered Jun 18, 2016 at 21:09

Faizan Younus

8031 gold badge9 silver badges14 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:19:23Z

-1

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 ;

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Jun 18, 2016 at 21:00

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

6 Comments

Bernd Buffen Over a year ago

i have not seen DELIMITER | before in the question, and it works then

juergen d Over a year ago

But not in phpMyAdmin

Bernd Buffen Over a year ago

and OP has change his question

juergen d Over a year ago

Actually not, he only added the rest of the error message

Bernd Buffen Over a year ago

here is the answer stackoverflow.com/questions/16801550/…

|

Collectives™ on Stack Overflow

Mysql event error using php

3 Answers 3

2 Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related