Set up MariaDB as app database on montage-beta alongside SQLite

[lgelauff]

Currently montage-beta uses SQLite as its app database. Since beta runs on Toolforge it has access to ToolsDB (MariaDB) and the wikireplica — the same stack as production. Setting up MariaDB on beta would allow:

• Testing MariaDB-specific query behaviour before it hits prod
• Validating the production deployment docs end-to-end (including migrate_prod_db.sql)
• Testing labs.py query performance against commonswiki_p with a realistic app database

Approach:

• Set up from scratch (not a copy of prod) to validate reproducibility and the deployment docs
• Keep the existing SQLite setup intact — this is a config switch, not a destructive change
• OAuth credentials and cookie secret can be reused from the existing beta config

Steps (high level):

1. Create a new ToolsDB database for montage-beta
2. Run create_schema.py against it
3. Run migrate_prod_db.sql to apply the Migrate image/oldimage queries to file/filerevision/filetypes #505 migration
4. Update beta config to point db_url at ToolsDB
5. Restart webservice and verify via /meta
6. Import a large category and test query performance

This should ideally be done before #505 merges to prod, so that migrate_prod_db.sql and the query performance of the new schema can be verified against a real MariaDB instance first.

[lgelauff]

Complete (2026-04-19)

All phases done. montage-beta is now running on MariaDB (10.6.22, utf8mb4_unicode_ci) — same stack as production.

What was validated:

• Schema creation from scratch (drop/recreate cycle due to FK constraint ordering with MariaDB).
• migrate_prod_db.sql and revert_prod_db.sql both run cleanly and are reversible.
• Category import via UI: WLM France 2025, 9,844 files, zero gaps.
• Two MariaDB-specific bugs found and fixed (see PR Migrate image/oldimage queries to file/filerevision/filetypes #505 comment for details).

Gaps found in deployment docs (to be addressed in a follow-up PR):

• create_schema.py must be run from the correct git ref to avoid column mismatches.
• MariaDB requires explicit -h flag; no local socket on Toolforge bastion.
• FK constraint ordering requires dropping the full database rather than drop_all() when recreating schema.