For years, Mediabot achievements lived in var/achievements.json.
That worked while the bot stayed in the same directory. It was much less reassuring once deployments started rotating release trees: an update could leave the last achievement state behind in an archived directory, and a restart could make long-earned progress appear to vanish.
MB646 changes the persistence model completely.
Achievements are now durably stored in MariaDB. The JSON file becomes a legacy import/fallback format rather than the source of truth.
This is not a cosmetic change. Existing installations must apply the new database migration before relying on DB-backed persistence. The procedure below is deliberately detailed because the safest upgrade is an ordered one.
MB646 introduces four tables:
ACHIEVEMENT_PROFILE
ACHIEVEMENT_IDENTITY
ACHIEVEMENT_UNLOCK
ACHIEVEMENT_PROGRESS
The model separates the durable achievement owner from the IRC aliases used to recognise that person:
ACHIEVEMENT_PROFILE
└─ ACHIEVEMENT_IDENTITY
├─ ACHIEVEMENT_UNLOCK
└─ ACHIEVEMENT_PROGRESS
A profile belongs to one channel.
An identity records an observed IRC tuple such as:
nick + user@host + channel
Unlocks and progress then belong to the durable profile rather than directly to a transient nickname.
All four tables use:
InnoDB
utf8mb4
utf8mb4_unicode_ci
Fresh installations already receive these tables from install/mediabot.sql.
Existing installations must apply:
install/migrations/20260816_achievements_db.sql
Identity matching is intentionally conservative.
Mediabot would rather create two profiles that can later be reconciled than merge two unrelated IRC users and give one person’s achievements to somebody else.
The resolver follows this order:
nick + user@host + channel;USER.id_user on the same channel — this is authoritative;user@host on the same channel, allowing a nick change;Historical message-derived achievement calculations can use the known aliases attached to the durable profile, so changing nick no longer means starting long-running progress from zero.
The important rule is simple:
Do not perform the first MB646 DB-backed startup until the migration has been applied and verified.
The code contains a legacy JSON fallback if the tables are missing, so an accidental early start is designed not to destroy achievements. Nevertheless, the controlled procedure below is the recommended production path.
Replace the examples with the real service instance, application root and database for your installation.
For example:
SERVICE = mediabot@myinstance
ROOT = /home/mediabot/mediabot_v3
DB = mediabot
Use systemd when the instance is systemd-managed:
systemctl stop mediabot@myinstance
systemctl status mediabot@myinstance --no-pager
The expected state is:
Active: inactive (dead)
A clean shutdown should also log:
Achievements: final save() before exit
Before touching files or the database, make sure the old process is actually gone.
For a normal installation:
pgrep -a -f '/home/mediabot/mediabot_v3/mediabot\.pl' || true
Do not continue if the instance is still running.
If var/achievements.json exists, copy the post-shutdown file somewhere outside the release tree.
Example:
cp -av \
/home/mediabot/mediabot_v3/var/achievements.json \
/home/mediabot/achievements_pre_mb646.json
Verify the copy:
sha256sum \
/home/mediabot/mediabot_v3/var/achievements.json \
/home/mediabot/achievements_pre_mb646.json
The two SHA256 values must match.
Keep this backup until MB646 has been started, verified, restarted and confirmed to reload its state from MariaDB.
If you ever roll back to code older than MB646, this backup is especially important because the successful MB646 importer may rename the live JSON to:
achievements.json.migrated-<timestamp>
Take the normal database backup used for your installation before applying a schema migration.
A standard MariaDB example is:
mariadb-dump \
--single-transaction \
--routines \
--triggers \
mediabot \
> /safe/backup/path/mediabot_pre_mb646.sql
Use the appropriate authentication and backup location for your environment.
Do not store database dumps containing credentials or private data in the Git repository.
Update the application tree using your normal deployment mechanism.
If your deployment script normally restarts Mediabot automatically, disable that restart for this migration or stop the service again before proceeding.
The migration file must now exist under the deployed tree:
ls -l \
/home/mediabot/mediabot_v3/install/migrations/20260816_achievements_db.sql
Some deployments build a new tree and rotate the current tree to an archive such as:
mediabot_v3.166
mediabot_v3.old.20260816_170238
MB646 understands both archive layouts:
<root>.NNN
<root>.old.YYYYMMDD_HHMMSS
Archive discovery is scoped to the exact current application-root basename.
That matters when two independent Mediabot installations live under the same Unix home. A bot running from mediabot3 will inspect only mediabot3.* / mediabot3.old.* archives; it will not import achievements from a sibling mediabot_v3 family.
Archived JSON files are treated as historical evidence and are not modified.
Open the MariaDB client:
mariadb
Select the target database:
USE mediabot;
Set the session explicitly:
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;
SET collation_connection = utf8mb4_unicode_ci;
Verify it:
SELECT
@@character_set_client,
@@character_set_connection,
@@character_set_results,
@@collation_connection;
Expected values:
utf8mb4
utf8mb4
utf8mb4
utf8mb4_unicode_ci
Still inside MariaDB:
SOURCE /home/mediabot/mediabot_v3/install/migrations/20260816_achievements_db.sql;
The migration is structural and idempotent: the tables are created with CREATE TABLE IF NOT EXISTS.
Now verify them:
SHOW TABLES LIKE 'ACHIEVEMENT%';
Expected:
ACHIEVEMENT_IDENTITY
ACHIEVEMENT_PROFILE
ACHIEVEMENT_PROGRESS
ACHIEVEMENT_UNLOCK
Verify engine and collation:
SELECT
TABLE_NAME,
ENGINE,
TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME LIKE 'ACHIEVEMENT%'
ORDER BY TABLE_NAME;
Every row should report:
InnoDB
utf8mb4_unicode_ci
Before the first MB646 startup, run:
SELECT 'ACHIEVEMENT_PROFILE' AS table_name, COUNT(*) AS rows_count
FROM ACHIEVEMENT_PROFILE
UNION ALL
SELECT 'ACHIEVEMENT_IDENTITY', COUNT(*)
FROM ACHIEVEMENT_IDENTITY
UNION ALL
SELECT 'ACHIEVEMENT_UNLOCK', COUNT(*)
FROM ACHIEVEMENT_UNLOCK
UNION ALL
SELECT 'ACHIEVEMENT_PROGRESS', COUNT(*)
FROM ACHIEVEMENT_PROGRESS;
For a database that has never run MB646, the expected result is:
ACHIEVEMENT_PROFILE 0
ACHIEVEMENT_IDENTITY 0
ACHIEVEMENT_UNLOCK 0
ACHIEVEMENT_PROGRESS 0
Do not blindly continue as if this were a first migration.
The database may already have been imported by an earlier MB646 startup.
Inspect the existing data and logs first. Re-running the migration SQL itself is safe, but the operational state must be understood before treating the installation as a fresh import.
Start the instance:
systemctl start mediabot@myinstance
Verify that it stays running:
systemctl is-active mediabot@myinstance
Expected:
active
Then inspect the achievement startup messages:
journalctl -u mediabot@myinstance \
-n 150 \
--no-pager \
| grep 'Achievements:'
For a first DB-backed startup with legacy state, the important sequence is:
Achievements: loaded 0 DB profile(s), 0 progress counter(s)
Achievements: processed legacy state from N source(s) into DB (...)
Achievements: loaded X DB profile(s), Y progress counter(s)
Achievements: database persistence enabled
Achievements: system initialized
If a live var/achievements.json existed in the new tree, a successful import should also report:
Achievements: archived live legacy JSON as var/achievements.json.migrated-<timestamp>
If the deployment used a directory swap and the live JSON moved into the newly-created <root>.old.<timestamp> directory, that archived file can be imported directly. It does not need to be copied back into the live tree first.
The import message deliberately says records processed.
For example:
Achievements: processed legacy state from 36 source(s) into DB
(154 profile records processed, 175 unlock records processed,
92 progress records processed)
Those values describe legacy records handled by the importer.
They are not guaranteed to equal the final number of rows in MariaDB.
Why?
Because historical snapshots can contain repeated or case-equivalent identities, and MB646 canonicalisation can map several legacy records to the same durable profile or unlock.
The authoritative post-import state is the subsequent:
Achievements: loaded X DB profile(s), Y progress counter(s)
plus the SQL counts below.
After the first successful startup:
mariadb
Then:
USE mediabot;
SELECT 'ACHIEVEMENT_PROFILE' AS table_name, COUNT(*) AS rows_count
FROM ACHIEVEMENT_PROFILE
UNION ALL
SELECT 'ACHIEVEMENT_IDENTITY', COUNT(*)
FROM ACHIEVEMENT_IDENTITY
UNION ALL
SELECT 'ACHIEVEMENT_UNLOCK', COUNT(*)
FROM ACHIEVEMENT_UNLOCK
UNION ALL
SELECT 'ACHIEVEMENT_PROGRESS', COUNT(*)
FROM ACHIEVEMENT_PROGRESS;
The values should now be non-zero on an installation that had achievement history.
Do not compare the final profile count mechanically with “profile records processed”; canonicalisation may legitimately reduce it.
The migration is not considered fully validated until Mediabot has demonstrated that it can restart using MariaDB alone.
Restart:
systemctl restart mediabot@myinstance
Check:
systemctl is-active mediabot@myinstance
Then inspect the new logs:
journalctl -u mediabot@myinstance \
-n 100 \
--no-pager \
| grep 'Achievements:'
The restart should show:
Achievements: final save() before exit
Achievements: loaded X DB profile(s), Y progress counter(s)
Achievements: database persistence enabled
Achievements: system initialized
What you must not see as a new startup action is another historical archive import.
In other words, after the database contains the durable profiles, a normal restart reloads MariaDB rather than replaying the archive collection.
MB646 was designed to fail conservatively.
If the four tables do not exist, Mediabot keeps legacy JSON mode and logs:
Achievements: DB persistence tables are missing; using legacy JSON fallback.
Apply install/migrations/20260816_achievements_db.sql.
This is a compatibility safety net, not the desired final state.
Apply the migration rather than leaving a production installation indefinitely in fallback mode.
The DB import is transactional.
If the import fails:
Do not delete legacy JSON or release archives until the import has been validated.
For duplicate information found across old release snapshots:
This allows MB646 to recover useful history from multiple old releases instead of trusting only the newest JSON, which may itself already be incomplete.
Not immediately.
Keep:
the pre-MB646 JSON backup
the database backup
the most recent old release tree
until all of the following are true:
first import succeeded
database counts were checked
Mediabot restarted successfully
the restart loaded MariaDB without a new archive import
normal achievement commands behave correctly
Only then should normal release-retention policy remove old deployment trees.
The .migrated-<timestamp> JSON is also useful as migration evidence and should not be deleted during the initial validation window.
A fresh installation does not need to replay the historical migration stack.
The four MB646 tables are part of:
install/mediabot.sql
Create the database using the normal fresh-install procedure and validate the reference schema normally.
The standalone 20260816_achievements_db.sql file is for upgrading an existing database.
mediabot.pl is now tracked by Git as executable.
Git stores only the executable bit, so the repository mode is:
100755
A deployment can still apply a stricter runtime permission such as:
750
according to its ownership and security policy.
The final focused achievement/update regression passed:
PASSED : 256/256
The full Mediabot suite passed:
PASSED : 12964/12964
RC=0
The migration was also validated through the complete operational cycle:
legacy state present
→ clean shutdown
→ legacy backup
→ code deployment with bot stopped
→ empty achievement tables verified
→ first historical import
→ DB row counts verified
→ clean restart
→ same DB-backed state reloaded
→ no second historical import
A directory-swap deployment was additionally validated with 11 legacy archive sources while proving that zero sources from a sibling Mediabot deployment family were considered.
Before MB646:
release-local JSON
→ update rotates directory
→ achievement history can be stranded
After MB646:
IRC identity aliases
↓
durable per-channel profile
↓
MariaDB unlocks + progress
↓
restart/update-safe achievement history
The JSON file is no longer the castle’s only copy of the ledger.
MariaDB is now the durable source of truth.
You must be logged in to reply.