I've tried to set up an event that will copy the state of table Daily_Prices into a Price_History table.
The event code is as follows:
CREATE EVENT CopyTodayPrices
ON SCHEDULE EVERY 1 DAY
STARTS '2024-08-13 20:25:00'
ON COMPLETION NOT PRESERVE
ENABLE
DO
BEGIN
-- Ignore if prices are old (if the unique index throws an error)
INSERT IGNORE INTO Prices_History (`Kreditorennr`, `Bar Code`, `Tagespreis`, `Preis vom`)
SELECT `Kreditorennr`, `Bar Code`, `Tagespreis`, `Preis vom`
FROM Daily_Prices;
END;
It doesn't insert any rows into the table Price_History.
I've tried to run the code between BEGIN and END manually as a query in DBeaver after the event was suposed to run and it successfully inserted all the rows.
Event scheduler works:
> SHOW PROCESSLIST;
|Id |User |Host |db |Command|Time |State |Info |
|---|---------------|--------------------|------|-------|-----|---------------------------|------------------------------------------------------------------------------|
|5 |event_scheduler|localhost | |Daemon |1,597|Waiting for next activation| |
And the event also seems to be scheduled:
SHOW EVENTS;
|Db |Name |Definer|Time zone|Type |Execute at|Interval value|Interval field|Starts |Ends|Status |Originator|character_set_client|collation_connection|Database Collation|
|------|---------------|-------|---------|---------|----------|--------------|--------------|-------------------|----|-------|----------|--------------------|--------------------|------------------|
|nocodb|CopyTodayPrices|root@% |SYSTEM |RECURRING| |1 |DAY |2024-08-13 20:25:00| |ENABLED|1 |utf8mb4 |utf8mb4_0900_ai_ci |utf8mb4_0900_ai_ci|
You have Price_History in comment and Prices_History in query. With plural priceS.