Schema-change history

The Schema changes tab of every MySQL and MariaDB database lists each table, column, index, view and routine that was created, altered, renamed or dropped, pass by pass. The agent on the database’s active collector reads the schema, compares it with the last one it saw, and sends only the changes. Read Database monitoring first for collectors, grants and privacy modes.

What the tab shows

Part	Shows
Header	The title, with Refresh now beside it, and under it one status line: Last pass (when, and how long it took) · Next pass · Layer · Tracked (for example 412 tables · views tracked (9) · routines tracked (13)) · History starts. A long Tracked is cut short with an ellipsis: hover over it to read it whole.
Timeline	The changes grouped by pass — Between 03:09 BST and 09:09 BST · collected by app-02 — each with its action (Created, Altered, Renamed, Dropped), its kind and its object. Details opens what changed.
Markers	History starts, History re-keyed and History re-baselined: a new baseline, recorded silently, with the reason.
Filters	On the left, Type (All types, or tables, views or routines), Action and Schema. On the right, the tab’s own time range picker, always shown: `1h`, `3h`, `12h`, `1d`, `5d` or Custom, and `5d` when the tab opens. To see older changes, pick a Custom range, which reaches back to when history starts. The filters and the range are in the page’s address, so a link keeps them. 50 entries a page.
Empty	A window with no changes reads No changes were recorded in this window. Before the first pass it reads No schema pass yet., and while a filter is set No change matches the filters in this window. The status line stays above it, so Last pass still says when the last pass ran.

A change’s details read like this, with each key shown as a name in Clear names mode:

+ column #c41a3f… bigint unsigned NOT NULL auto_increment
~ column #9be210… type int → bigint
~ column #9be210… collation utf8mb3_general_ci → utf8mb4_0900_ai_ci
~ column #77d0a1… enum (3 → 4 members), members changed
~ column #2f1c88… default changed
- index #e01b5c…

The Layers available card on the database’s Info tab shows the layer’s state on its L4 Schema-change history row. The Overview’s Monitoring coverage gauge counts it as 1 when it is collected in full, half on InnoDB tables only or while it records tables only, and 0 otherwise: before the first pass, or while history is off, for example.

What is tracked — and what is sent

Object	Tracked
Tables	Created, dropped and renamed; their storage engine (InnoDB or other)
Columns	Added, dropped, moved and altered: type, nullability, extra attributes (such as `auto_increment`), character set and collation
Primary key and indexes	Their columns and prefix lengths, uniqueness and type (full-text, spatial)
Views	Created and dropped: no grant reveals their definitions
Stored routines	Created, altered (the body changed), renamed and dropped, where the privilege allows

What leaves your server:

Names as keys. Each schema, table, column, index, view and routine is a keyed hash under the database’s redaction key, such as #c41a3f…. Names travel only in Clear names mode.
Types in clear. Column types, character sets and collations are sent as they are, so a change reads type int → bigint. An ENUM or SET is sent as enum or set with its number of members, never the members.
Only that something changed for a column’s default, comment or generation expression, a functional index’s expression, a routine’s body, and ENUM and SET members. The agent folds them into a fingerprint keyed with your redaction key and sends a flag such as default changed. Their text never leaves your server, and WatchTower15 cannot test a guess against the fingerprint.
A baseline without names. WatchTower15 keeps the last schema as keys, fingerprints and InnoDB table ids only. A new collector compares with it, so it does not list every table as new.

Not tracked

View definitions, triggers and events.
Who made a change: MySQL does not record it.
Foreign keys, CHECK constraints and table options (row format, the table’s default collation, AUTO_INCREMENT).
Partitions: they fold into their table, so adding or removing one records nothing.
Schemas created or dropped while empty, the system schemas (mysql, sys, performance_schema, information_schema), the temporary tables of an ALTER in progress, and InnoDB’s own internal tables and indexes — including a column named FTS_DOC_ID or an index named FTS_DOC_ID_INDEX that you created yourself.
A routine’s parameters, return type or characteristics: routines are compared by type and body, so a change that keeps the body records nothing.
A column default switched between a literal and an expression with the same text.
Anything undone between two passes: a table created and dropped again in between records nothing.

The layers and their grants

Layer	Grants	Tracks
Full (tier D)	`PROCESS` and tier D	Everything above
InnoDB only (no tier D)	`PROCESS` only	InnoDB tables, their columns with a coarse type (such as `int 4 B unsigned`) and their indexes. No views, routines, non-InnoDB tables, character sets or index prefixes, and a functional index’s expression change is not seen.
Unavailable (no PROCESS)	—	Nothing: no pass runs. The tab shows the `GRANT` to run, and keeps the history recorded before.

Tier D is in the grant script:

Engine	Tier D
MySQL 8.0.20 and later, 8.4, Aurora MySQL 3.x	`REFERENCES, SHOW VIEW, SHOW_ROUTINE`
MySQL 8.0 before 8.0.20	`REFERENCES, SHOW VIEW`: that version has no `SHOW_ROUTINE` and refuses it, so leave it out of the script’s tier D line
MySQL 5.7, Aurora MySQL 2.x	`REFERENCES, SHOW VIEW`
MariaDB	`REFERENCES, SHOW VIEW`

Granting tier D, or revoking it, changes the layer: the next pass records a new baseline silently, and lists no table as created or dropped for it.

Routines

Routines are tracked only where the monitoring user can read their bodies:

MySQL 8.0.20 and later (8.4 and Aurora MySQL 3.x included): with SHOW_ROUTINE, which tier D includes.
MariaDB 11.3 and later: with SHOW CREATE ROUTINE, which the grant script does not include. Grant it yourself, to the same user and host as the script’s other grants, for example:
```
GRANT SHOW CREATE ROUTINE ON *.* TO 'wt15_monitor'@'<collectors-subnet>/<netmask>';
```
Not tracked on MySQL 5.7, MySQL 8.0 before 8.0.20 or MariaDB before 11.3.

Without the privilege the tab says Routines are not tracked and shows the GRANT. Granting it records a new baseline silently: the routines that exist then are not listed as created. Revoking it lists none as dropped.

When it runs

Pass	When
The first pass	10–15 minutes after a server becomes the database’s active collector: at its start, after an agent upgrade, or at a handover. It records a baseline silently (History starts); changes are listed from the next pass.
Every 6 hours	From the first pass. Each pass lists the changes made since the one before.
Refresh now	Within a minute or two. It does not move the 6-hourly schedule.

Short and bounded. Every read is capped at 5 seconds and the whole pass at 30. information_schema.TABLES is never read.
Its own connection. A pass runs on one short connection of its own, beside the collector’s. The monitoring user’s three connections leave room for it.
One at a time. An agent runs one schema pass at a time, across all the databases it collects, within 32 MiB of memory.
No pass while the collector cannot reach the database (Next pass then reads due), or while the monitoring user lacks PROCESS.

Skipped and failed passes

A skipped or failed pass changes nothing on the tab but its own line, The scheduled pass at <time> was skipped: … (The refresh at … for a refresh). The last answered pass’s time, layer and figures stay as they were.

The tab says	Means
skipped: the database was under pressure	32 or more threads were running. Retried every 30 minutes; it runs at the latest 6 hours after the first skip, whatever the load.
skipped: DDL was running	On MySQL 5.7, Aurora MySQL 2.x and MariaDB, reading the catalogue can wait for as long as tables are being created, altered or dropped. Retried every 30 minutes; it runs at the latest 24 hours after the first skip.
skipped: an earlier read was still waiting on the server while DDL ran	On the same engines. Retried every 30 minutes until that read has ended.
skipped: the monitoring user’s connections were all in use	MySQL refused the pass’s connection with error 1203 or 1226. On MySQL 5.7 and Aurora MySQL 2.x, also when a storage read held the short connection the two share for more than 10 seconds. Retried in 30 minutes.
failed: a catalogue read reached the 5 s limit	Tried again at the next 6-hourly pass. Two in a row: tables only.
failed: MySQL error <n>	The server refused a read, with that error number. Only the number is reported, never the message. Tried again at the next 6-hourly pass.
failed: a client-side error on the collector	For example, the agent could not write its state file. Tried again at the next 6-hourly pass.

Refresh now

Refresh now asks the active collector for a pass at once. It needs database.edit.

One per 10 minutes. After a refresh, the tab says when the next one is available.
Refresh requested shows while the collector has not answered. A request it never picks up expires after 15 minutes.
Disabled while the database is paused, while the monitoring user lacks PROCESS, and while the active collector’s agent needs updating.
Audited. Each request is recorded in the audit trail as Schema refresh requested.

The limits

Two limits keep a pass short and small on your server.

Limit	Above it
The table limit: this database’s table limit — 2,048 tables by default, which support can raise to 4,096	Schema history is off. Nothing is recorded, not even tables created or dropped. The history recorded before is kept.
The column limit: 60,000 columns, on every engine	Tables only. Only tables created, dropped and renamed are recorded: no column, index, view or routine changes.

The table limit is the one that also applies to storage. Above it the tab reads:

Above 2,048 tables, this database’s table limit: schema history is off. Contact support to raise the limit.

Counting. Tables are the tables of your own schemas: each partitioned table counts once, and the system schemas and temporary tables are left out. A schema pass counts them itself, so near the limit its count can differ slightly from the Storage & growth tab’s. Columns are the columns of your InnoDB tables, each partitioned table once; generated virtual columns are not counted.
Coming back. History returns at the first pass with the database at or below its table limit. Column, index, view and routine history returns once the column count falls below 90 % of the column limit: 54,000 columns.
The catalogue reads time out. Two passes in a row that cannot read the catalogue within 5 seconds also switch the database to tables only. The full read is tried again after 7 days, or sooner once the column count falls below 90 % of its count when this began. The tab shows the date and the figure.
A new baseline each way. Every move to tables only or off, and back, records a new baseline silently (History re-baselined). Changes made while history was off, or while it covered tables only, are not listed.
To raise the table limit, contact support.

One pass lists at most 1,000 changes. The rest go in a Summary line, counted by kind and action: and 2,314 more changes: 1,120 tables created, …. A created table lists at most 64 columns and 32 indexes, and an altered one at most 64 entries in each list; the rest are counted (… and 12 more columns added).

Renames

Tables are paired by InnoDB’s table id: RENAME TABLE shows as Renamed, with the previous name’s key.
A rename and a rebuild in one statement gives the table a new id, so it shows as dropped and created.
TRUNCATE TABLE, OPTIMIZE TABLE, and dropping and re-creating a table with the same name and structure, give the table a new id but record nothing.
A MyISAM or MEMORY table has no InnoDB id, so its rename shows as dropped and created.
Routines are paired by type and body: one dropped and one created with the same type and body show as renamed.
Views are never paired: a renamed view shows as dropped and created.
Column moves. Columns moved lists the columns whose order relative to the others changed. A column added with AFTER is one column added, not a move of every column after it.

Names and revealing a key

Names follow the database’s privacy mode:

Mode	The Schema changes tab shows
Hashed (default)	Each object as a key, such as `#c41a3f…`.
Clear names	The names, kept in WatchTower15’s database only.
Counts only	The number of changes per pass, by kind and action: 3 tables created, 12 columns added. The per-object history recorded before the switch is kept, and switching back to Hashed or Clear names resumes it with no new baseline.

Switching away from Clear names, to either mode, deletes every stored name in the schema history.
Accept new key gives every object a new key: the pass after it records a new baseline silently (History re-keyed), and the changes before it stay under their old keys.

Revealing a key on your host

Click a key, then Reveal on the host, for the command to run on the database’s active collector, as root:

sudo /var/lib/wt15-server-agent/wt15-server-agent db reveal <database-uuid> <key>

It prints the match — column `shop`.`orders`.`total`, index `shop`.`orders`.`idx_customer`, view `shop`.`v_orders` or routine `shop`.`refresh_totals` (procedure) — or no object with this key. It sends nothing to WatchTower15.

Column, index, view and routine keys are looked up in the schema state the agent keeps on the active collector, with no database read. On another server it answers no schema state on this server: run it on the active collector.
Schema and table keys are found as on the Storage & growth tab: see Revealing a key on your host. That lookup connects to the database. If MySQL refuses it with error 1203 or 1226 (the monitoring user’s connections are all in use, as they can be during a pass), try again in a minute.

The agent keeps the database’s last accepted schema on the active collector, names in clear, in /var/lib/wt15-server-agent/db-<database-uuid>-schema.jsonl.gz: mode 0600, readable only by the agent’s user and root. It never leaves your server. See What the agent keeps on the host.

Pauses, handovers and outages

Pause (Collectors tab) stops schema passes. The active collector deletes its schema state, no incident opens, and Refresh now is disabled. The tab keeps its history under the paused banner.
After Resume, the first pass runs 10–15 minutes later and compares with WatchTower15’s baseline. The changes made during the pause are listed in one pass whose window spans the pause. Without the agent’s own earlier state there is no before and after, so an altered table reads Details unavailable.
A handover works the same way: the new collector’s first pass lists the changes since the last accepted pass, with took over from <server>, and altered objects without details.
No baseline to compare with makes that first pass record a new baseline silently instead (History re-baselined). The changes made in between are not listed.
While WatchTower15 cannot be reached, nothing is lost: the agent’s state moves on only once WatchTower15 accepts a pass, so the next pass reports what a lost one would have.
Needs the agent update. A collector whose agent predates schema history runs no pass. Agents update themselves within minutes of a release; history starts 10–15 minutes after the updated agent restarts.

Retention

The history is kept for as long as the database exists in WatchTower15. Pausing keeps it. Deleting the database deletes its schema history and its baseline.

The alarm

Database schema changed is one of the database’s default alarms, on by default.

It opens a warning when a pass finds a table, column, index, view or routine created, altered, renamed or dropped: one incident per pass with changes.
It resolves itself 15 minutes later, without a resolve notification.
The incident links to the database’s page. Its Schema changes tab lists the pass and each of its changes, in a time range that covers it: the tab opens on the last 3 hours, so pick 5d or a Custom range for an older pass.
Quiet until the first change. A first pass, a new redaction key, a change of layer, an upgrade of the database engine, and a routine privilege granted or revoked each record a new baseline silently and never raise it.

Permissions

database.view reads the tab and the Info tab’s L4 row. database.edit runs Refresh now. See Roles & permissions.