Some bugs crash loudly.
Others are quieter.
They sit in a tool, wait for the right schema shape, then whisper something absurd like:
ALTER TABLE `KARMA_LOG` ADD COLUMN `COMMENT` 'Vote timestamp';
That is not a migration.
That is a cursed parchment.
This update is about making Mediabot’s schema drift checker smarter, safer, and less likely to hand us nonsense SQL when the database and the reference schema are being compared.
No dragons this time.
Just cartography.
Because before you can safely move through a castle, you need the map to stop inventing doors.
The drift checker was doing a useful job: comparing the live MariaDB schema against install/mediabot.sql.
But it had two blind spots.
A column such as this:
`ts` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
COMMENT 'Vote timestamp',
could be parsed incorrectly.
The script treated the second line as if COMMENT were a column name.
That led to a fake drift:
MISSING COLUMN KARMA_LOG.COMMENT
And worse, the generated migration became invalid SQL:
ALTER TABLE `KARMA_LOG` ADD COLUMN `COMMENT` 'Vote timestamp';
MySQL quite rightly rejected that.
The problem was not the database.
The problem was the parser.
The parser no longer splits table definitions line by line.
It now splits the body of a CREATE TABLE into real top-level SQL items.
That means it respects:
multi-line column definitions
ENUM(...)
COMMENT '...'
quoted strings
backticked identifiers
commas inside definitions
table constraints and indexes
This prevents attributes like COMMENT, DEFAULT, COLLATE, CHARACTER SET, or KEY from being mistaken for missing columns.
A small guardrail was also added: even if a suspicious reserved word somehow appears as a “missing column”, the generator refuses to emit dangerous nonsense and prints a review comment instead.
In other words:
less divination
more parsing
Professor McGonagall would approve.
Probably with a very small nod.
The generated migration header used to start with:
=== GENERATED MIGRATION SQL ===
That is readable to humans, but not valid SQL if copied directly into the MySQL client.
It now emits:
-- === GENERATED MIGRATION SQL ===
That tiny -- matters.
A migration preview should be copyable without immediately stepping on a rake.
The checker was only looking at structure:
tables
columns
optionally types
But Mediabot also has small reference tables that are part of the runtime contract.
The obvious example is:
CHANSET_LIST
This table is not random user data.
It defines known channel settings such as:
Youtube
UrlTitle
AchievementAnnounce
Games
So if install/mediabot.sql contains:
(16, 'Games')
but the live database only has entries up to:
15 AchievementAnnounce
then the bot can behave differently from what the reference schema says.
The old checker did not see that.
Now it does.
CHANSET_LIST rows are now checked tooThe drift checker now parses seeded CHANSET_LIST rows from install/mediabot.sql and compares them with the live database.
If a chanset such as Games is missing, the generated migration now includes an idempotent insert:
INSERT INTO `CHANSET_LIST` (`id_chanset_list`, `chanset`)
SELECT 16, 'Games'
WHERE NOT EXISTS (
SELECT 1 FROM `CHANSET_LIST` WHERE `chanset` = 'Games'
);
That is exactly what we want:
safe to review
safe to rerun
no duplicate rows
no destructive action
The map now tracks not only the rooms, but also the labels on the doors.
This tool remains safe by design.
It still does not generate:
DROP TABLE
DROP COLUMN
destructive cleanup SQL
Extra live columns are reported, not removed.
So legacy leftovers such as:
USER.hostmasks_legacy
CHANNEL.key
CHANNEL_PURGED.key
can remain visible without the tool trying to destroy them.
That is the right behavior.
A drift checker should warn before it swings an axe.
Preferably, it should not swing an axe at all.
The recent social features introduced new runtime assumptions:
+AchievementAnnounce
+Games
achievements
leaderboards
chronos
games gating
If the code expects a chanset but the live database does not contain it, the feature may silently fall back, misbehave, or behave differently between instances.
That is dangerous in a multi-instance bot setup.
The reference schema and live databases need to agree not only on table structures, but also on critical seed data.
This update makes that visible.
Before this fix, the drift checker could produce false or incomplete guidance:
fake missing column: KARMA_LOG.COMMENT
missed missing reference row: CHANSET_LIST.Games
After this fix, the tool should:
stop generating bogus COMMENT columns
detect missing USER_SEEN if the table is absent
detect missing Games in CHANSET_LIST
generate idempotent INSERTs for missing chansets
continue reporting legacy extra columns without dropping them
That is a much better tool.
Not perfect forever, but clearly safer and more useful.
Run:
cd /home/mediabot/mediabot3 || exit 1
perl -c tools/check_schema_drift.pl
tools/check_schema_drift.pl --conf=mbundernet.conf --generate-migration
Expected improvements:
no ALTER TABLE KARMA_LOG ADD COLUMN COMMENT
missing CHANSET_LIST rows are reported when absent
generated migration header is SQL-commented
legacy extra columns remain comments only
If Games is missing from the DB, the fix should generate a safe insert for it.
After applying the missing chanset row, the checker should stop reporting it.
This is not a glamorous update.
No duel. No horoscope. No leaderboard trophy. No dramatic achievement unlock.
But it is important.
A bot can only evolve safely if its tools tell the truth.
This pass makes the schema map less gullible, less dangerous, and more aware of the small reference data that actually shapes Mediabot’s runtime behavior.
That is quiet engineering.
The kind that prevents future chaos.
The kind that lets the next spell land cleanly.
🧭🧪📜
You must be logged in to reply.