Commit: c06f226
Version: 3.4dev-20260817_061148
Mediabot Doctor can now inspect the database and migration state without changing either of them.
That sounds simple. It was not.
The goal of this round was deliberately strict:
Diagnose MariaDB, schema drift and migration effects without applying migrations, modifying schema, changing data, restarting services, or pretending to know more than the database can actually prove.
The result is now committed and pushed.
Doctor 1.1 adds a real DATABASE domain.
It connects through Mediabot’s isolated database path rather than using the normal DB constructor, because a diagnostic tool must not terminate the whole process just because the database connection fails.
Once connected, Doctor switches the session to:
SET SESSION TRANSACTION READ ONLY
and only then performs its inspection.
The current checks include:
database connectivity
driver/version visibility
session charset/collation
MB646 achievement persistence availability
schema/reference drift
On the development instance:
DATABASE
[ ok ] connected read-only to database 'mediabotv3' via DBD::MariaDB 1.24
[ ok ] session charset utf8mb4 / utf8mb4_unicode_ci
[ ok ] MB646 achievement persistence tables present
No database write is required to obtain those facts.
This round intentionally does not:
ALTER TABLE
CREATE INDEX
DROP INDEX
INSERT
UPDATE
DELETE
apply migrations
repair schema
restart Mediabot
reload systemd
Doctor reports.
The operator decides what to do next.
That separation matters even more for database diagnostics than it does for filesystem checks: a diagnostic command must never become an accidental migration engine.
Doctor does not implement its own schema comparator.
It delegates schema/reference checks to the existing authoritative tool:
tools/check_schema_drift.pl
That immediately exposed an interesting problem: the checker itself had accumulated normalization assumptions that were too strict for MariaDB metadata.
The first Doctor run reported:
147 schema drift issues
That looked alarming.
It was also misleading.
Instead of “fixing” the database, we investigated the checker.
Two classes of false positives were found.
MariaDB can expose historical display widths such as:
BIGINT(20)
INT(11)
while the reference schema expresses them as:
BIGINT
INT
Those are not automatically different schema intentions.
The checker now normalizes these representations before comparison.
COLUMN_DEFAULT representationMariaDB can expose defaults already in SQL-like form.
The old checker effectively compared values such as:
expected: DEFAULT 'irc'
live: DEFAULT '''irc'''
or treated an implicit nullable column as if:
no explicit DEFAULT
and:
DEFAULT NULL
were necessarily different.
That created a large amount of noise.
After correcting those normalizations:
147 issues
↓
81 issues
↓
28 issues
with zero MariaDB modifications.
That is a far more useful diagnostic signal.
The checker does not normalize everything away.
The remaining list contains differences that deserve to remain visible, including examples such as:
ACTIONS_QUEUE.id_actions_queue
expected bigint unsigned auto_increment
live bigint without auto_increment
ACTIONS_QUEUE.PRIMARY
expected
absent live
CONSOLE
reference utf8mb4
live utf8mb3
KARMA / REMINDERS
reference bigint unsigned
live int
TIMERS.duration
reference int
live bigint
USER.creation_date
datetime vs timestamp
There are also historical index differences such as:
userhost(191)
versus a full-column index.
Doctor does not decide that these are automatically dangerous, but it does not erase them either.
The first implementation treated any schema drift reported by the canonical checker as:
FAIL
→ UNSAFE
That was too coarse.
Doctor now distinguishes missing required structure from non-missing schema/reference differences.
Conceptually:
required table/column/data missing
→ FAIL / UNSAFE
type/index/reference differences
→ WARN / DEGRADED
On the real development instance, the final result is therefore:
[ WARN ] schema/reference drift detected
rather than claiming the database is unusable.
Mediabot currently has no authoritative migration ledger that can prove:
migration X was executed at timestamp Y
Doctor therefore refuses to invent one.
Instead it scans the current migration files and checks observable effects.
The vocabulary is explicit:
observable_effect_present
observable_effect_missing
indeterminate
This round discovered 18 migration files and two with incomplete observable effects.
20260707_factoid.sqlThe FACTOID table exists.
Its expected foreign-key effects do not:
FACTOID.fk_factoid_channel
FACTOID.fk_factoid_created_by
mediabot_fun_commands_migration_20260512.sqlThe relevant tables exist, but two expected constraints are absent:
REMINDERS.fk_reminders_channel
KARMA.fk_karma_channel
Doctor reports this carefully:
2 migration file(s) have observable effects missing
and adds the important qualification:
this does NOT prove the migration file was never executed
That wording is deliberate.
A table may have existed before a later migration, or a migration using IF NOT EXISTS may have encountered an older structure. Observable state is not the same thing as historical execution evidence.
After the database and migration work, a full Doctor run on the development instance produced:
35 finding(s)
24 ok
8 info
0 unknown
3 warn
0 fail
Result: DEGRADED
RC=0
The three warnings were understandable and useful:
DATABASE
28 remaining schema/reference differences
UPDATER
working tree dirty during development
MIGRATIONS
2 migration files with some observable effects missing
No false UNSAFE.
No hidden repair.
No unknown state silently turned into OK.
Two new test files protect this round:
t/cases/830_mb649_doctor_database_migrations.t
t/cases/831_mb649_schema_drift_normalization.t
Existing Doctor tests were also updated where the previous expectations became obsolete now that database and migration domains are implemented.
The normalization tests explicitly cover cases such as:
MariaDB integer display widths
implicit NULL defaults
quoted string defaults
empty string defaults
case-sensitive string default differences
The complete Mediabot test suite passed:
PASSED : 13206/13206
RC=0
Mediabot Doctor now has working domains for:
Runtime ✓
systemd ✓
Config ✓
Database ✓
Updater ✓
Filesystem ✓
Migrations ✓
The important part is not just that every heading now exists.
Each domain has been exercised against real deployment behaviour, and several incorrect assumptions were removed only after the real installations contradicted them.
The most interesting result was not “Doctor can connect to MariaDB”.
It was this:
147 problems
did not mean:
147 database problems
It meant:
some real historical drift
+
some comparison noise
+
some metadata representation differences
A diagnostic tool becomes dangerous when it confuses those categories.
So instead of altering MariaDB until the warnings disappeared, we improved the measurement first.
That is exactly how Mediabot Doctor is meant to work.
c06f226
🧙 Teach Mediabot Doctor to Read the Database Without Casting a Spell
Version:
3.4dev-20260817_061148
Committed scope:
CHANGELOG.md
VERSION
tools/mediabot_doctor.pl
tools/check_schema_drift.pl
t/cases/828_mb647_doctor_core_local_probes.t
t/cases/830_mb649_doctor_database_migrations.t
t/cases/831_mb649_schema_drift_normalization.t
The owl has reached GitHub.
And MariaDB was left exactly where we found it.
You must be logged in to reply.