MySQL query not working in phpMyAdmin Event Scheduler

Question

I'm trying to run a query:

SET @Curr_Weekday := DAYOFWEEK(CURRENT_DATE);

INSERT INTO daily_calibrations (`calibration_date`, `machine_ID`)
SELECT *
FROM
(
  SELECT CURRENT_DATE, 37 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 38 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 39 FROM dual 
  UNION ALL
  SELECT CURRENT_DATE, 40 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 41 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 42 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 43 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 44 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 48 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 49 FROM dual
) tmp
WHERE @Curr_Weekday <> 1

It works fine when I run it from phpMyAdmin console, all rows are being added but for some reason when I try to schedule it as event I get an error:

MySQL said: #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 'INSERT INTO daily_calibrations (calibration_date, machine_ID) SELECT * FRO' at line 3

You are probably missing a delimiter definition. Pls share the `create event` statement. — Shadow, Oct 25 '17 at 10:47

score 0 · Accepted Answer · answered Oct 25 '17 at 11:02

Worked it out (thanks for your help @Shadow)

DELIMITER $$

CREATE DEFINER=`root`@`localhost` 
EVENT `add_daily_calib` 
ON SCHEDULE EVERY 1 DAY STARTS '2017-10-25 00:01:00' 
ON COMPLETION PRESERVE ENABLE 
COMMENT 'Add calibrations everyday' 
DO 
BEGIN
SET @Curr_Weekday := DAYOFWEEK(CURRENT_DATE);

INSERT INTO daily_calibrations (`calibration_date`, `machine_ID`)
SELECT *
FROM
(
  SELECT CURRENT_DATE, 37 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 38 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 39 FROM dual 
  UNION ALL
  SELECT CURRENT_DATE, 40 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 41 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 42 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 43 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 44 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 48 FROM dual
  UNION ALL
  SELECT CURRENT_DATE, 49 FROM dual
) tmp
WHERE @Curr_Weekday <> 1;
END $$

I've created event via phpMyAdmin console. When I view event definition it drops DEMILITER $$ and END $$ part but I've tested it and it works fine.

MySQL query not working in phpMyAdmin Event Scheduler

1 Answers1