MySQL data freshness check and dashboards

Connect MySQL

The connection form asks for host, port (default 3306), user, password and db. That is all it has: there is no SSL toggle and no SSH tunnel field, so the server has to be reachable on a public address. arcodash resolves the host before connecting and refuses anything that lands on a private, loopback or link-local range. It also has no fixed outbound IP list, so a firewall that only admits specific addresses is a problem; the troubleshooting page covers that case. Connecting gives up after 5 seconds, so a wrong host fails fast. Field-by-field help is in Connectors overview.

Create a user that can only read:

CREATE USER 'arcodash_ro'@'%' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT ON shop.* TO 'arcodash_ro'@'%';

Replace % with a narrower host pattern if you can. Grant per schema rather than *.*; a monitoring user has no business reading mysql.user. If you point it at a replica, mind the replication lag covered below.

A freshness check

A check is a scheduled query that returns one row with one number. The monitor reads the first row, pulls the column you name and compares it with your threshold, so aggregate down to a single value. This returns minutes since the newest row landed:

SELECT COALESCE(
         TIMESTAMPDIFF(MINUTE, MAX(loaded_at), UTC_TIMESTAMP()),
         999999
       ) AS minutes_since_last_row
FROM events;

Monitor minutes_since_last_row with > and 120 for “nothing in two hours”. The COALESCE turns an empty table into a huge number; without it the result is NULL and a NULL never crosses a threshold, so a table that was truncated would look healthy. Here loaded_at is a DATETIME that the loader fills in UTC, which is why the comparison uses UTC_TIMESTAMP() and not NOW(). Run the query on a schedule a bit tighter than your threshold. The general pattern is in the data observability monitors guide.

A volume check

Freshness says rows arrived; volume says enough of them arrived. Count the last hour, or the last day for slow tables:

SELECT COUNT(*) AS rows_last_24h
FROM events
WHERE loaded_at >= UTC_TIMESTAMP() - INTERVAL 24 HOUR;

Monitor it with < and a floor that is well under a normal day. If your daily volume swings a lot, a baseline monitor learns the normal size so you don’t pick the floor by hand. Index loaded_at, or this scan reads the whole table every run.

A metric alert

Validity checks fit the same shape. This one returns the percentage of rows with a missing amount:

SELECT 100.0 * SUM(amount IS NULL) / NULLIF(COUNT(*), 0) AS null_pct
FROM events;

MySQL treats a comparison as 1 or 0, so SUM(amount IS NULL) counts the nulls without a CASE. Alert when null_pct is > 1. Duplicates work the same way: SELECT COUNT(*) FROM (SELECT id FROM events GROUP BY id HAVING COUNT(*) > 1) t, alert on > 0.

DATETIME, TIMESTAMP and the session zone

This is where MySQL monitoring goes wrong. A TIMESTAMP is stored as UTC and shown in the session time zone. A DATETIME is stored as written and never converted. On a test server I inserted a row, then changed the session zone:

SELECT created_ts FROM events LIMIT 1;   -- 2026-10-12 11:27:19
SET time_zone = '+09:00';
SELECT created_ts FROM events LIMIT 1;   -- 2026-10-12 20:27:19

Same row, nine hours apart, and a DATETIME column would have stayed put. So decide per column: NOW() compares correctly with a TIMESTAMP because both follow the session zone, UTC_TIMESTAMP() compares with a DATETIME your loader wrote in UTC, and a DATETIME written in local time needs NOW() only if the server zone is that local zone. If you can’t tell which, insert a row and compare.

arcodash returns both types as zone-less ISO strings, so a tile will not tell you which zone the value was in. TIMESTAMP also stops at January 2038, which matters for expiry dates but not for load times.

ONLY_FULL_GROUP_BY

MySQL 8.4 enables ONLY_FULL_GROUP_BY by default. A query that selects a column that isn’t grouped or aggregated is rejected with error 1055, which shows up as a failed check run. I hit it on purpose:

SELECT DATE(loaded_at) AS d, amount FROM events GROUP BY DATE(loaded_at);
-- ERROR 1055 ... incompatible with sql_mode=only_full_group_by
SELECT DATE(loaded_at) AS d, ANY_VALUE(amount) FROM events GROUP BY DATE(loaded_at);

The fix is to aggregate the column or wrap it in ANY_VALUE(). Don’t disable the mode to make a dashboard query pass; the value you’d get from an ungrouped column is arbitrary.

Replication lag and long queries

If the connector points at a read replica, freshness is measured on the replica, so a lagging replica looks like a stale pipeline. Check SHOW REPLICA STATUS on the replica when a freshness alert fires and the primary looks fine. A run is stopped at 300 seconds and keeps at most 10,000 rows (see Limits), so aggregate in SQL instead of pulling rows. arcodash also sets max_execution_time on the session as a backstop on MySQL, which is a best-effort setting.

To generate freshness, row count, null and duplicate checks for a MySQL table, use the MySQL data check generator.

Three dashboard tiles

Orders per day, a line chart:

SELECT DATE(loaded_at) AS day, COUNT(*) AS orders
FROM events
GROUP BY DATE(loaded_at)
ORDER BY day;

Revenue per day, a bar chart. amount is a DECIMAL, and arcodash returns DECIMAL values as strings to avoid rounding, so chart it from a query that casts with CAST(SUM(amount) AS DOUBLE) if the axis comes out as text:

SELECT DATE(loaded_at) AS day, SUM(amount) AS revenue
FROM events
GROUP BY DATE(loaded_at)
ORDER BY day;

Hourly load shape, to see where the pipeline stalls:

SELECT DATE_FORMAT(loaded_at, '%Y-%m-%d %H:00') AS hr, COUNT(*) AS n
FROM events
GROUP BY hr
ORDER BY hr;

Put the freshness query on the same dashboard as a counter tile so the people reading the charts also see how old the data behind them is. arcodash is hosted only and costs from $59 a month with unlimited seats after a 14 day free trial.

MariaDB and TiDB speak the same wire protocol; see the MariaDB and TiDB pages for their differences.

docs

MySQL setup in the docs

Connection fields, an example configuration and caveats.