How to Automate Reports in MariaDB

Why automate reports in MariaDB?

Automating reports removes manual query runs, produces consistent CSVs, and frees developers to focus on feature work. MariaDB’s built-in event scheduler executes SQL blocks on a calendar, so no cron or external ETL is required.

How do I enable the event scheduler?

First set the global variable, then persist it in the my.cnf file.

SET GLOBAL event_scheduler = ON; -- immediate
# my.cnf
[mysqld]
event_scheduler=ON

How do I schedule a daily sales report?

Create an event that runs once per day at 02:00 and exports a CSV to the server’s secure mysql-files directory.

CREATE EVENT report_daily_sales
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
SELECT o.id,
o.order_date,
o.total_amount,
c.name AS customer_name,
c.email
INTO OUTFILE CONCAT('/var/lib/mysql-files/daily_sales_',
DATE_FORMAT(CURDATE(), '%Y_%m_%d'), '.csv')
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE o.order_date = CURDATE();

How can I generate weekly low-stock alerts?

Events can call stored procedures that email or write alert rows.

CREATE PROCEDURE low_stock_alert()
BEGIN
INSERT INTO Alerts(product_id, stock_level, created_at)
SELECT id, stock, NOW()
FROM Products
WHERE stock < 10;
END;

CREATE EVENT weekly_stock_check
ON SCHEDULE EVERY 1 WEEK STARTS '2024-01-05 06:00:00'
DO CALL low_stock_alert();

Best practices for safe automation

Write events as idempotent queries; use CONCAT() with dates to avoid file overwrites. Store output in a directory defined by secure_file_priv. Log each run in an audit table for troubleshooting.

How do I modify or drop an event?

ALTER EVENT report_daily_sales ENABLE; -- or DISABLE
DROP EVENT IF EXISTS report_daily_sales;

What permissions are required?

The user creating the event needs EVENT, FILE, and table-level privileges. Grant them explicitly to avoid runtime failures.

Common mistakes and fixes

Scheduler off: If events don’t fire, check SHOW PROCESSLIST for event_scheduler. Ensure event_scheduler=ON globally and in config.

Wrong OUTFILE path: MariaDB writes only inside secure_file_priv. Move your directory inside that path or change the variable in the server config.

Next steps

Combine events with Galaxy Collections to version and share report SQL, then let Galaxy’s AI copilot optimize queries as data models change.

FAQ

Can I schedule multiple reports in one event?

Yes—wrap each report query in a stored procedure and call them sequentially inside a single event body.

How do I test an event immediately?

Temporarily change ON SCHEDULE AT NOW() or just execute the event body manually to validate output.

Does an event survive server restarts?

Events are stored in the data dictionary and persist, but the scheduler must be ON after each restart.