Finally migrating failheap‑challenge

The source database declared every text column latin1_swedish_ci. The bytes inside those columns were UTF‑8.

Nobody chose this. latin1_swedish_ci was MySQL's compiled-in default from 4.1 (2004) until 8.0 (2018), and this server runs 5.0.95 — so every CREATE TABLE that did not name a charset silently got latin1. Neither phpBB nor vBulletin ever named one:

tables at latin1_swedish_ci        204
tables at utf8_general_ci            1   # tsx_server, a 2014 add-on
columns with explicit non-latin1     4   # all four in that same add-on table

The post table makes it plain — pagetext carries no CHARACTER SET clause of its own, and the table ends with a bare DEFAULT CHARSET=latin1 it inherited from the server. In 204 tables the forum software specified a charset exactly zero times; the only table that did came from a third party, three years after the migration.

Both applications got away with it because each stored UTF‑8 bytes and told MySQL the connection was latin1. MySQL passed the bytes through untouched and handed back exactly what it was given. Byte in, byte out — the declared charset never mattered, because nothing in the chain ever tried to interpret it. That held for fifteen years.

The UTF‑8 bytes themselves arrived with the phpBB import, and the database can date that precisely. vBulletin's import*id columns sit empty on a forum that was never imported into; here they are populated on 133,388 posts and 1,986 users, and the seam is sharp enough to date to the hour:

last post carrying an import id   2011-07-01 21:20:56
first post with none              2011-07-01 22:34:06   # 73 minutes later

imported content spans            2011-04-09 → 2011-07-01   # ~12 weeks
native vBulletin content spans    2011-07-01 → 2026-06-24   # everything since

So the forum ran roughly three months on phpBB and moved to vBulletin on 1 July 2011, carrying its history across. The oldest posts in the archive — a thread where people are racing to reply "second" and arguing about "'11s" — are the phpBB forum's opening day, preserved through the move with their original timestamps.

Left alone this is harmless — vBulletin read back the same bytes it wrote. It becomes destructive the moment you run mysqldump normally, because MySQL then "helpfully" re-encodes on the way out.

Confirming it was safe to fix

A uniform mislabelling is recoverable with one rule. A mixed encoding — some rows genuinely latin1, others UTF‑8 — is not, and always loses something. So the first job was proving which case this was.

-- how many posts contain any high byte?
SELECT COUNT(*) FROM post
WHERE CONVERT(pagetext USING BINARY)
      RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']');
-- 43,125

-- of those, how many are valid UTF-8 when reinterpreted?
SELECT COUNT(*) FROM post
WHERE CONVERT(pagetext USING BINARY)
      RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']')
  AND CONVERT(CONVERT(pagetext USING BINARY) USING utf8) IS NOT NULL;
-- 43,125  ← every single one. Zero exceptions.

Both numbers matching is the green light: the whole archive is UTF‑8 wearing the wrong label, and one uniform rule fixes it.

# on the source
mysqldump --default-character-set=latin1 --skip-set-charset ...

# while loading
sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4/g'

The dumps that were already there

Three earlier dumps sat in /root on the old server. All three carry SET NAMES utf8, meaning they were taken without the passthrough flags. Verified byte-for-byte against the new dump: they are double-encoded throughout. Importing from any of them would have baked é into 43,125 posts permanently.

What that looks like in practice

A French post from 2012, as it survives in the correct dump, and as the same sentence appears in the three dumps that were already sitting on the server:

Those three dumps were the obvious thing to import from — they were already made, already on disk, and nothing about them looks broken until you go looking for a specific character. Using any of them would have baked that second column into 43,125 posts permanently.

Roughly half the database was junk that should never be migrated: 382 MB of spam-filter logs, 977 MB of search index that Discourse rebuilds itself, plus edit history and BBCode caches. Dumping only the 36 content tables cut 1.4 GB of dead weight.

Discourse's importer is not a standalone converter — it is a Ruby script that runs inside a working Discourse instance, reading from a live MySQL copy of the old forum. So both halves have to exist at once.

Things that had to be fixed to get this far

The work took about four weeks. Three of those were planning and test runs; the final push ran from a Saturday to the following Tuesday. Almost none of that was the import itself.

A full move takes roughly three days, and it has to be done more than once. Each pass turns up damage the previous one hid, so the sequence is: run it, find what broke, fix it, run it again. The import is the cheap part. Finding out what it quietly got wrong is the expensive part.

Two full passes over 1.9 million posts, back to back, single-threaded. The importer is CPU-bound in Ruby at about twenty posts a second, on one core, with the database idle and fifteen others doing nothing. There is no parallelism switch. That is how long it takes.

It also has to survive being interrupted, and it does: every record is stamped with its original vBulletin id as it lands, so a restart reloads that index and skips what already exists. Killing it loses nothing. That property is what made repeated passes affordable, and it is worth checking for before starting anything this long.

The other reason for the repeat runs is that damage does not announce itself. A row count that drifts by forty. A progress bar attributed to the wrong phase. Avatars present in the database and invisible on the page. None of it raises an error; all of it is found by looking at the result rather than the exit code.

Getting the data out

Before any of it could be imported it had to be copied off a 2006-era server, and scp lied about doing so. It reported success, exited zero, and delivered 209 MB of a 375 MB file. The truncation was only visible because the resulting dump would not import.

The fix was to stop trusting the transfer: split the file into chunks, checksum each one on both ends, and retry any that disagreed. Every large transfer in this project has been checksum-verified since, and that habit caught nothing else — which is the point. You do not find out you needed it until you did not do it.

The hang at 91%

Sixteen hours into a run, at 1,704,559 posts, the import stopped dead. It did not crash and did not error: the process stayed alive pinned at 100% CPU, holding an open Postgres transaction with 181 locks and no query issued for fifteen minutes. The log stopped growing at the same instant.

The open transaction named the topic, and walking the source table to that offset found the post responsible:

post 1776968, by "Sp4m"
length      99,545 characters
[IMG] tags  1,326   ← the same image URL, over and over

A joke post: one image repeated thirteen hundred times. The BBCode converter parses every tag and resolves it against the surrounding text, and on that input it went quadratic and never came back. Pinned CPU, no disk activity, no memory growth, no progress — catastrophic regex backtracking, which looks exactly like healthy work from the outside.

Collapsing the repeats in the staging database took the post from 99,545 characters to 188, keeping the first image and a note about what was removed. The post keeps its meaning and its place in the thread.

Deleting an account in vBulletin asks what to do with its posts, and the answer here was usually to keep them. Removing fifteen years of somebody's replies would gut the threads they appear in, leaving conversations that answer nobody. So the account went and the posts stayed: post.userid set to zero, the display name left sitting in post.username, and the thread still reading exactly as it always had.

That happened for two quite different reasons. Some people asked for their account to be removed, in which case it would be renamed first and then deleted, so the posts survived under a name that was no longer theirs. Others lost the account as a punishment. Either way the intent was the same: the person leaves, the conversation stays intact.

Discourse's importer reads only post.userid. When that lookup fails it falls through to the system account and the username in the very same database row is discarded.

# script/import_scripts/vbulletin.rb, line 428
user_id: user_id_from_imported_user_id(post["userid"]) || Discourse::SYSTEM_USER_ID
#                                                          ^ post["username"] is never read

Two people account for 65,000 of them. On vBulletin those posts showed as written by smuggo and XenosisReaper; after import they show as written by the system account, with the names gone from threads that are still actively read.

The fix

The stored ids let the posts be joined back to post.username afterwards, with no re-import. For each of the 365 names an account is created and the posts are moved onto it. Threads then read the way they did on vBulletin.

The question is what kind of account. Discourse offers a staged user — an account that cannot be logged into but that anyone able to receive mail at its address can claim. That is the usual choice for imported guest content, and it is the wrong one here.

./fix-orphan-authors.sh check   # read-only, shows what would change
./fix-orphan-authors.sh apply   # create the accounts and reassign

One name needed special handling. FraXy is the only one of the 365 who also still has a live account — seven posts under the surviving account, sixteen more orphaned under the same name. Creating a suspended duplicate would have split one person across two accounts, so the script detects that case and returns the orphaned posts to the existing account instead.

The script refuses to run while the importer is active, and re-running it is harmless. A handful of posts — 33 of the 71,761 — have no username recorded at all and stay on the system account, because there is nothing to restore.

This is the largest defect in the migration, and it went unnoticed for most of it because the import reports nothing wrong. Posts arrive, counts match, text is intact — and nearly half of them render their markup as visible text.

Quotes: 905,000 posts, and a lesson about reading the whole program

Discourse converts BBCode during import with ruby-bbcode-to-md. Its quote pattern matches lowercase [quote]. vBulletin's Reply‑With‑Quote button writes uppercase, which is how almost every quote on a fifteen-year-old forum was made. So mid-import the database looks like this:

[QUOTE=spasm;148785]The Indians would probably respond with pig-bombs.[/QUOTE]
← what a reader sees, verbatim, instead of a quote block

uppercase, untranslated .... 828,413 posts
lowercase, translated fine ..  87,282 posts

Nearly half the forum rendering as raw markup, with no error and every count reconciling. The obvious conclusion was that this is the migration's biggest defect and needs a repair script. A script was written, tested, and very nearly run.

[QUOTE=spasm;148785]…[/QUOTE]
        ↓
[quote="spasm,post:12,topic:4567"]…[/quote]

The repair script produced plain markdown blockquotes. Correct-looking, and strictly worse — no link, no jump target. Worse still, it would have consumed the [QUOTE=user;postid] tags that post_process_posts needs as input. Running it first would have permanently downgraded 905,000 quotes from linked to inert, and the designed phase would then have found nothing to do.

Video: 53,662 posts

vBulletin 4's own video tag — [video=youtube;CUUiHQRu6Xo]…[/video] — is not in the converter at all. 48,897 YouTube, 4,165 of a second YouTube variant, 555 Vimeo, 45 Dailymotion. Discourse embeds a bare video URL sitting on its own line, so the repair is to reduce each tag to the URL it wrapped and let oneboxing do the rest.

Seven custom tags

No generic converter could have handled these: they were defined on this forum, and they live in a bbcode table that is not part of the content dump at all. Finding them meant going back to the source server and reading it directly.

These are the counts measured in Discourse after the import, restricted to tags with a matching closer — prose mentions of a tag and markdown links that look like one are excluded, which is why some numbers are lower than a naive text search suggests.

[y2kpp] is the smallest entry and the easiest decision: it was a one-shot tag built for a single event, an incursion into the forum's EVE Online community, and it has no use beyond it. Both surviving instances are [y2kpp][/y2kpp] with nothing inside. Dropped without ceremony.

[strike] carries a fingerprint of the older migration. Some instances read [strike:2z0scad4] — that suffix is a phpBB uid, attached to every tag phpBB parsed so it could tell real markup from text a user had typed. It survived the 2011 move into vBulletin and sat in the database untouched for fifteen years.

The spoiler tag is the one that matters. 40,265 posts used it, and its vBulletin definition is a block of inline JavaScript with a show/hide button — markup that means nothing to Discourse. It maps cleanly onto a native collapsible block.

What it took

55,245 posts were rewritten. Every media tag turned out to wrap a plain URL, so reducing each one to the URL on its own line lets Discourse's own link handling produce a real embed — no bespoke rendering needed:

[h5VIDEO]http://i.imgur.com/aTeNcGA.webm[/h5VIDEO]        →   http://i.imgur.com/aTeNcGA.webm
[SOUNDCLOUD]http://soundcloud.com/matas/hobnotropic[/…]  →   http://soundcloud.com/matas/hobnotropic
[video=youtube_share;CUUiHQRu6Xo]http://youtu.be/…[/video] →   http://youtu.be/CUUiHQRu6Xo
[strike:2z0scad4]Navy Raven (Jita - 560~)[/strike:…]   →   ~~Navy Raven (Jita - 560~)~~

The quotes themselves were handled by the importer, not by this script — and handled better than a hand-written fixer could. This is a real post from the forum, four people quoting each other in a chain:

[QUOTE=Nimbly Bimbly;635596][QUOTE=Eard;635546]
[QUOTE=Rep;635488][QUOTE=Hels;635478]Plan to
pick it up again after the standalone
releases.[/QUOTE][/QUOTE][/QUOTE][/QUOTE]

[quote="Nimbly Bimbly,post:412,topic:8891"]
[quote="Eard,post:408,topic:8891"]
[quote="Rep,post:405,topic:8891"]
[quote="Hels,post:403,topic:8891"]
Plan to pick it up again after the
standalone releases.
[/quote][/quote][/quote][/quote]

Each name resolves to the post it quotes, so the rendered quote is clickable and jumps to the original — the post ids come from the same import-id mapping used everywhere else. That is the part a hand-rolled converter could not have reproduced, and the reason for not pre-empting the importer with one.

The spoiler tag, including the uppercase variant the converter also missed:

test
[spoiler]icle[/spoiler]

[SPOILER]testicle[/SPOILER]

test

[details="Spoiler"]
icle
[/details]

[details="Spoiler"]
testicle
[/details]

And video, where the tag is reduced to the URL it wrapped so that Discourse embeds it:

PVP Video:

[video=youtube_share;CUUiHQRu6Xo]
http://youtu.be/CUUiHQRu6Xo[/video]

PVP Video:

http://youtu.be/CUUiHQRu6Xo

← renders as an embedded player

The script clears each post's baked_version, so Discourse re-renders them in the background afterwards.

The host had crashed mid-import days earlier. The import resumed and finished, but the database kept a souvenir. The first query run against the finished forum failed like this:

ERROR:  missing chunk number 0 for toast value 567848 in pg_toast_26316
CONTEXT:  parallel worker

Postgres stores oversized column values — here, post bodies — outside the main table in a side table called TOAST. This says a row points at content that is no longer there. It is the error people find when they search for whether their database is corrupt.

What made it confusing is that the same query worked when restricted to a range of post ids, and failed only when run against the whole table. That difference is the diagnosis: a narrow query uses an index and reads only live rows, while an unrestricted one falls back to a sequential scan and walks every physical row on disk, including dead ones no query can return.

scanned 1927108 rows, 0 bad

The practical effect is narrow. Discourse itself never trips over it, because Discourse queries by id and stays on the index. It only appears when something sweeps the whole table — which is exactly what measuring the leftover BBCode was about to do.

With the import finished, the obvious question is how many quotes post_process_posts actually fixed. The obvious query gives a frightening answer:

posts matching [quote=   928,069   # unchanged from before the phase ran

Which would mean fifteen hours of post-processing had achieved nothing. It had not. The pattern matches both formats — the vBulletin tag it was looking for and the Discourse tag it had been rewritten into. The converted form puts a double quote straight after quote=; the vBulletin form never does, and that one character separates them.

So the phase worked, and a second query said 13,574 posts still held raw tags — 98.5% converted, a rounding error's worth of work left. That number was wrong by a factor of twenty-five, and the way it was wrong is worth more than the number.

A query that could not see the problem

The count asked for posts holding a vBulletin tag and no converted one:

raw ILIKE '%[quote=%'  AND  raw NOT LIKE '%[quote="%'

That second condition sounds like a sensible way to exclude finished work. It is in fact a way to exclude the evidence. The importer converts only the outermost quote in a post and stops, so a quote-of-a-quote ends up holding both forms at once:

[quote="Frug,post:151,topic:18768"]              converted
[QUOTE=erichkknaar;1695312]                      left as literal text
[QUOTE=Approaching Walrus;1695153]have some pure ideology
[/QUOTE][/QUOTE]

Every post like that contains [quote=", so the query threw it away. What it measured was the small population of posts where the importer had converted nothing at all — and reported that as the whole problem. Dropping the second condition changed the answer from 13,574 to 350,254.

What was actually left

Resolving them works the same way the importer does: look the vBulletin post id up in the import-id map and build a linked quote, falling back to a plain attributed one where the target was never imported.

posts changed          355,515
tags → linked quote     627,137
tags → plain quote        22,885
stray [/QUOTE] closers   1,046,087

1,871 tags carry no post id at all — [quote=Wired News], [quote=the article] — because they quote a source rather than a member. There is nothing to link to, and they become plain attributed blockquotes, which is what they always were.

179 posts still match the pattern and none of them should change: their [quote= sits inside a code block, where it is content rather than markup.

The videos were links, not videos

Converting the video tag to the URL it wrapped was correct, but vBulletin had stored those URLs without a scheme — //youtu.be/WSKi8HfcxEk. Discourse only embeds a URL it can resolve, so 51,326 posts rendered a bare blue link where a player belonged. Restoring https: turned every one of them back into a video.

vBulletin had 4,858 users. Discourse ended up with 4,840. The eighteen missing accounts left no error, no warning and no log line — the importer simply did not create them.

Twelve were lost to a single setting. Discourse's normalize_emails defaults to true, which strips Gmail dots and +tags before comparing addresses. Two accounts that vBulletin considered entirely separate look identical to it:

58644  jsmith      [email protected]   ✓ imported
58645  jsmith2     [email protected]   ✗ silently skipped
58647  jsmith_b    [email protected]   ✗ silently skipped
58696  smithj      [email protected]   ✗ silently skipped

all four normalize to  [email protected]

The other six collided on username instead — different people who happened to share a name with someone already imported. Between them the eighteen account for 1,279 posts, which landed either on the system account or on the same-named account that won the race.

All eighteen were recreated afterwards, keeping their real vBulletin address and join date, and their posts moved back onto them. It is a small job — two of the eighteen carried almost all those posts and had already been recovered by hand, leaving sixteen accounts and 107 posts — but the alternative was leaving sixteen people's authorship wrong to avoid touching a database that had already been reviewed. Every one of the 4,858 vBulletin accounts now exists.

A ban evasion the merge would have erased

One of the twelve is worth telling in full, because it is the clearest illustration of what gets lost. Two accounts, same person, and both banned:

Banned in 2017 with an explicit invitation to come back. Returned sixteen months later under [email protected] — a plus-alias of the original address — and was recognised after seventeen posts. The second ban reason is two words long and makes the whole story legible.

Because the alias normalized to the original, the second account was never created and its seventeen posts fell to the system account. Both bans collapse into one, and the return, the alias and the recognition all disappear — leaving a single 2017 ban and no evidence anything else ever happened.

Splitting them back apart is straightforward once the cause is known, because nothing had to be moved off the surviving account: the posts were never merged onto it in the first place. Recreate the second account from its vBulletin id, move its seventeen posts across, and apply its own ban with its own date and reason. The only obstacle is that normalize_emails blocks the fix for the same reason it caused the problem — so it is switched off for the duration of the operation and restored immediately afterwards.

A couple of hundred posts held a video tag that was broken:

[video=youtu /video]        [video=  video]        [video=y  eo]

Checking the source database showed the damage was not caused by the migration — vBulletin had been displaying these as literal text for years. The provider name and video id were gone. The obvious conclusion is that nothing can be done, and the obvious conclusion is wrong.

Look at where the broken tags sit:

[QUOTE=Jack bubu;164505][video=youtu /video]

:O[/QUOTE]

Every one is inside a quote. These were not posts that contained a video — they were posts quoting a post that contained a video, and vBulletin's Reply‑With‑Quote mangled the tag while copying it. The original is untouched, and the quote records exactly which post it came from.

post 164505 (the original, intact):
  [video=youtube;BpA6TC0T_Lw]http://www.youtube.com/watch?v=BpA6TC0T_Lw[/video]

So the id is recoverable: read the quoted post id out of the broken post, look up that post in the source database, and pull the video id from its intact tag. Ten videos came back that way, each restored to the quote that had lost it. Five more could not be — the post they quoted had no video tag of its own, having been mangled further up a longer chain.

A fingerprint from 2011

4,370 posts carried tags that looked like this:

[quote:2u7yqhbz]...[/quote:2u7yqhbz]        [spoiler:3p26ccso]...[/spoiler:3p26ccso]

That suffix is a phpBB uid. phpBB stamped every tag it parsed with a short random string, one per post, so its renderer could tell markup it had produced from text a user had typed. The suffixes came across in the 2011 migration, meant nothing to vBulletin, and sat in the database being displayed as literal text for fifteen years. Removing the suffix leaves an ordinary tag, which the conversions used everywhere else then handle.

These are the oldest posts on the forum, and their markup is the last trace of the software it ran on before.

The avatars that were imported and invisible

The import brought 1,207 avatars across, and the files sat correctly on disk. The forum showed letter circles for everyone.

Discourse does not serve an avatar from the uploaded file. It serves a resized copy from an optimized_images table, generated by a background job the first time one is needed. That job runs under Sidekiq, and nothing had started Sidekiq on a machine being used for repair work. The table was empty, so every avatar request fell through to the fallback.

Generating the 10,827 thumbnails directly took a few minutes. On the production machine none of this applies — Sidekiq runs, and the job does it unprompted.

Every YouTube link on the forum rendered as a plain blue link. The URL was right, the markup Discourse produced was right, and no video ever appeared.

The obvious conclusion is that something in the imported posts is malformed, and that conclusion survived three rounds of increasingly precise edits to the stored HTML — each one comparing the imported markup against what Discourse generates itself, finding a small difference, correcting it, and changing nothing on screen.

Two environmental causes, neither in the data:

Sidekiq was not running. Resolving a onebox — fetching a video's title and thumbnail — is a background job. Without the worker, Discourse marks a link for oneboxing and never finishes the job, leaving exactly the bare link that was showing.

Plugin JavaScript had never been built. The frontend was built earlier with pnpm build, which builds Discourse core and deliberately refuses to import plugin modules — plugins compile through a separate task:

rake assets:precompile:build_plugins        # 44 plugins, 64 seconds

Without it discourse-lazy-videos never loads, so nothing turns a video container into a player. The container was in the page the whole time, inert.

Both are artefacts of running repair work on a development image. The production container compiles plugin assets during bootstrap and supervises Sidekiq, so neither failure can occur there — which is the argument for deploying onto the official image rather than promoting the machine the repairs were done on.

A million jobs that were not a problem

Starting Sidekiq immediately produced an admin warning: 1,386,127 queued jobs, almost all of them PullHotlinkedImages — Discourse offering to download and re-host every externally linked image on the forum.

That reads like days of crawling and a serious decision about link rot. It is neither. The queue holds one job per post per rebake, and the posts had been rebaked several times; nearly every job opens a post, finds nothing external, and exits. Counting the actual images gives a very different picture:

external image URLs in cooked posts   47,783
  — of which img.youtube.com          46,997   # thumbnails generated locally, regenerable
  — genuinely external                   786   # across 426 posts

786 images, in 426 posts — imgur albums, CCP dev blogs, personal sites. Those are the ones that vanish when a host lapses, and they are worth keeping. Running them directly took minutes rather than days, and the rest of the queue was discarded: clearing it removes no data, since the URLs stay in the posts and the images keep loading from wherever they live. The job only ever made local copies.

vBulletin stored passwords as md5(md5(password) + salt). Discourse uses PBKDF2 and cannot verify a vBulletin hash, so no old password can survive a migration. What is easy to miss is what the importer does instead.

vBulletin row      md5hash, salt
importer builds    "md5hash:salt"     # joined, and passed as the PLAINTEXT password
Discourse stores   pbkdf2(that string, 600,000 iterations)

The hash and salt are concatenated and handed to Discourse as though a user had typed them. Discourse dutifully hashes the result. Every one of the 4,841 accounts ends up with a valid, working credential — one derived from the old database and known to nobody, not even its owner.

So the credentials were deleted outright rather than left in place. All 4,842 rows are gone, along with every session token. Every account now has no password at all, and the only way into one is a reset link sent to its registered address — which is the intended route for a forum that has been offline for a year regardless.

=== deleting all user_passwords rows ===
DELETE 4842
  credentials remaining: 0
  expiring all active sessions

This ran last, after an administrator account existed — wiping every password on a forum with no admin is a memorable way to lock yourself out of your own site.

A forum's smilies are part of how it sounds. Failheap had 228 of them, and none arrived. The importer reads the smilie table exactly as attentively as it reads the bbcode table — which is to say not at all, and that table was never in the 36‑table content dump either.

The posts, though, were fine. vBulletin substituted smilie images at render time, so what sits in the database is the plain code the author typed:

stored in vBulletin    "well that went badly :psyduck:"
rendered by vBulletin  <img src="images/smilies/psyduck.gif">
stored in Discourse    "well that went badly :psyduck:"   # unchanged

Discourse uses that same :name: syntax for custom emoji. So this was never a text substitution — it was an upload. Put each image in under its matching name and tens of thousands of posts start rendering correctly without a single character of post content being touched.

Which of the 228 actually mattered

Guessing from filenames would have been the obvious mistake. The set was built from two sources and intersected: every :code: extracted from 1.9M post bodies in one pass, against the image directory.

codes found in posts   6,037 distinct   # most are ordinary text - ":30:", ":1:"
smilie images on disk    228
intersection              50

Which left 178 images with no detected usage, and 5,987 codes with no image. Both numbers looked settled until the forum's owner read the list and said the only one that surprised him at zero was :companion-cube:.

Fifty-one, then. And immediately a second problem, which is that twenty of those names already mean something to Discourse.

Twenty names that were already taken

Discourse ships the standard Unicode emoji, and a custom emoji overrides the built-in of the same name everywhere — there is no separate namespace. Upload one called lock and every :lock: on the site becomes a forum padlock, in every future post by every user.

Three options, each costing something. Take the name and Unicode loses it site‑wide. Skip it and 10,597 old posts quietly render the wrong picture — worse than it sounds, because the post still works, it just says something else. Or rename the custom version to :fhc_lock: and rewrite the old posts to match.

The third is the only one that loses nothing, and it is also the only one that writes to raw — 13,921 posts of it. So the work split cleanly in two: the 31 names Unicode has never heard of are purely additive and went in immediately; the 20 collisions are written, dry‑run, and deliberately left for a quieter day.

What the dry run caught

Running the rewrite against all 13,921 real posts before touching production found two bugs that would otherwise have shipped.

Regexp.union(NAMES)  →  :((?-mix:lock|troll|mad)):   # (?-mix:) turns /i OFF
NAMES.join("|")      →  :(lock|troll|mad):           # honours it

union form   13,881 posts
join form    13,921 posts

Ruby's Regexp.union emits a group that explicitly disables the case‑insensitive flag, silently overriding the /i on the outside. The forty‑post gap is real: vBulletin matched smilies case‑insensitively, so people wrote :MAD: and :SMUG:. Discourse emoji names are lowercase‑only, which means the replacement has to downcase as well — or those forty posts get :fhc_SMUG:, a dead emoji that renders as bare text.

The pattern itself needed no code‑block parser, but that was measured rather than assumed: 0 of the 13,921 posts contain a colliding code inside a URL, and the 11 that contain a [code] block were read by hand — in every one the smilie sits outside the block. Colon‑heavy block content like a PHP ternary does not match, because the pattern requires an exact name between colons.

Installing into a live forum

This was the first change made with the forum already up, which changed the order of operations: back up first, then the additive half only. Two things went wrong, both mine, and neither touched any data.

Peer authentication failed for user "discourse"   # ran rails as root
Errno::EISDIR: Is a directory @ io_fread          # docker cp nested a leftover dir

Postgres in the official image uses peer authentication, matching the OS user to the database user, so docker exec needs -u discourse or Rails dies before doing anything. And docker cp into an existing directory nests it rather than replacing it, so a retry after a failure produced /tmp/fhc-emoji/fhc-emoji and the loop tried to read a directory as an image.

All 31 installed. The last step is the one that is easy to forget: emoji render from cooked HTML, so nothing changes on screen until the posts are re-rendered. Discourse will not revisit a post it considers current, so the 48,348 posts using those 31 names had their baked_version cleared, at which point Sidekiq worked through them on its own at roughly 400 a minute.

225 of them — half a percent — will never render, because the code is glued directly to a word, as in now:obama:. Discourse requires a boundary before the opening colon. That is a fair place to stop.

A week after going live, a member wrote in: “I think my username lost a ‘v’ in the migration.” Their profile read lt1, and they remembered being ltv1.

The source database settles it in one query. Their vBulletin username was lt — two characters, hex 6C74, no v anywhere. What changed was not a letter going missing but one being added:

vBulletin   "lt"     2 characters
Discourse   "lt1"    # minimum username length is 3

Discourse enforces a three-character minimum, and the importer pads anything shorter with 1 until it fits. So lt became lt1, and a one-character name needs two of them — V became V11. Read back a week later, in a name you have typed a thousand times, lt1 looks exactly like ltv1 with something knocked out. The display name is still plain lt, which is the tell.

Twenty-three accounts were padded this way. Two of them are people who actually posted:

What else the username rules did

The padding is one of four rules, and the announcement email had already mentioned a different one. Comparing every imported account against its vBulletin name gives the whole picture:

1,103 of 4,857 accounts were renamed — 22.7%, which is the figure that went into the announcement after the draft's claim that everyone kept their name was checked and found false. (Section 18 quotes it as 1,104 of 4,858: this comparison skips the one account vBulletin stored with no username at all, since there is nothing to compare it against. Same percentage either way.)

The last row of that table is the interesting one. Five accounts had names Discourse could not transliterate into anything usable at all, and they fell through to a generic user1…user5:

vB 1319   (empty)      → user1     411 posts
vB 2350   ???-???????  → user2      15 posts
vB 2552   ?????????    → user3       0 posts
vB 3480   Я            → user4       2 posts
vB 9611   !!!          → user5       3 posts

Two of those are worth separating. user4 was a single Cyrillic character and user2/user3 are non-Latin names that the source database had already reduced to question marks years ago — the latin1 mislabelling from section 1, in the one place it did cause real loss. The original characters are not recoverable from this data, because vBulletin never stored them.

user1 is different again: vBulletin had no username at all for it, an empty string on an account with 411 posts. Discourse cannot represent that, so it got a generated name like the rest.

Whether to change it back

min_username_length is a site setting, so lowering it to 2 would let the twelve two-character names be restored — including Me and lt, which between them hold 10,382 posts. The one-character names could not be restored without going to 1, which is a bad idea: single-character handles collide with routing and make @‑mention parsing ambiguous.

Left at 3. Twenty-three accounts out of 4,858 is half a percent, and relaxing a global validation to fix it changes the rule for every future registration as well — a wider blast radius than the problem. Anyone who dislikes their padded name can change it themselves in preferences, and whatever they pick will satisfy the rule anyway. The point of writing it down is that a user reporting a bug in their own name was right that something changed and wrong about what — and the only way to tell the difference was to go back to the source bytes rather than reason about what looked plausible.

vBulletin had reputation; Discourse has likes. Nobody had mentioned them, and the assumption going in was that fifteen years of rep was a running total on the user record — a single number, unattributable, and therefore unmigratable.

It is not. vBulletin’s reputation table stores every rep event as its own row, which is almost exactly the shape a like needs:

postid | whoadded | userid | reputation | dateline
  ^        ^          ^                    ^
  which    who gave   who got              when

Discarding the negatives was the only judgement call worth arguing about. Discourse has no downvote at all, and the nearest structure — a moderation flag — is not the same gesture. Resurrecting 15,850 decade-old dislikes as flags against people who still post here would have been actively harmful. The rows were kept in a file rather than deleted, which costs nothing and keeps the decision reversible on paper if not in practice.

The part that would have gone badly

Creating a like the ordinary way goes through PostActionCreator, which calls PostAlerter, which creates a notification, which queues an email. That chain is correct and desirable for a like made by a human at a keyboard. Run 144,522 of them through it and the arithmetic becomes something else:

users by email_level = {0=>1, 1=>5168, 2=>5}
                              ^^^^ "email me when I am away"

So the requirement was not “prefer fewer emails”, it was zero — and the way to get zero is to bypass the layer that sends them. insert_all writes rows straight to the table, skipping validations, callbacks and PostAlerter.

How do you know it skips them? Reading the source gives a strong argument: PostAction.act no longer exists in this version, and the model’s only callback contains no notification logic. But an argument is not a measurement, and this was production — so it was measured on the live database, inside a transaction that was rolled back:

notifications total  : 953 -> 953
liked notifications  : 1 -> 1
post.like_count      : 0          # note this
--- rolled back, nothing persisted ---

Zero notifications, on real data, with nothing kept. The script also carries the same check as a live tripwire: it reads Notification.count before and after the insert and aborts if the number moves.

The cost of going around the front door

That post.like_count : 0 in the test output is the bill. The callback being skipped is the one that maintains Discourse’s denormalised counters, and the heart on a post renders from posts.like_count, not from counting rows. Without recomputing them by hand, the migration would have finished with 144,522 perfectly good likes in the database and absolutely nothing visible on any page.

This is the third time the same trap appeared in this migration — avatars in section 12, emoji needing a rebake in section 15, now counters. Data present is not data visible. Five counters had to be rebuilt in SQL: posts.like_count, posts.like_score, topics.like_count, user_stats.likes_given, user_stats.likes_received.

The run

inserted            : 144,522
notifications       : 958 -> 958      # the whole point
posts with a like   : 105,958
topics with a like  : 7,998
counter drift       : 0 posts, 0 topics, 0 users

92% of positive rep converted. The 12,121 that did not are deleted posts and deleted accounts — content that legitimately is not in Discourse — not mapping failures. Original timestamps were preserved, so the likes sit in history spanning 2011 to 2026 rather than all landing on the day of the run.

Two details worth recording. The dry run had counted 32 pre-existing likes and the real run found 34: two members had liked something in the intervening hours. Both were detected and preserved, which is what the unique index on (post_id, user_id, post_action_type_id) is for — a collision there would have aborted the whole batch rather than silently doubling anything.

And the counters were verified by recomputing them independently afterwards and diffing against what the migration had written, rather than by trusting the script’s own summary of its own work. Zero drift on all three tables. Then the same claim was checked a third way, from outside, against the public JSON the site serves to anonymous visitors:

GET /t/smartbomber-need-sponsors/14003.json
  post 32 → actions_summary: [{ id: 2, count: 54 }]

Two days later, the counters reset themselves

The forum owner asked why his likes had gone. His profile read 39 received where it should have read 533. Across the forum, only 34 users had any likes at all.

Nothing had been lost. All 144,648 like rows were still in the database, none deleted, and posts.like_count still summed to exactly that. The hearts on the posts were fine. It was the profile counters — the last two of the five rebuilt above — that had been zeroed.

The migration wrote user_stats directly. That was correct, and it could never have lasted, because Discourse maintains those two counters from a table the migration never touched:

DirectoryItem.refresh  →  counts user_actions  →  OVERWRITES user_stats

user_actions is the index behind the user directory and the activity stream. Going around the front door had skipped it along with the notifications, so the first scheduled directory refresh counted 103 likes forum-wide and stamped that over the correct total. It undid 1,688 users’ received counts and 1,243 given — almost exactly the 1,691 and 1,245 the migration had set.

The fix is not to write the counter again. It is to populate the table the scheduled job reads, and then let Discourse compute its own numbers. Each like needs two rows, one belonging to the giver and one to the receiver — a shape confirmed against real rows Discourse had created itself, because reversing them would have credited every like to the wrong person:

inserted LIKE      rows : 144,522
inserted WAS_LIKED rows : 144,522
elapsed                 : 10s
notifications           : 7,724 → 7,724
entrox                  : 39 → 533 received

Then the refresh was run a second time, because that is precisely where the first fix had died. The number held. A repair that has not survived the thing that broke it is not a repair yet.

One residue, deliberately left: the profile counters total 144,641 against 144,648 like rows. The seven are likes on posts by deactivated accounts, which the directory excludes by design along with private messages and hidden posts. Anyone measuring against a raw row count will find that gap forever, so it is written down rather than chased.

A counter that a scheduled job owns cannot be fixed by writing to it. Find the table the job reads. The same trap had already been recorded for post counts in this migration — and it was not recognised as the same trap.

What it did not do

Backdated likes feed trust-level promotion, and there was a real question about whether 144k of them would launder a large part of the forum into TL3 overnight. The settings say no, and the reason is that likes are only 2 of 8 criteria while the other six are time-based on a rolling window:

tl3_requires_likes_received = 20     # likes help here
tl3_requires_likes_given    = 30     # and here
tl3_requires_days_visited   = 50     # ...in the last 100 days
tl3_time_period             = 100

A forum that has been live for one day cannot produce fifty days of visits no matter what its like table says. The distribution after the run was unchanged in shape — TL0 331, TL1 4,840, TL4 3 — and nobody was promoted. So the machinery was deliberately left running rather than frozen: the backfilled likes now count as history, people earn levels on real activity, and there is no cleanup pass owed later.

A day after go–live, a user reported that long posts were cut short. The post he linked stopped mid–word at 558 bytes. Our dump had 558 bytes too, and so did the local MySQL container, so we initially thought the damage predated the migration.

Both of those are copies of the same export. Checking an export against copies of itself proves nothing. On the original server — still running, untouched — that post is 8,594 bytes.

The cause

Section 1 covers the encoding fix: the source declared latin1_swedish_ci but stored UTF–8, so the dump passed bytes through untouched. That is right for bytes that are valid UTF–8 — and not all of them were.

Not all of them were. Fifteen years of pasting from Word and typing on British keyboards left bytes that were never valid UTF–8. Loading those into a utf8mb4 column made MySQL silently truncate the value at the first bad byte — no error, no warning. Row counts reconciled perfectly, because no rows were lost. Only their contents.

...promising a [0x93] bone shattering drop on July 14...
               ^ everything from here on was lost

The first measurement was wrong

Byte 0x93 is a Windows–1252 smart quote, so the first scan looked only at the cp1252 punctuation block 0x80–0x9F. That found 7,778 posts.

But any byte above 0x7F that is not part of a valid UTF–8 sequence truncates identically, and the most common offender was 0xA3 — £, on a British gaming forum. Also ¬_¬ emoticons and accented letters. Scanning for invalid UTF–8 instead of a byte range multiplied the count by five:

The 43,125 figure had sat in the notes since week one, labelled as the posts that were “genuinely UTF–8”. Almost none of them were.

The rebuild

Every post carries its vBulletin id, so damaged posts could be rebuilt individually from the live source. The hard part is that a post arrives as BBCode and must go through exactly the transformations the original migration applied, or the repaired posts render differently from their neighbours.

Reimplementing those transformations by hand scored 9 of 32 against undamaged control posts — mostly wrong quote attributions, because the importer had renamed 1,092 users and the reimplementation had not. Using the real importer code and the real BBCode gem, plus the repair passes that ran after it, scored 594 of 603.

What a passing control test missed

603 posts caught three defects. Running all 37,017 caught four more, none of which a sample would contain:

• An illegal codepoint (�) in a 2011 Unicode torture–test paste crashed the entity decoder. The original import never hit it, because MySQL had truncated that post before the bad entity.
• Two crashes in the importer’s own [TABLE] handler — a nil.split on [TR="bgcolor: ..."], and a negative string multiplication on a table with no data cells. Both upstream Discourse bugs.
• The BBCode gem silently deletes text. A malformed [IMG /IMG] makes it drop the entire enclosing quote body: 119 characters in, 61 out. It also strips the leading [ from markdown links, turning [£9.99](url) into £9.99](url).

The fix for that last one hid markdown links behind a sentinel while the gem ran. The sentinel was a raw 0x01 byte; Discourse’s markdown pipeline rewrote it to %between% and 46 posts went live with their content replaced. They were restored from the saved pre–repair text within minutes. The sentinel is now a private–use codepoint and the apply script refuses to write text containing one.

A passing control test bounds the failure modes you thought of, on the posts you sampled. Run the real thing over the real data, then check the output for your own scaffolding before writing a row.

Result

The second row is the subject of the two subsections below, and it did not arrive for another day — a reader had to report a post that was still broken before anyone looked.

Zero notifications for the same reason as the likes migration: update_all, never PostRevisor. One post was excluded by hand, because a moderator warning had been appended to it after the import — rebuilding it from the source would have deleted the warning.

A thread that had read Br for a day went back to Brütal Legend.

The safety gate was too crude

The apply step refused to write any rebuild that lost a word. That is the right instinct and it was measured at the time as “around 380 posts held back, mostly font tags”. That figure was inferred from a sample rather than audited, and it was wrong.

A reader reported a post the next day that was still truncated. It had been detected, its rebuild was correct and complete, and the gate had blocked the write — because the rebuild renamed Christopher Multsanti to Christopher_Multsant. Discourse truncates usernames at twenty characters, the token multsanti therefore disappeared, and the gate sacrificed 1,104 bytes of prose to protect a fragment of a username.

The real number was 1,095 posts, not 380. Classifying every one of them by why its token vanished said 1,052 were safe to write. A second check, deliberately built on a different principle — strip both texts down to prose and ask whether the live prose is still a subset of the rebuilt prose — found 43 the classifier had cleared, losing real words out of image filenames and alt text.

And then the rebuild turned out to be the worse copy

Inspecting the worst of those by hand produced the finding that mattered, and no amount of token analysis would ever have reached it:

LIVE     ![Norway+shooting+bodies+by+shoreline](upload://6Xwx....jpeg)
REBUILD  http://4.bp.blogspot.com/-HZ_9zgUVA-g/.../Norway%2Bshooting...jpg

After the import, Discourse’s PullHotlinkedImages job had walked the forum and rehosted external images locally. The rebuild predates that and still carries the original hotlink — to a host that died years ago. Writing it back would trade a working, self-hosted image for a dead link, and the post would get longer in the process. Every heuristic based on length scores that as a repair. It is data loss.

777 of the 1,095 were in exactly that state. The gate had been right about them, for a reason nobody had thought of when it was written.

The corrected script wrote 316 posts, not 289. The extra 27 had never been blocked by the prose gate at all — the first pass had skipped them for unrelated reasons and nobody had gone back to look. Together the 316 recovered 310 KB, and sent zero notifications.

The corrected gate asks two questions instead of one: does every word of live prose survive, and does every rehosted image survive? A post fails on either. Working images outrank recovered bytes.

A safety check that cannot distinguish a lost paragraph from a renamed user will quietly refuse real repairs, and report itself as working. The same check, loosened on a single wrong assumption, will quietly destroy images and report a byte count that says it helped.

I noticed that videos and image links in old threads rendered as plain blue URLs instead of players. The posts were intact; nothing had been lost. The problem was one step further along.

When Discourse cooks a post it does not resolve bare URLs immediately. It emits a placeholder — <a class="onebox"> — and leaves a background job to fetch the URL, look at what came back, and swap in an <img> or a <video>. During the import that job never ran.

The last row matters: images written as [IMG] tags were always fine. Only bare URLs, pasted on their own line, were left sitting in their placeholder state.

Why only imgur

Running the job across all 148,497 posts was measured before it was attempted, and the numbers argued against it:

all hosts : 2.85s per post, 39% hit rate
imgur     : 0.84s per post, 99% hit rate

The gap is dead hosts. One of them — a member’s own image host, gone for over a decade — accounts for 16,596 links. Its domain still resolves in DNS, so it cannot be filtered out cheaply; every request waits for a full connect timeout and then fails. Imgur answers in 0.05 seconds and 90% of its links are still live. So the job was pointed at imgur alone.

Then it ran for ten hours and did almost nothing

The first attempt converted 127 posts. Its log claimed 13,607. Three faults, only one of them in the job itself:

A Sidekiq process was leaking threads — 9,316 of them, against a container limit of 9,469, while reporting itself idle. Ruby resolves DNS by spawning a thread per lookup, so once that ceiling was reached every fetch failed. Whole batches returned 400 errors out of 400.

The other two were mine. The success counter was meaningless: it scored a post as converted whenever its HTML changed and contained an <img> tag — but every post contains one, because quote blocks embed the quoted user’s avatar. And the detector condemned healthy posts, because class="onebox" survives conversion: Discourse renders a converted image as <a class="onebox"><img></a>. Even after that was fixed it still failed, because a greedy \s* let the regex backtrack past its own negative lookahead and match anyway.

A counter that cannot report failure is not a check. This one ran for ten hours reporting success while the container could not open a socket.

The leak returned hours later on a fresh process id, 1,117 threads with five doing work. Killing it is documented as safe because “the supervisor respawns it” — and it did not: Discourse starts Sidekiq as a demon from inside a web worker, so the supervisor died with the thing it supervised, and nothing consumed a queue of 9,380 jobs. A reaper thread brought it back 79 seconds later, unprompted. Checking twice was worth more than the fix.

And then it took the site down

A 503, reported by the forum owner. The job ended every batch with a COUNT(*) over all 1.9M posts, filtered by a regex — purely to print how many were left. A regex predicate cannot use an index, so each call full-scanned the largest column in the database. They stacked up at 96 seconds apiece in wait=IO/DataFileRead and starved the web workers of disk. Load average 13.2; the front page took seven seconds.

Estimating between batches and measuring properly once an hour took load to 1.8 and the front page back to 50–260 ms. The expensive query was also wrong: it reported 19,361 posts remaining when the true figure was 3,967.

What it did

It ended by stopping itself: the last batch converted nothing and errored four times, tripping the guard against a batch that is mostly failures. Four posts remain, all pointing at images imgur no longer serves. The guard could not tell “everything failed” from “nothing left to try” — an ugly exit for a finished job.

Data present is not data visible. The database was right, the posts were right, and the pages were still wrong — for the third time in this migration.

Roughly 99,000 posts on other hosts keep their bare links. Those hosts are dead, and no amount of fetching will bring back what they were serving.

A separate defect of the same shape turned up while checking this one, and it is the subject of the next section.

Three defects were left after the forum had been live a week. None of them lost data. All three were the same shape: something stored correctly, rendered wrongly.

Every quote block pointed at the wrong computer

When you quote someone, Discourse embeds their avatar in the quote header. On 836,046 posts — 43% of the forum — that avatar pointed at //localhost:3000, the hostname of the machine that ran the import. In a visitor’s browser that address means their own computer, so the avatar was broken.

The address existed only in the cached HTML. Exactly one post had it in the source text, and that was Discourse’s own seed welcome topic. Re-cooking regenerates the HTML from the source using the current hostname, so the fix was a rebake of 829,000 posts — no edits, no revisions, no notifications.

Which took four attempts, because the job lied about its progress

The obvious way to rebake in bulk is rebake!(priority: :ultra_low), which queues the work rather than doing it. The script counted the queueing as success. It reported 499 posts fixed per batch, and the true number of broken posts did not move for two hours:

reported : fixed=499 per batch
actual   : 205,245 queued jobs against 102,896 broken posts
           40 of 40 sampled jobs pointed at posts already fixed

Sidekiq had fallen behind, so the loop re-selected the same posts every minute and queued them again. This is the second time in this migration that a counter measured the attempt instead of the result — the imgur job did the same thing in section 19. The fix is a one-word change to a synchronous rebake!, after which the count fell on the very first batch.

And underneath it, a thread leak in Discourse itself

Three times the job halted because the container ran out of process slots. Something was leaking threads — 3,377 of the 3,403 in the offending process shared one name, final-destination_resolver_thread. The cause is in Discourse’s DNS resolver:

sleep timeout
if !@result
  @thread.kill      # an asynchronous request, not a guarantee
  @thread.join
  @thread = nil     # orphaned; the next lookup spawns another

The thread is blocked inside Addrinfo.getaddrinfo, a C call that ignores Ruby’s interrupt until it returns. kill does not kill it. It is abandoned, and the next lookup creates a replacement. Every DNS timeout leaks a thread permanently.

The file has not changed since February 2023 and nothing in it acknowledges the problem. It is probably invisible on an ordinary forum; you need thousands of links to hosts that time out rather than refuse, precisely what a fifteen-year archive is made of.

Two of the three obstacles here were mine and one was upstream’s, and the upstream one was the easiest to find. Wrong code announces itself. Code that reports success while achieving nothing does not.

The tag the converter never knew about

A member reported a post rendering a broken link. Its source was one line:

[video]https://youtu.be/R4nFEQb7kZo[/video]

The importer converted [video=youtube;ID], vBulletin 4’s form, and the verification confirmed none were left. It had never heard of the bare form, which reached Discourse as literal text — and then the automatic link detector swallowed the closing tag into the address, producing ...R4nFEQb7kZo%5B/video%5D. A link to a page that cannot exist.

1,023 posts. The fix is to delete the tags and leave the bare URL, which Discourse expands into a player by itself. But 197 of those posts were people typing the word:

"[video] tag with full url anyways: it retains the option to go fullscreen"
"[video]yes, that song is indeed 35secs long"

A regular expression confident enough to fix the first group would eat the second group’s sentences. So the rule was narrowed to the unambiguous case — an opening tag, one URL, a closing tag, nothing else — and everything else was left alone. 826 rewritten, 210 skipped, no broken addresses left anywhere on the forum.

The item that measured itself out of existence

The third defect was 1,353 posts holding [email] tags. Checking what they actually rendered, rather than what they stored, ended the task:

Discourse converts the tag at display time, so the overwhelming majority were never broken. The eight inside code blocks are correct as they are — code is meant to be verbatim. That leaves six posts out of 1.9 million, whose repair would mean rewriting real email addresses into plain text and exposing several hundred of them to scrapers that currently meet only a mailto: link. The work was not worth doing, and knowing that took one query.

“Harmless” was an assumption

A health check a week later reported the forum clean, with one aside: twenty-eight background jobs sitting in Sidekiq’s retry queue, described as harmless. The forum’s owner read that and asked why it did not sound more like something stuck.

It was stuck. The jobs had been failing since three days earlier, their retry counters climbing past sixteen, and they could never succeed. The cause was a URL:

showthread.php?52-The-Babe-Thread%99-NSFW       %99 = ™  in cp1252
showthread.php?24228-(Germany-%FCber-alles      %FC = ü  in latin1

The same encoding that started this whole migration, reappearing in link addresses. Those percent-escapes decode to bytes that are not valid UTF-8, so the web framework throws while parsing the query string, the job dies, and the scheduler patiently books it in again. 140 posts were carrying one.

The posts themselves were fine — every one rendered with working links and previews. So the first instinct was to delete the doomed jobs and leave the URLs alone. That was wrong, and the owner said so: the jobs get re-created every time anything re-processes those posts. Deleting them resets a loop rather than ending it.

So the URLs were re-encoded instead — %99 to %E2%84%A2, %FC to %C3%BC — on 96 posts, restricted to links pointing back at the forum itself. The 49 pointing elsewhere were left as they are, because re-encoding a link to someone else’s server can break it if that server genuinely expects the old bytes, and those links were not what the job was choking on. Afterwards, all 96 were deliberately re-processed: none raised, and the retry queue did not move.

A number that was wrong three different ways

Scanning for those bad escapes turned up something else: the string %between%, which is not a URL at all. It was a marker the truncation repair used internally to protect links from the BBCode converter, and it had leaked into live posts — a defect recorded in section 18 as 46 posts, all repaired.

The scan said 79. Both figures were wrong, and neither was wrong because of the data:

WHERE raw LIKE '%\%between\%%'          -- 48,052 posts
WHERE position('%between%' in raw) > 0   -- 9 posts

In SQL, % is the wildcard. A pattern built to find a string that itself contains percent signs reads as “anything, then the word between, then anything” — and matches every post containing an ordinary English word. The real count was nine.

All nine were checked against the vBulletin source, which contains that string zero times. Seven of them turned out to be harmless in a way no amount of reasoning would have predicted: the marker had landed inside an empty [youtube][/youtube] tag. There had never been a video there. One of those posts, from 2011, reads “still dont work mate” about its own broken embed. Two had genuinely lost content and were restored from the source.

Eight were repaired. The ninth was left alone deliberately: its entire content was the dead video, and blanking somebody’s post is worse than leaving a link that goes nowhere.

Three counts of the same defect — 46, then 79, then 9 — and the data never changed. Only the query did.

Every hunt so far had the same shape: someone reports a broken post, the tag gets identified, a query counts the others, it gets fixed. That works, and it only ever finds tags somebody has already complained about.

After the fourth such report the question changed. Instead of “how many posts have this tag”: what BBCode-shaped text is visible to readers anywhere? The rendered HTML is what a person actually sees, so anything tag-shaped in it either failed to convert or is someone quoting markup on purpose.

People type square brackets in prose, so the results need filtering — but the filter needs no vocabulary list at all: a real tag has a matching closing tag in the same post.

One check, and fifteen years of jokes sorted themselves out of the results.

The quote tag was not the problem

The reported post had a quote nested inside another quote, and the inner one had reached the reader as literal text — so the converter evidently could not handle nesting. Except that it handles nesting fine; a clean nested example cooks correctly.

The actual trigger is whitespace. Discourse’s parser wants the tag alone on its line, and vBulletin never cared:

…and that was that.[/quote]     parser gives up, prints the tag
[quote]straight into text          same

That makes the repair a whitespace change — every tag onto its own line, nothing added or removed. A fix that cannot alter what a post says is a much easier fix to trust, and the check enforcing it is one line: strip all whitespace from both versions and require them to match.

That last row is deliberate: more opening tags than closing ones is not import damage, it is somebody mistyping in 2011. Supplying the missing closer invents structure, and guessing wrong swallows the rest of the post into a quote block.

The tweet tag

The same scan found 9,189 posts holding a tag nobody had reported — [tweet]801674224708427776[/tweet]. vBulletin had a plugin for it; the importer had never heard of it.

The reasonable assumption was that little could be done, since Twitter ended anonymous embedding and this forum has no API credentials. Checking rather than assuming produced a better answer: handed the equivalent URL, Discourse still renders the tweet in full — author, handle and text, no key required. 9,054 of the 9,189 convert; the rest hold something other than a numeric id and are left alone.

Four separate reports, four separate fixes, and the fifth question — asked backwards, from the reader’s side — found more than the four before it combined.

The reported post stayed broken, and the gate was right to skip it

Both fixes ran: 20,922 quote posts and 9,054 tweet posts, matching their dry runs exactly. Then the obvious check — look at the post that started this.

Still broken, and untouched. The write had been refused by the last gate, the one that asks whether the change actually improves the rendered output. The tempting response is to widen the fix until the reported post is covered. The better one is to ask why the gate said no.

The answer was a bug in the fix itself

Roughly 24,000 posts had resisted every classification. Taking one apart line by line found the cause, and it was not in the data:

closer:  [ 	]*\[/quote\]        → "
[/quote]"      leading side only
opener:  (\[quote…\])[ 	]*    → "[quote…]
"    trailing side only

A tag needs a line to itself, which means whitespace on both sides. The script only ever supplied one, so text glued after a closing tag survived and the post stayed broken.

Here is the part worth keeping. The bug cost coverage, not correctness. Every post it failed to repair was caught by the gate and skipped, rather than written back half-fixed. Nothing needed rolling back; all 20,922 first-pass writes were checked against their originals and every one was a strict improvement. A gate that refuses writes is an inconvenience right up until it is the only thing between a subtle bug and 20,000 posts.

The corrected version repaired 26,164 further posts — more than the first pass managed. Before writing, 400 random candidates were cooked and compared: 333 improved, 18 unchanged, and zero made worse, zero prose altered, zero rehosted images lost.

It also dissolved a sub-project. 729 posts whose quote tags straddled a Markdown blockquote had been scoped as a separate defect needing its own fix; the corrected script takes them to zero as well. They were never a distinct bug, just the same one from another angle — worth remembering before carving the next backlog into categories.

Two theories that were wrong

One broken post rendered as [quote=“Malcanis…”] and the smart-quote typographer is switched on, so that looked like the culprit. But a clean seven-deep stack cooks perfectly, curly quotes and all: the smart quotes are a symptom of a tag that already failed to parse. Nesting depth was the second theory; eight levels also cook perfectly.

The method that found the real cause was itself wrong on the first attempt. It cooked progressively longer prefixes of the post, which show literal tags — truncating a post cuts off its closing tags. Bisect by deleting lines from the whole, not by growing a fragment.

A third cause, and 656 posts left alone

The importer had also written attributes with doubled quote marks. The tidy instinct is to collapse all 1,412 of them; the control cook says otherwise.

[quote=""Bob""]             → renders fine
[quote=""CCP Zinfandel""]   → broken

Only names containing a space break the parser. So 730 posts were repaired and 656 deliberately left untouched — rewriting those would be an edit to somebody’s post with no visible benefit. “Fix all 1,412” was the tidy answer and the wrong one.

Of what remains, 3,785 are unbalanced — the 2011 typos — and two sit inside code fences where the tag is displayed on purpose. The honest floor is not zero. It is about four thousand posts that nobody should touch.

A postscript, in which a success is not one

The last task of the day was enabling geolocation: credentials in, rebuild, seven minutes of downtime, site back up. Then the verification. Everything said it worked — credentials loaded, nothing errored, and a lookup returned real data:

DiscourseIpInfo.get("8.8.8.8")
→ {asn: 15169, organization: "Google LLC"}

That is a correct answer to the wrong question. No country, no city, no coordinates — the entire point of the exercise. Two databases are needed and only one had downloaded, the other quietly skipped because a stale file from that morning was already on disk. One call fixed it. The credentials had never been wrong.

A partial success that answers queries is more deceptive than a clean failure, because every surface signal reads healthy. The check that catches it is not “did it return something?” but “did it return the field I actually came for?”

Years before the migration, a moderator deleted an entire subforum. Not a thread, not a weekend’s posts — all of it, along with anything on the filesystem that might have brought it back.

Five database dumps survived on the old server. Every one of them was taken after the deletion, so each held the same empty shell. That question was closed early and written down as closed: cannot be restored.

Then it was reopened by a machine nobody was thinking about. An earlier, abandoned attempt at moving to Discourse had left a virtual machine sitting on a shelf, and on it was a dump from 25 January 2024. It predated the deletion. The subforum was still in it, whole.

Checking before hoping

The dump arrived with a warning attached: it was probably exported with the double-encoding fault that had already ruined three other files. Three checks, in order — is the archive intact, is the content there, is it readable?

The warning was reasonable and the file was clean. The forum is an image board by habit, so almost everything in it is ASCII and a link; there was very little for the encoding fault to damage.

Putting people’s names back on their posts

An import that gets the text right and the authorship wrong is only half a restoration. Of 314 authors, 286 matched a live account by the id the original migration recorded. The obvious move is to sweep the other 28 into one anonymous bucket.

Looking at who they actually were killed that idea. Twenty-six were ordinary members whose accounts had simply lapsed in the intervening years. One of them, posting as A sick little pony, had written 109 posts in this subforum alone. Two were genuine spambots.

So the fallback got two more tiers: match on username as well as id (which recovered accounts that existed but carried no import id), and for the genuinely-missing, create one archive account each, carrying that person’s own name. The result:

Ninety-eight percent of the restored posts sit under the name of the person who wrote them.

Whose deletions to honour

387 posts were flagged hidden in the dump. It would be easy to read those as more of the same vandalism and restore them triumphantly — and wrong. They were eleven years of routine moderation by 37 different people: doubleposts, off-topic drift, a little spam, and 91 posts by a forum bot deleted with the reason “quack”, which was clearly a running joke.

None of that is the deletion this section is about. They were imported and then immediately soft-deleted: staff can see and restore any of them, members cannot. Nothing discarded, no moderator’s decision quietly reversed.

What three test threads caught

The import ran three times on single threads before it ran on all 154, and each rehearsal found something:

Reading the finished output found two more: [spoiler:1knvojzp], where a hash suffix from the forum’s phpBB era had survived two migrations and defeated a vBulletin-shaped pattern, and 200 posts rendering didn't instead of didn’t.

The four that got through

Four spam threads came in with the rest. The first attempt to find them keyed on accounts banned in vBulletin, which immediately flagged a member with 51 genuine posts who had been banned years after writing them. The signal that worked was narrower: no author at all (the spammer’s account had been purged, orphaning the post), a single-post thread, and pharma keywords. Together, decisive; separately, each one wrong.

They were deleted outright. Four posts in 23,796.

The answer had been written down as settled, and it was settled — for every copy anyone had thought to look at. What changed was not a better technique but a forgotten machine from an abandoned attempt two years earlier.

The import is finished and the repairs are done. What follows is what came out of it, and what is now running in production.

What the repairs actually moved

None of this needed a re-import — the stored ids made every one of these a matter of joining back to the old database and correcting what was found.

Every one of the 4,858 vBulletin accounts now exists in Discourse. 10,076 posts remain on the system account — the ones vBulletin recorded no name for at all, anonymous in the source data and equally anonymous now.

The last two rows of each table arrived after the forum was already live, which is why they have sections of their own. The smilies (section 15) and the reputation-to-likes conversion (section 17) were both done on a running site, days after go-live, and both were additive by design — that is what made them safe to do at all.

Old links still work

The importer writes a mapping of every old thread id to its new topic, but with URLs (thread/3) that nothing on the internet actually links to. This forum used vBulletin's SEO-friendly form, so the shapes that need to resolve are:

/showthread.php?3332-GANK-NIGHT-GN-31-Crowes-and-Bummers-II
/showthread.php?t=3332
/showthread.php?9095-R.I.P.-SHC&p=206497&viewfull=1

Discourse rewrites incoming URLs through a permalink_normalizations setting before looking them up, so one rule per shape is enough — with one catch worth recording, because it is not documented anywhere obvious. The setting is split on | and the boundary between pattern and replacement is found by counting unescaped /. A rule can therefore contain neither an alternation nor a slash in its replacement, which makes thread/ impossible to express. Renaming the stored permalinks to thread-3332 sidesteps it entirely.

All three shapes now return a 301 to the right topic, so fifteen years of links from elsewhere survive the move.

The forum has been live at failheap-challenge.com since 25 August 2026, on a four-core machine behind HAProxy behind Cloudflare. Three things went wrong on deployment day, each invisible until it bites.

The bootstrap failed on a plugin that no longer needs installing

The container build aborted forty minutes in, with git clone exiting 128:

fatal: destination path 'discourse-solved' already exists and is not an empty directory

That plugin now ships with Discourse core. The fix is a one-word guard — test -d <dir> || git clone … — which also means the build cannot break the same way when upstream absorbs the next plugin.

The database would not dump

Then pg_dump refused to finish, on two different tables:

ERROR:  missing chunk number 0 for toast value 567834 in pg_toast_28637

This is the same host crash from section 8, and it was worse than it had looked. A row-by-row walk of all 1.93 million posts read cleanly — every live row was fine. But REINDEX gave the real answer:

ERROR:  could not create unique index "posts_pkey"
DETAIL:  Key (id)=(157942) is duplicated.

The table held 42 duplicated ids — 1,927,151 physical rows where the primary key allowed 1,927,109 — and the index itself was corrupt, which is why index scans could return the same row twice and why every attempt to copy the table failed. Each duplicate had one readable copy and one whose content was unreachable. Keeping the readable copy, identified by physical row address, and rebuilding the index brought the count back to exactly what the primary key expected.

Everything a database dump does not contain

The restore brought 1.93 million posts across intact, and the forum still looked wrong three times over — each because something lives outside the database:

That last row is the general lesson. The development database had email switched off and registration open, both correct for development, and the restore carried them into production along with the posts. Settings are data too.

The expensive mistake here is not a bounced email. It is sending four thousand of them from a domain that has to deliver a password reset the following week.

One line in the draft claimed “your account is still there, under the same name”. Checking it before sending showed that 1,104 of 4,858 accounts — 22.7% — had been renamed, almost all spaces becoming underscores. More than one recipient in five would have read that sentence and found it false. It was replaced with an explanation of what changed and why their posts are still attached either way.

Bounces now feed back automatically: a Mailgun webhook tells Discourse when an address fails, and two hard bounces disable it. Those 62 dead addresses will retire themselves rather than being mailed forever.