I am attempting to use a Transaction within a Scheduled Event for MYSQL and it simply will not except the whole transaction, only the Start Transaction; portion.
I have tried wrapping my code, removing semi-colons, setting autocommit = 0 and fenagaling my code a variety of ways to no avail. I simply want to know how to run a transaction that completed a Truncate and Repopulation of the truncated table.
CREATE EVENT IF NOT EXISTS monitored_15min
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
START TRANSACTION;
TRUNCATE dashboard.monitored;
INSERT INTO dashboard.monitored (brand,product,Status,old_Price,Current_Price,Product_Title)
SELECT
amp.brand,
amp.product,
amp.Status,
amp.old_Price,
amp.Current_Price,
Product_Title
FROM results.amp;
COMMIT;
I expect that every minute this query would drop the current values of dashboard.monitored and insert the information from results.amp, but nothing happens as it doesn't except the entire query in the event.
As you have observed the event only consists of the code to the start transaction; (which will run forever on your system until you either drop or disable). The code appears to run once but that's only because it drops through. 'As with stored routines, you can use compound-statement syntax in the DO clause by using the BEGIN and END keywords' (https://dev.mysql.com/doc/refman/8.0/en/create-event.html), in your case
delimiter $$
CREATE EVENT IF NOT EXISTS monitored_15min
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
begin
START TRANSACTION;
TRUNCATE dashboard.monitored;
INSERT INTO dashboard.monitored (brand,product,Status,old_Price,Current_Price,Product_Title)
SELECT
amp.brand,
amp.product,
amp.Status,
amp.old_Price,
amp.Current_Price,
Product_Title
FROM results.amp;
COMMIT;
end $$
delimiter ;
See the manual for more information AND ensure you have enabled the event scheduler.