Start Transaction causes an error when creating an event in phpMyAdmin

Question

For whatever reason, phpMyAdmin won't allow me to create an event with Start transaction. This is what I'm using:

CREATE EVENT `set_history`
ON SCHEDULE
    EVERY 1 DAY STARTS '2018-9-29 00:00:00'
ON COMPLETION PRESERVE
DISABLE ON SLAVE
DO BEGIN

 START TRANSACTION;


INSERT INTO historical_transactions SELECT * FROM transactions WHERE domain IN (SELECT domain FROM domain JOIN accounts ON domain.accessAbility_ID = accounts.accessAbility_ID WHERE accounts.`renew_at` = DATE(NOW()));

DELETE FROM `transactions` WHERE domain IN (SELECT domain FROM domain JOIN accounts ON domain.accessAbility_ID = accounts.accessAbility_ID WHERE accounts.`renew_at` = DATE(NOW()));

UPDATE `accounts` SET `renew_at`= DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH) WHERE `renew_at` = DATE(NOW());

COMMIT;
END

The START TRANSACTION, INSERT, DELETE, UPDATE, and COMMIT all work when run outside of the event. This is the error I keep getting:

#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 8

Hoping you can help. Thank you.

score 0 · Accepted Answer · answered Sep 29 '18 at 18:26

You need to define DELIMITER to something else (eg: $$) other than (;) and redefine it back to (;) at the end.

Basically, PHPMyAdmin parser will interpret ; as an execution trigger, and it will try to trigger the query, instead of taking it in whole as a Create statement for the event. So redefining the delimiter helps in bypassing the execution.

Do the following:

DELIMITER $$
CREATE EVENT `set_history`
ON SCHEDULE
    EVERY 1 DAY STARTS '2018-9-29 00:00:00'
ON COMPLETION PRESERVE
DISABLE ON SLAVE
DO BEGIN

 START TRANSACTION;


INSERT INTO historical_transactions SELECT * FROM transactions WHERE domain IN (SELECT domain FROM domain JOIN accounts ON domain.accessAbility_ID = accounts.accessAbility_ID WHERE accounts.`renew_at` = DATE(NOW()));

DELETE FROM `transactions` WHERE domain IN (SELECT domain FROM domain JOIN accounts ON domain.accessAbility_ID = accounts.accessAbility_ID WHERE accounts.`renew_at` = DATE(NOW()));

UPDATE `accounts` SET `renew_at`= DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH) WHERE `renew_at` = DATE(NOW());

COMMIT;
END $$
DELIMITER ;

Start Transaction causes an error when creating an event in phpMyAdmin

1 Answers1