Mediabot v3 — install/mediabot.sql sync with production (May 2026)

Context

After adding Mediabot::ChannelBan and the CHANNEL_BAN table via a migration script (install/migrations/20260502_channel_ban.sql), the reference install schema install/mediabot.sql was not updated to match. A fresh install would be missing the table entirely, causing the bot to crash on the first !ban or !kickban.

A diff between the production dump (mediabotv3_9_.sql) and the install schema also revealed a legacy column still present in prod.

Changes

CHANNEL_BAN added to install/mediabot.sql

The table is now present in the main schema with all its columns, indexes and foreign keys, in alphabetical order between CHANNEL and CHANNEL_FLOOD.

A composite index idx_channel_ban_channel_expires on (id_channel, active, expires_at) was added at the same time — it was missing from both the migration and the install schema. This index accelerates expired_bans() queries that filter on all three columns.

Usage instructions updated

The header comment now specifies to use --default-character-set=utf8mb4 on the CLI and SET NAMES utf8mb4 inside the client before SOURCE, to avoid charset ambiguity with shell redirection.

Production cleanup recommended

Two manual operations are recommended on the live instance:

1. Drop the legacy hostmasks column — USER.hostmasks_legacy is a migration artifact from when hostmasks were stored as a CSV in the USER table. The column comment already says “safe to DROP after validation”. USER_HOSTMASK is fully in use and validated.

ALTER TABLE USER DROP COLUMN hostmasks_legacy;

2. Add the missing composite index on CHANNEL_BAN if the instance was created from the migration script rather than the updated install schema:

ALTER TABLE CHANNEL_BAN
  ADD KEY idx_channel_ban_channel_expires (id_channel, active, expires_at);