I’ve always been a silent reader here until now. Now I would like to ask for your expertise and post my ver first question here.
I have to achieve the following task on a weekly basis in my MariaDB via Events:
Every Week on Saturday night at midnight, i want to save the results of a certain view in an excel file (xlsx). The filename should be variable depending on the site_id and the current timestamp.
After saving the results into the file I want to cleanup the DB Tables with another Event, but the previous event must be successfully finished as a condition to start the cleanup event.
e.g.filename: viewname_[site_id]_timestamp.xlsx
overall_weekly _3_01082022.xlsx
This is what I have so far:
EVENT 1(saving results into file):
CREATE EVENT overall_weekly ON SCHEDULE EVERY 1 WEEK STARTS TRUNCATE(CURRENT_TIMESTAMP) + '00:00:00' HOUR_SECONDS ON COMPLETION PRESERVE ENABLE DO DECLARE @path = char DECLARE @view = char DECLARE @site_id = int(3) DECLARE @timestamp = timestamp DECLARE @filetype = char(5) DECLARE @full_filename = char SET @path = "/home/reports/" SET @view = "overall_traffic_weekly" SET @site_id = 3 SET @timestamp = current_timestamp SET @filetype = ".xlsx" SET @full_filename = CONCAT(@path,@view,@site_id,@timestamp,@filetype) SELECT * FROM ( SELECT 'Column_name_1','Column_name2', ... UNION ALL ( SELECT * FROM overall_weekly WHERE site_id = 3 ) ) resulting_set INTO OUTFILE @full_filename FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '/n';
EVENT 2(cleanup):
EVENT 1 must be SUCCESSFULLY finished for event 2 to start. IF event 1 finishes with errors, cleanup must not start.
CREATE EVENT cleanup ON SCHEDULE EVERY 1 WEEK STARTS TRUNCATE(CURRENT_TIMESTAMP) + '03:00:00' HOUR_SECONDS ON COMPLETION PRESERVE ENABLE DO TRUNCATE sourcetable1, TRUNCATE Sourcetable2 ;
Many thanks for reading.
Advertisement
Answer
Problem solved: I used 2 tables instead and matched the 2 records together in a third table