Error in create event query

Question

I am writing this query:

CREATE EVENT `event_maid_availability` ON SCHEDULE EVERY 5 SECOND STARTS 
'2015-08-12 10:16:47' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
SET @t1:=1;
INSERT INTO `maid_slots_availabilty`(`maid_slot_id`, `date`, `status`)
SELECT maids_slots.id,
              DATE_ADD( CURDATE(), INTERVAL @t := @t + 1 DAY),
              maids_slots.status
FROM
  (SELECT *
    FROM maids_slots
    WHERE status = '1'
      AND end_date IS NULL) AS `maids_slots`
INNER JOIN
  (SELECT day_slot_id
    FROM day_slots
    WHERE day_id = DAYOFWEEK( curdate())-1) AS day_slots
WHERE day_slots.day_slot_id = maids_slots.day_slot_id;
  ALTER TABLE maid_slots_availabilty AUTO_INCREMENT = 1; END;

And I am getting this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

I am spend two days on this but I am not able to find where is the problem.

use delimiter something as `delimiter //` at the start and at the end `end;//` — Abhik Chakraborty, Aug 12 '15 at 06:48

score 0 · Answer 1 · answered Aug 12 '15 at 06:40

0

Have you checked removing the ` marks and then trying again? Sometimes for some reason it's my problem!!

for example because of ` mark usage for table name I was getting an error message

answered Aug 12 '15 at 06:40

Sina KH

563
4
16

" ` " can solve problems in MySQL, and not to add them usually. – Tata Aug 12 '15 at 06:49
I have tried both with " ` " and without " ` " but same error persist – Ashvini Kumar Aug 12 '15 at 06:52
"`" backticks are used to escape column and table names when you use mysql reserved words for name or when the table/column name contains whitespaces. See [this](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) for further details. This is not OP situation. – Lelio Faieta Aug 12 '15 at 06:53

Tata · Answer 2 · 2015-08-12T07:07:37.247

0

Then you are running create event or procedure statements via the MySQL console, you should change the delimiter first. Otherwise - it thinks that you running a list of commands and then you get this error.

So to avoid this problem you should set a different delimiter. so basically your code should look like this:

delimiter ||

CREATE EVENT `event_maid_availability` ON SCHEDULE EVERY 5 SECOND 
STARTS 
'2015-08-12 10:16:47' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
SET @t1:=1;
INSERT INTO `maid_slots_availabilty`(`maid_slot_id`, `date`, `status`)
SELECT maids_slots.id,
              DATE_ADD(CURDATE(), INTERVAL @t := @t + 1 DAY),
              maids_slots.status
FROM
  (SELECT *
    FROM maids_slots
    WHERE status = '1'
      AND end_date IS NULL) AS `maids_slots`
INNER JOIN
  (SELECT day_slot_id
    FROM day_slots
    WHERE day_id = DAYOFWEEK(curdate())-1) AS day_slots
WHERE day_slots.day_slot_id = maids_slots.day_slot_id;
  ALTER TABLE maid_slots_availabilty AUTO_INCREMENT = 1; 
END; ||

delimiter ;

Here I set the delimiter to || but you can use any other character that is not previously used in your code.

UPDATE:

You have another option when you work from console directly.

You can run edit command which will open you the default editor. Write your event there. Save the document Then prompt will return to -> sign of MySQL - add ; (which is the default delimiter)

Here is a reference to a MySQL commands page https://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html

UPDATE 2:

If you are using phpAdmin - check out this video. phpMyAdmin tutorial at Debconf13, part 4 : triggers/routines/events and server status monitor

edited Aug 12 '15 at 07:07

answered Aug 12 '15 at 06:47

Tata

802
9
19

i got this -#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter' at line 1 – Ashvini Kumar Aug 12 '15 at 06:50
what is the version of MySQL you are using? (and check the other option I have added in the update – Tata Aug 12 '15 at 06:54
5.6.24 is the version I am using – Ashvini Kumar Aug 12 '15 at 06:56
write the exact command that you are typing. here is exactly how it works `mysql> delimiter || mysql>` – Tata Aug 12 '15 at 06:58
I have pasted the query you suggested in the phpmyadmin query box and press 'go' and then it showed me the error – Ashvini Kumar Aug 12 '15 at 06:59
this works in the normal mysql command line. phpadmin - can do a lot of stuff that I'm not aware of. you should write that you are using phpadmin next time. You can open a console and it will work with no problem. – Tata Aug 12 '15 at 07:03
found more people complaining about the same here (http://stackoverflow.com/questions/13994159/phpmyadminhow-to-create-an-event-do-2-actions) for example. and no solution. I personally do not use phpadmin, so can not test it . – Tata Aug 12 '15 at 07:04
Thanks for your suggestion but I have already read MySQL documentation and using similar query as they are using in their example in the docs and still i am getting error. – Ashvini Kumar Aug 12 '15 at 07:21
MySQL documentation is not relevant for phpAdmin for 100%. You should use the console :) and will have no problems – Tata Aug 12 '15 at 07:29

Error in create event query

2 Answers2