Development chronicle — mb565 to mb571 · 3.4dev

The castle’s oldest register had grown enormous: millions of entries, years of conversations, duplicated bookmarks, and enough paper to slow every new line written by the scribes.

This series of changes does not burn the past. It measures it, copies it safely, moves only what should leave the Great Hall, and teaches the portraits where to look afterward. 🏰

📚 mb565 — The steward takes inventory

A new read-only tool now examines CHANNEL_LOG and the surrounding MariaDB database:

perl tools/analyze_channel_log.pl --conf=mediabot.conf

It reports:

• volume and yearly growth;
• recent monthly activity;
• event-type distribution;
• busiest channels and nicknames;
• data size, index size and fragmentation;
• auto-increment capacity;
• existing and missing indexes;
• the largest tables and mixed collations.

The tool never changes the database. It proposes useful online DDL, but the final decision remains with the operator.

🗄️ mb566 — Pages are copied before they leave

A second tool provides controlled, reversible cleanup:

perl tools/archive_channel_log.pl --conf=mediabot.conf --analyze-month 2026-04

Execution is deliberately guarded:

• dry-run by default;
• explicit filters required;
• gzip archive created before deletion;
• exported row count verified;
• deletion performed in bounded batches;
• passwords and message content never printed;
• an incomplete or mismatched archive aborts the deletion.

The archivist counts every page before removing a single sheet. 📜

✒️ mb567 — The sealed scroll learns UTF-8

The first real archive encountered accents and emoji returned by DBI as Perl character strings.

The gzip writer now encodes wide characters as UTF-8 bytes before sealing the archive. Existing byte strings remain untouched.

The safety design proved its value: the export failed before the delete phase, so no database row was lost.

📣 mb568 — The crier stops tearing up announcements

The AntiFlood path previously discarded the bot’s own excess output.

That meant long replies such as leaderboards, horoscopes, statistics or chronologies could lose their final lines without any visible error.

Mediabot now keeps a bounded queue per channel:

• up to 30 deferred messages;
• one message released every two seconds;
• original order preserved;
• a message blocked again returns to the front;
• both normal messages and actions use the same protection;
• a full queue is logged instead of silently ignored.

The castle crier now waits for silence instead of throwing away the parchment. 📯

🧭 mb569 — A daily route to the archive

A scheduled channel_log_archive task can now move old presence events into a real MariaDB archive table.

The process is replay-safe:

1. select a bounded batch;
2. copy it with INSERT IGNORE;
3. verify that the complete batch exists in the archive;
4. delete only after successful verification.

If the bot was offline for several days, the next run catches up automatically.

A new normalization tool also defines the canonical CHANNEL_LOG indexes:

perl tools/normalize_channel_log_indexes.pl --conf=mediabot.conf

Dry-run remains the default; --execute must be requested explicitly.

🖼️ mb570 — The portraits read both libraries

Old public conversations may now be archived separately from presence events.

The policy is opt-in:

[mysql]
CHANNEL_LOG_ARCHIVE_DBNAME=mediabot2
CHANNEL_LOG_ARCHIVE_CONTENT_DAYS=730
CHANNEL_LOG_ARCHIVE_MAX_PER_RUN=2000000

The onthisday command reads both the live table and the archive:

• years are merged;
• message totals are combined;
• the top nickname is selected across both sources;
• the historical quotation comes from the table containing that year.

Without an archive configuration, historical behaviour remains unchanged.

The portraits still remember what happened years ago — they simply consult the annex when the original volume has moved. ✨

🛡️ mb571 — The archive wards are strengthened

The final pre-commit inspection added several important safeguards.

The IRC loop stays responsive

Large scheduled archive passes no longer perform millions of database operations directly inside the main IO loop. Heavy work is isolated so the bot can continue serving IRC traffic.

The exported selection is frozen

Manual gzip archives now operate on an exact bounded selection. Rows arriving while an export is running cannot accidentally enter the later delete phase without being present in the archive.

Private archives stay private

Archive files are created with restrictive permissions suitable for channel history.

Index replacement is fail-safe

The normalization tool does not remove an existing index when creation of its intended replacement has failed.

Legacy purge cannot outrun archival work

The historical purge path cannot delete rows that a bounded archive run has not yet copied and verified.

Documentation states the real scope

onthisday reads live and archived conversations. Other lifetime statistics and achievements continue to follow the live retention window unless they are explicitly adapted later.

The final regression test is deterministic

The LUSERS throttle test now uses a fixed expired timestamp, avoiding full-suite sensitivity to shared-package imports or wall-clock adjustments.

🧪 Validation

The work was developed through complete-suite milestones reaching more than eleven thousand passing assertions.

Before commit, the final tree is validated locally with:

perl t/test_commands.pl

The pushed branch is then checked independently by the GitHub CI.

🏗️ Deployment notes

For an existing instance, create and grant the archive database before enabling scheduled archival:

CREATE DATABASE IF NOT EXISTS mediabot2 CHARACTER SET utf8mb4;
GRANT SELECT, INSERT, CREATE ON mediabot2.* TO 'bot_user'@'localhost';

Start with conservative limits. For a large historical catch-up, remember that a limit of two million rows per daily run requires at least three runs to move more than 4.6 million eligible rows.

After the mass movement:

1. run OPTIMIZE TABLE CHANNEL_LOG;
2. execute the index normalizer;
3. run the analyzer again;
4. compare the before-and-after report.

⚡ Final parchment

The castle keeps its memories, but the Great Hall no longer carries every book at once.

The steward measures.
The archivist copies and verifies.
The crier waits instead of discarding.
The portraits consult both libraries.
And every dangerous operation remains bounded, reversible and visible.