Database migrations

Database migrations

Fresh installation and upgrade are intentionally different:

• fresh install: ./configure loads the current install/mediabot.sql reference schema;
• existing database: back up, inspect drift, apply only required official migrations in order, then require zero strict drift.

The authoritative order is install/migrations/README.md in the exact source being deployed. This page explains the operator workflow; it does not replace that file.

1. Stop writes and identify the target

Record the instance, config, database and source revision before changing anything:

cd /home/mediabot/mediabot_v3 || exit 1

git rev-parse --short HEAD
cat VERSION
sudo systemctl status mediabot@dev.service --no-pager -l
perl install/configure_config.pl \
  --config mediabot.conf \
  --get mysql.MAIN_PROG_DDBNAME

Use the actual instance/config. Stop the selected bot before applying schema changes:

sudo systemctl stop mediabot@dev.service

2. Create a private backup

Use a privileged local database path without exposing a password in process arguments. Example with the root socket:

sudo install -d -o root -g root -m 700 /var/backups/mediabot
sudo mariadb-dump \
  --protocol=socket \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  DATABASE_NAME | sudo tee /var/backups/mediabot/pre-upgrade.sql >/dev/null
sudo chmod 600 /var/backups/mediabot/pre-upgrade.sql

Replace DATABASE_NAME only after reading it safely from the selected config. Test the backup on a disposable database for high-risk/long-lived upgrades.

3. Generate and review drift

perl tools/check_schema_drift.pl \
  --conf=mediabot.conf \
  --generate-migration \
  --types \
  --indexes

The generated plan is evidence, not an instruction to paste blindly. Compare it with the official migration order and the version you are upgrading from.

4. Apply official migrations in order

Current order in the supplied 3.6dev tree:

20260502_channel_ban.sql
20260502_user_seen.sql
mediabot_fun_commands_migration_20260512.sql
20260515_claude_chanset.sql
20260521_trivia_scores_note.sql
20260603_karma_log.sql
20260604_achievement_announce_chanset.sql
20260604_chansets_mb115_mb118.sql
20260706_channel_log_channel_ts.sql
20260707_channel_report_chanset.sql
20260707_didyoumean_chanset.sql
20260707_factoid.sql
20260707_factoids_chanset.sql
20260708_onthisday_chanset.sql
20260708_onthisday_digest_chanset.sql
20260710_quotes_hits.sql
20260724_lang_chansets.sql
20260816_achievements_db.sql
20260822_rss_feeds.sql
20260823_legacy_schema_reconciliation.sql
20260825_wit_chanset.sql
20260827_spark_chanset.sql
20260827_vdm_chanset.sql
20260827_danstonchat_chanset.sql
20260828_spark_action_chanset.sql
20260902_hailo_policy_chansets.sql
20260902_gemini_chanset.sql
20260903_fullop_chanset.sql
20260904_mbweb_sessions.sql
20260905_quotes_512_contract.sql
20260909_quip_chanset.sql
20260911_radio_chanset.sql

Apply only files missing from the source version/schema you are upgrading. Released migrations are immutable history; never edit one to make a local database pass.

Interactive root example:

./install/db_migrate.sh \
  DATABASE_NAME \
  install/migrations/20260911_radio_chanset.sql \
  root

For automation, use a private non-symlink MariaDB option file with no group/other permissions:

sudo ./install/db_migrate.sh \
  --defaults-extra-file /root/.mediabot-mysql.cnf \
  DATABASE_NAME \
  install/migrations/20260911_radio_chanset.sql

The helper accepts only SQL files under install/migrations/ and imports them with explicit utf8mb4 settings.

5. Require clean strict drift

perl tools/check_schema_drift.pl \
  --conf=mediabot.conf \
  --strict \
  --types \
  --indexes

Do not restart the bot on a non-zero result.

6. Validate and restart

perl -I. -c mediabot.pl
perl t/test_commands.pl --fast --progress

sudo systemctl start mediabot@dev.service
sudo systemctl status mediabot@dev.service --no-pager -l
tail -n 120 /home/mediabot/mediabot_v3/mediabot.log

Then confirm !version, !help and affected features on IRC.

Special migrations

• 20260823_legacy_schema_reconciliation.sql is for long-lived databases predating the canonical schema history. Rehearse it on a clone.
• 20260904_mbweb_sessions.sql creates MBWEB_SESSION; it creates no mbweb account or grants.
• 20260905_quotes_512_contract.sql widens legacy quote text only after fail-closed preconditions.
• Chanset migrations register names but do not enable channels.

Rollback

Rollback means restoring the private pre-upgrade database dump and the previous application tree as one matched pair. A source rollback without a database decision can be unsafe.

Document the exact dump, source revision, config backup and service instance before starting. See Release and upgrade notes.