Recovering a phpBB Forum From a Broken Backup

What was left

I previously went over how to deploy phpBB on Kubernetes following a rather critical hardware failure which took down not only my main virtual machine setup at the time, but also did a number on the backups I had too. This was covered in two parts previously — part one covering how I build phpBB into a suitable container, and part two which then went through the Kubernetes-side of the deployment, but both part of the wider recovery of the forum itself. However, what I did not cover specifically in either of those posts (although I did mention it) was how I went about recovering what I could of the old forum, from what little I was able to recover from the failed hardware. The scripts I used are published in a repository of their own, and they are all there to be read and copied. It is a companion to this post rather than a project, and it is not maintained.

So, to recover any forum, you ultimately have two sources of data which need to be recovered — there is the Web Root (the forum scripts, the avatars, the media album), and then there is also the Database (all of the users, forum posts and topics).

Out of both of these, the webroot backup (a complete snapshot from 2016 was all I could recover here) — the files the forum was served from — was intact, and if honest it turned out to be the least useful, because the forum's content lives in the database and not in the files. Admittedly, at least having all the avatars and media files is a win, it's not the most valuable side of the site — that bit comes from the data itself, the posts, the content and the guides and useful information which over the past 20 years, people have posted and shared.

So what about the Database backups then? On the same drive as the webroot backup, there was also a hot snapshot from the underlying database. The size of the files stored all looked good on a first glance, I had all the raw MySQL data files, so at the time it felt like a win.

I had also managed to recover what I thought was also a complete phpMyAdmin dump from 2013, but on closer inspection I discovered this was truncated. Not to worry, I have the 2016 snapshot right?

Wrong... Later on I discovered the 2016 snapshot was quite badly damaged in a way that took a while to understand, and it was properly damaged, not the sort you can simply repair and carry on from — so the forum went from being an operational problem to a recovery nightmare!

But as I work through this, there are a few surprises in here that I did not expect going in. The one that surprised me most is that a MySQL index can survive when the data it points at cannot — and that you can read some elements of data from it.

But what I did manage to achieve was the board still has 125,622 posts, 15,156 topics and 11,150 members. The old board had 157,243 posts, so that is 80% of what it was, and the loss is not spread evenly across its life. What that number is made of, and what is missing from it, is most of this post.

Triage before you load

Before anything was loaded into a database, the first thing to establish was how bad the backup was. A hot backup is a copy of the data directory, so it can be read on its own, without a database server and without any privileges.

It turned out to be worth writing a tool for, because it answers three questions about a backup before you trust it:

• Which databases and tables does it hold?
• How many rows does each table hold?
• How many of those rows can actually be read?

Of the three questions, the row count is the interesting one. It comes from the .MYI index file, which holds it at offset 32 as a big endian number. That was proved against the tables whose counts were already known, rather than assumed.

RECORD_COUNT_OFFSET = 32

Being the authority on the count, the index survives the damage to the data file, so it tells you what the table held before the failure — and the number of readable rows is what the repair finds, which is what the record chain can still reach. A table whose count is much larger than its readable rows has lost rows, and it does not need a database to tell you that.

It is also how the media album was ruled out. The album has its own database, and it is in no backup, so the question is whether some other backup holds it. The tool reads a data directory without loading it, which means a candidate backup can be checked while it is still on the disk.

The damage

The 2016 backup was taken from a live MySQL 5.x data directory, by copying the files while the database was running. On a healthy filesystem that is a reasonable thing to do, and the forum ran on it for years without trouble.

What I could not predict is how the backup came back the way it did. The drives I was able to recover what I could from had bad sectors on them following the failure, so the copy zeroed what it could not read. Other drives where there were backups stored were toast, so this is all I had to go off.

Three tables came out of it with holes in their data files. The pattern is the same in all three, and it is stark: a contiguous run of zero bytes, with live data on either side of it.

Read that first row again, because it sets the scale of the problem. The data file for the forum's posts is 60,997,188 bytes, and 58,720,256 of them are zeros. That run of zeros starts at the very beginning of the file.

Only 701,522 bytes of it are live, and 624,017 bytes of that is text, in 1,547 separate runs. Every one of those runs was checked against the board later on, and 19 of them were not on it. The rest of the board was in the 2013 dump instead.

In the members table the hole is in the middle of the file instead, from offset 454,656 to 3,600,384. That is where the record chain breaks, and it is why the rows on either side of it cannot be reached from the chain.

There was a real risk that I was looking at a broken extraction rather than a broken backup, so I checked that three ways. Three independent extractions produced byte-identical files. The extracted file was dense rather than sparse. The archive contained no sparse metadata and accounted for every byte it held.

The zeros were in the archive. They were in the backup. They were in the original. What I could not establish is how they got there, and that is a different question from the one I could answer.

One detail is worth knowing, because it makes the archive look far smaller than it is. MyISAM allocates the data file at full size for the row count the table was sized for. A table that held a lot of data has a large file whether or not the data is in it.

Which is why 58 MB of zeros compress down to almost nothing and the whole 2016 database backup is a 14 MB archive. The size of the archive is not a measure of what was in it.

Repairing the table

MySQL's own answer to a damaged MyISAM table is REPAIR TABLE, and the variant that matters here is the one that rebuilds the table definition from the .frm file as well:

REPAIR TABLE phpbb_users USE_FRM EXTENDED;

The USE_FRM is not optional. Plain REPAIR fails on these tables, because the definition it would read is damaged too.

Before trusting it, I proved it was not lossy. I made a synthetic table of 1,000 rows, zeroed a quarter of the middle of its data file, and ran the repair on it. It recovered 750 of the 1,000 rows, including rows on both sides of the hole. The repair is not throwing the good rows away.

It does, however, stop at the hole. That matters later, and it is worth understanding now. The repair walks the record chain from the start, and when the chain breaks it truncates the file there. The phpbb_log data file was 2,474,516 bytes before the repair and 266,200 bytes after it. The 320,020 bytes of live rows on the far side of the hole were thrown away by the repair itself.

Thousands of rows looked like they might be recoverable from the index, because the index on phpbb_log claims 17,198 rows and the merged board had 10,726. They cannot be, and the reason is not the hole. It is the repair. The rows were there until the repair ran, and the repair deleted them.

There is a way round it. Close the hole first by moving the live tail of the file up to where the hole began, so the two live regions become one contiguous file, and then repair. The script that does it is recover-tables-from-data-file.py, and it dumps the rows with SELECT ... INTO OUTFILE, one file per column, with every value hex encoded, because the text fields hold newlines and a line-based dump cannot be split on a separator.

Every row surviving in the 2016 data files, it turned out, was already in the merged board.

Nothing was newly recovered, and that was the finding rather than a failure. The gap is a hole, not a mapping problem. The board is missing 6,854 log ids that sit in one run, from 11,636 to 18,489 — the 2013 dump covers log ids 1 to 11,635, and the 2016 data file's live tail starts at 18,490. Everything between those two numbers was destroyed, and no index can bring back a value that was never written.

That is the first surprise, and it cuts against the obvious reading of the damage. It is also the moment that the interesting idea stopped working, and I want to be clear about why, because it is the part that took the longest to understand.

The members in the index

One of the three destroyed tables was phpbb_users, and the obvious reading was that all 13,971 member records had gone. The data file had a hole covering most of it, and the rows after the hole were unreachable from the broken record chain. Three separate methods of recovery all found the same 2,176 rows:

The extend check is the tool that is supposed to find every row in a damaged file, and it finds no more than the plain repair. The 2,058 of the closed repair are a subset of the 2,176, so closing the hole only loses rows. On the evidence of the data file alone, 9,493 members had gone.

They had not — and the reason is the most interesting thing in this post.

A MyISAM index stores the key value, not just a pointer to the row. The phpbb_users table has five indexes, and the one on username_clean still holds the username of every member who ever registered, including the members whose rows were destroyed. The .MYI file was not damaged. Its header records 13,971 rows, and MariaDB can scan the index of a table that is marked as crashed, so an index-only scan returns every one of them.

It works because of the layout. Each index holds one key segment, and the two that matter are:

PRIMARY key, on user_id:
    [3 bytes: the user_id]
    [6 bytes: the pointer to the row in the .MYD]

username_clean index:
    [1 byte:  the name length]
    [N bytes: the name]
    [3 bytes: a hash prefix]
    [3 bytes: the offset of the row in the .MYD]

Both segments hold the same row offsets, so joining them on the offset gives the name and the user_id of every member. The script is recover-users-from-index.py, and it reads the index file directly rather than going through the server, because the server will not serve a crashed table and the file will.

There are three parts to that, and none of them is obvious.

The names come from the server, but only from the index. The scan is a FORCE INDEX, which asks for the username_clean column and nothing else:

SELECT username_clean FROM corsab_forum.phpbb_users
FORCE INDEX (username_clean);

MariaDB can serve that from the index alone, even though the table is marked as crashed. The same query asking for a column from the data file crashes the server, which is how the boundary between the two was found.

The user ids come from reading the file. The PRIMARY index holds a run of nine byte records, three for the user id and six for the pointer to the row in the data file. A run of them is found by its shape, rather than by a fixed offset, because the index is not laid out in a predictable place:

STRIDE = PRIMARY_UID + PRIMARY_POINTER  # 3 + 6 = 9

A run is identified by its user ids ascending. A run of twenty records whose ids ascend, each one between 1 and 20,000, is a run of PRIMARY records. The script walks the run to its end and reads the row offset out of the last three bytes of each pointer.

The names are located by their own shape. The username segment holds the length as a single byte, then the name, then a three byte hash prefix, then the three byte row offset. So each name from the scan is searched for in the file as a length byte followed by the name, and the offset is what follows it:

needle = bytes([len(name)]) + name
at = myi.find(needle)

Joining the two sets of offsets then gives the answer, because a name and a user id that share an offset are the same member.

for offset, name in entries:
    user_id = primary.get(offset)
    if user_id is not None:
        pairs.append((user_id, name))

That join is the whole recovery. The rest of the script is bookkeeping.

There is an ordering constraint that is easy to get wrong. The recovery has to run before the repair, because the repair rebuilds the index from the data file and throws the lost index entries away. So the script copies the original .MYI files out first, and reads the users from the copy.

Member count goes from 2,176 to 10,364, and the number of post authors with a user row goes from 1,060 to 3,461 of 4,685.

And then, because a recovery you cannot check is a recovery you do not have, it was validated against two independent sources of truth. The 2,176 members whose rows survived, and the 537 post authors, who record the poster_id and the name together on the post itself:

Leaving errors behind are members whose name and row offset are ambiguous, because a short name such as corsa can appear in more than one index segment.

Where the finding stops

This is the part I want to be careful about, because it is the part where it is tempting to over-claim.

The index recovery works because the username_clean index holds the username, and the username is the one field that identifies a member to a reader. The phpbb_log and phpbb_privmsgs indexes hold only their own primary key, which is a number with no meaning on its own. There is nothing in those indexes to recover.

So the trick is not general. It is specific to a table with an index whose key is something a human would recognise.

That is also the honest shape of the whole recovery. The names came back. The rest of the member record did not, and it was never in the index. The join date lives in the row. The email address lives in the row. The avatar, the signature, the post count, the birthday: all of them live in the row, and the row went with the hole.

So 11,677 member rows are named, and 2,176 of them have a surviving row with the rest of their details — which is the same number the repair found in the data file. The other 9,501 have a name and a user_id and nothing else.

Joining the two sources

Complementary rather than overlapping is what the 2013 dump and the 2016 backup are. The post_id overlap between them is zero, which makes the merge simple and the arithmetic checkable.

Truncated rather than corrupt is what the 2013 dump is. It ends mid-statement inside the phpbb_sessions_keys insert, and phpMyAdmin exports tables alphabetically, so everything that sorts after that table is missing. It has no phpbb_users and no phpbb_topics. What it does have is complete: 124,200 posts, 11,794 private messages, 47 forums and 330 board settings.

Supplied by the 2016 backup are the users, the 2013-era topics that survived, the late posts, and the custom phpbb_garage_* and phpbb_ads_* tables from the old mods.

What the merge needed, and did not come for free, was a schema mapping, because the two sources disagree about two columns:

• phpbb_posts: the 2013 dump has post_approved, where 1 means approved. The 2016 backup has post_visibility plus three soft-delete columns, where 0 meant visible at the time. The other 26 columns are identical.
• phpbb_forums: the 2013 dump has the old forum_posts and forum_topics counters. The 2016 backup has the approved, unapproved and soft-deleted split. The counters are recomputed from the merged data.
• 48 tables have identical schemas and copy straight across.

That visibility mapping is the one that bit me hardest, and I have put it in because it is the sort of thing that costs you a day. phpBB 3.3 uses ITEM_APPROVED = 1 and ITEM_UNAPPROVED = 0. The 2016 schema had it the other way round, with post_visibility = 0 meaning a visible post. So every recovered post was marked unapproved, and no topic would open at all. The board looked fine and was completely unreadable.

Rebuilding the topics

About 12,000 topics had no row in phpbb_topics, because the 2013 dump was truncated before that table. A topic needs a row to exist, and a topic with no row cannot be opened.

They could be rebuilt, because phpBB stores the topic title in the first post's post_subject as well as in the topic row. The earliest post in a topic carries the title, so the topic can be put back from its own content.

That claim is checkable, because the topics that exist in both sources can be compared. 2,529 of 2,531 titles match exactly.

The schema migration

Recovered as phpBB 3.1.9, the board had to reach the 3.3.19 the container runs. phpBB's updater requires an exact version match, so the migration runs in two steps, the way phpBB publishes its update packages:

3.1.9   ->  3.1.12    phpBB-3.1.9_to_3.1.12.tar.bz2
3.1.12  ->  3.3.19    phpBB-3.1.12_to_3.3.19.tar.bz2

Both packages are downloaded from download.phpbb.com and verified against the SHA256 that phpBB publishes beside them.

There are three things about that migration that are not obvious, and all three cost time. The 3.1.x updater has no console form, so it has to be driven in a loop. The updater runs the migrations from its own code rather than from the update package, so the 3.1.9 code cannot finish the job on its own. And the 3.1.9 code cannot run on PHP 8 at all, so the vendor directory has to come from the image.

All three are written up in detail in the migration sequence in Part 2, because that is where they first had to be solved. The result is 330 migrations done and the version at 3.3.19.

Where the migration sits in the order is what matters here. It is the last step, not the first, and that is the opposite of what most people would do. A phpBB migration knows the shape of the schema, and it does not know the shape of the data. So the data has to be right before the migration runs, and there were still three problems it could not fix.

What the migration cannot fix

A schema migration knows the shape of the schema. It does not know the shape of the data, so it leaves behind problems that no amount of migrating will touch. Those went into a separate repair script, which is now 25 steps long.

Three of them were enough to make the board completely unusable, and all three were invisible until you tried to use the thing.

The orphaned bots. Nineteen rows in phpbb_bots pointed at a user_id that was never recovered. The bot cache reads those rows, and the session code treats a matching user agent as a bot. So a plain curl request looked up a user that did not exist, the session came back empty, and the forum rendered as a guest with no permissions. The board index showed no forums at all.

The nested set. The 2013 and 2016 forum trees were merged without rebuilding the left_id and right_id values, so four pairs of forums shared the same values. phpBB reads the tree with a nested set query, so a broken tree means no forums. The parent and child relations were intact, so phpBB's own nestedset class could rebuild the values.

The member birthdays. The recovered member rows carry the old phpBB "no birthday" marker, 0- 0- 0. phpBB 3.3 splits the value on the dash and reads the first part as the day, so every one of those rows raised a warning on every post that member wrote.

The posts the forum was moderating. The 2013 dump has 187 posts that the forum's moderators had not approved, and the 2016 backup has 1,421 more that the visibility convention had hidden. The decision was to publish them, so the recovered forum shows everything the board once showed.

Left of the repair is a long list of smaller faults. Each of them was found by looking for a page that did not work rather than by reading the code, and the most instructive one is not a fault in the recovery at all, but a fault in a loader.

The apostrophe

8,188 of the 11,669 members could not open a topic. The page failed with an SQL error, and the cause was that four columns of the member row held a single apostrophe instead of a letter:

phpBB reads the sort key from the member row and builds the sort order from it. A key it does not know gives an empty ORDER BY, and the page fails.

The loader caused it, and it is a good example of a bug that is invisible until you look at what a database API actually returns. The loader read the default value of a column from information_schema, which reports it as the SQL literal. The default of a one-character column therefore came back as the three characters 't', with the quotes included. The value was then quoted a second time, and the escaping left the column holding a literal apostrophe.

Two things made it worse. The first is that ' and the empty string look identical in a plain comparison, so the check that would have caught it had to use HEX(). The second is that a WHERE clause which matches nothing is silent.

The repair script matched the quoted default with HEX(), but the row held the unquoted one. The WHERE matched nothing, the script set the value on every row, and the value it set was the broken one. The check was the thing that was broken.

The same fault produced the wrong date format, which is a good illustration of how a small thing can look like a much bigger one. The format was D M Y H:i, and it has no day of the month in it, because D is the day of the week and there is no lower case d:

Thu Nov 2004 16:32      what the members saw
Thu Nov 24 2004 16:32   what they should have seen

Every member whose date had been inferred was rendered with no day of the month, which reads like a bug in the inference. It was not. The inference was right and the format was wrong.

The join dates

The index gave back every username. It could not give back the join date, because user_regdate lives in the row, and the row went with the hole.

So 9,465 members had no join date, and the recovery gave them the board's start date, so the member list showed 19 October 2004 for every one of them.

What the board still knew was the evidence of what each member did, and the earliest of that is as close as the board can come to their join date:

That is 3,983 members. The important property is that this evidence is never earlier than the real join date, because you cannot post before you join. It can only be later, and how much later depends on how soon after joining somebody says something.

That is judged by leaving known answers out. Take a member with a real date out, predict it from the evidence, and compare:

The median error is 0 days and the 90th percentile is 37 days. The largest is 2,327 days, for a member who joined in 2004 and first posted in 2010.

The remaining 5,482 members have no evidence at all. They never posted, never wrote a private message, and never did anything the log records. Their row holds nothing but their name.

But phpBB hands out the user_id in registration order. A member's id places them between the members around them in time, so their join date can be interpolated between the nearest dated members either side of their id. The same leave-one-out test judges it:

The result is 11,644 distinct join dates for 11,677 members, and the member list reads as a real membership history again.

These are inferences, not recoveries, and the distinction matters. The scripts say so in their own header, and they write every change to phpbb_user_regdate_backup, so the whole thing can be undone with one statement:

UPDATE phpbb_users u
  JOIN phpbb_user_regdate_backup b ON b.user_id = u.user_id
   SET u.user_regdate = b.old_regdate;

The recovered facade

A forum is not only its posts. Some of what a reader sees is in the database but not in the content, and it all went or all came back wrong.

Some of the names were lost altogether. The index recovery could not pair a name with a user id for 1,266 members, so they are called "Former member 1234", where the number is their user id. The id is real, because the content refers to it, but the name is not in the index.

The name is in the forum's own caches, which store the name of the poster next to their id. The topic rows hold the creator of a topic and the last poster. The forum rows hold the last poster of a forum. The post rows hold the author of a post. The moderator cache holds every moderator. The 2013 database holds those names for 2013, and the snapshots taken before the repair overwrote them hold the 2016 names.

32 members were named. A name is only used when every source agrees on it and no other member already has it, so a member is never renamed onto another member's name. The other 1,234 are not in any of the sources.

The names lost their capital letters. The index recovery read the names from username_clean, and that column holds the lowercase name by definition in phpBB. So the 10,103 members whose row was destroyed were given a lowercase name, where the forum had the name the member chose.

The original case is in the forum's own caches, which write the name as it was. The topic rows hold the creator of a topic and the last poster. The forum rows hold the last poster of a forum. The post rows hold the author of a post. The moderator cache holds every moderator. 116 members got their case back, and the ones that came back include names with a capital in the middle of them and names that are entirely in capitals, which is what the forum had and the index had not.

A name is only used when it matches the member's current name, ignoring the case, so a member is never renamed onto another member's name.

The avatars came back. The web backup holds the avatar store: 779 files for 390 members, named the way phpBB names them, with the salt from the board's own config. The members whose row was destroyed lost the avatar with it, so the file was in the backup and the row did not point at it. 379 members got their avatar back. The width and the height are read from the file, so it is shown at the size it was uploaded.

The profile fields survived. The phpbb_profile_fields_data table was not one of the destroyed tables. It holds 12,108 rows of the custom fields the forum had: the postcode, the location, the occupation, the website, the interests and the car owner.

It is keyed by the original user id, so it joins to the members whose row was destroyed. 4,403 of them have a profile value, and the board shows it under their name.

The ban list survived too. It holds 374 rows, of which 173 are permanent bans of a member. The oldest of them was applied in April 2012 and has no end date, and it still holds.

The staff did not look like staff. The administrators and the moderators were in the right groups, but they read as ordinary members under their own name, for three reasons.

phpBB shows a member's rank and colour from the group named in phpbb_users.group_id, and the 3.3 migration reset every default group to REGISTERED. The administrators lost the ADMINISTRATORS default that way. The members whose row was destroyed had their rank and colour rebuilt from the index, which holds the name and the id and nothing else. They were left at zero and black. And the moderator cache, which phpBB only fills when a permission changes, was empty, so a forum that lost its moderators kept them missing.

phpBB's own phpbb_cache_moderators function rebuilds that cache. The default group is now set to the staff group that carries the rank. Where a member belongs to more than one staff group, the group with the highest rank wins, so an administrator who also moderates is shown as an administrator.

And the administration panel was gone. This one took a while to find. The panel showed four tabs and gave a 503 on most of the pages behind them. Neither the container nor the cache was the cause.

phpBB grants administration rights in two ways. The group can hold a_* permissions, and a member with user_type = 3 is the founder, which phpBB gives every a_* permission at login whatever the group says. The recovered board had no founder, and the administrators group held seven of the forty-five permissions, so the rest of the panel was locked away.

A module the administrator cannot reach is dropped from the menu, and its parent category is dropped with it, so three sub-categories took their parent with them. And fifteen module rows belonged to extensions that are not installed, with left_id and right_id values that collide, so phpBB's nested set query could not read the tree. The dead rows go, and the tree is rebuilt from the parent and child relations, which were intact. The tree goes from 158 rows to 143, which is the stock count. The tabs go from 4 to 8, and the panel pages that fail go from 17 of 23 to 0 of 1,333.

The board today

The last of the repairs was a decision rather than a fault. Every forum is locked. The lock is forum_status, and it is what the administration panel sets. A locked forum is read only for everybody without the m_lock permission. That is the administrators and the moderators, so the staff can still post. It locks the 39 forums that hold the threads, and leaves the category rows and the two link rows alone, because they hold no posts.

The reason for the read-only state is worth saying plainly, because it is not a technical decision. The visitor numbers were already falling, and the Corsa B is now a rare car. There is no point opening a board for posting when there is nobody posting on it. I might open it up again in future however, but for now it is simply being left as an archive of information.

Where the board stands:

The posts by year show what the loss actually cost, because the loss is not spread evenly across the forum's life:

2004:    268             2011: 17,307
2005: 22,324             2012:  7,617
2006: 11,473             2013:  2,590
2007:  2,985             2014:      0 <- lost
2008:  7,549             2015:    788
2009: 28,358 <- peak     2016:    633
2010: 23,646             2017+:     0 <- lost

The golden years are almost entirely intact. The 2013 dump holds 124,200 posts, and 124,117 of them are on the board today, which is 99.9% of everything written before the gap. The gap is the quietest part of the forum's life, which is the best place for it to be, and also anything from 2017 to present is completely lost.

There are zero posts with a post_id between 146,004 and 175,653.

That is 29,650 post ids with nothing in them. 124,117 posts survive below the gap and 1,422 survive above it, so the 28,228 that are missing are posts that are not on the board and cannot be brought back. Whether they were on the backup at all is not something I can answer.

What is still missing

Every remaining gap was investigated to see whether it could be closed, and the result was that all of them are destroyed rather than merely lost. The tests are recorded, and they are part of the pipeline, so every gap is checked on each run rather than taken on trust.

• The details of most member records. 11,677 members are named, but only 2,176 of them have a surviving row with the rest of their details. The email address, the avatar, the signature and the post count are unknown for the other 9,501. The email address is in the file, in the text that is left, and the hole covers it.
• The join date of 9,465 members. It has since been inferred, and the section above explains how. It is an estimate, not a recovery.
• The 2013 to 2016 window. 28,228 posts, in one run of 29,650 post ids.
• 6,854 log entries from 11,636 to 18,489, and 398 private messages from 14,154 to 14,551. These sit in the hole in the 2016 data files.
• All the attachments. The files/ directory holds only .htaccess and index.htm, and the phpbb_attachments table has zero rows in both databases.
• The names of 1,234 members. Their id is known and their posts are on the board, but their name is not in any of the sources.

One of these deserves a note, because it is the one case where the temptation is to fabricate rather than to lose.

The media album

The webroot carried a separate application next to the forum: a media album with its own PHP files and, more importantly, its own database. That database is in no backup. It is not in the 2016 data backup, which holds only the forum. It is not in the 2013 dump. It is not in the web backup.

So the title, the author, the category, the upload date and the ordering of every image are gone. The images themselves are mostly fine. All 3,441 of the uploads are images, and 2,477 of the 2,479 thumbnails are; the other two thumbnails are empty files, and the upload that goes with each of them is intact. Thirty-seven of the uploads are truncated, so they are missing the end of the file, but they still render, because the truncation took the end rather than the picture. They were checked by hand, and they are photographs of Corsa B cars. They also have no EXIF, because the album re-encoded them with GD when they were uploaded, so the camera and the date are not in them either.

The album is parked rather than published, and that is a decision rather than an oversight. The images can be served, but they have no title, no author and no album to belong to. A gallery has to either invent that structure or show them as an anonymous set, and inventing titles would be fabricating history.

There may be a separate hot backup of that database somewhere. There is a tool that checks one without needing a database server. It reads a raw MySQL data directory directly, and it reports the databases and tables it finds, the number of rows each one holds according to its index, and the size of the hole in each one.

The row count is at offset 32 of the .MYI as a big-endian number, which was proved against the tables whose counts are known.

The missing topics

One more piece of the forum survived in a way that was not obvious. The damage took the posts out of 1,162 topics and left the topic rows. The repair dropped them, because a topic without a post cannot be opened and was a dead link.

There was a second kind of dead link. 146 topics held only unapproved posts, because the 2016 backup used the opposite visibility convention, and those posts were hidden rather than missing. They were published, along with the 187 the moderators had not approved in 2013, so the board shows everything it once showed.

Their titles are not lost. They are in the recovered 2016 topics, and they are the record of what the forum was about. 430 of them are in General Discussion, 280 in Corsa General Help, 107 in Items Wanted, 89 in Items For Sale and 50 in Project Logs. The rest are spread over the other forums.

They are written to a table of their own, phpbb_recovered_topic_titles, which phpBB does not read. The rows are not put back into phpbb_topics, because a topic without a post is still a dead link, and the forum would show them again. They are also written to a file that is tracked in the repository, so the record is not only in the database.

What the data looks like

The counts are one thing, and the shape of the recovered rows is another. This is what came back, with everything that identifies a person taken out.

A member row holds 69 columns, and a recovered one is mostly empty. The counts below are the whole board:

The join date row is the one to look at. Every member has one, and almost all of them are the day that member registered.

That row is the result of the inference in the section above. It is the difference between a member list that reads as a real membership history and one that shows the same date nine thousand times.

The avatars are the other one. 379 members got theirs back, which is why 379 rows have one.

The usernames fell into four groups, and the shape of them says what happened.

The 1,027 with spaces are the names that were recovered from the index. The index holds the lowercase form, so a name the member chose in two words came back as one lowercase run.

The case was put back for 116 of them. The rest keep the lowercase name, because their case is in none of the sources.

The profile fields came back further than the member rows did, because their table was not one of the destroyed ones:

The posts and the private messages are in better shape than the member rows. Neither of those tables was one of the three the damage took.

The last post is the notice about the recovery. The last private message is from July 2016, which is where the 2016 backup stops.

And the join dates, which are the clearest single piece of evidence that the inference worked. The earliest is 8 November 2004, and exactly one member holds it. The first post is three days later.

year      members   distinct dates
2004           45              45
2005          950             950
2006          872             872
2007          505             505
2008         1102            1102
2009         2554            2554
2010         2129            2129
2011         1190            1190
2012          536             536
2013          715             715
2014          529             529
2015          251             251
2016          156             156
2017          107             107

Every member in every year has their own date, with one exception. The thirty-six members from 2026 are the ones who joined after the forum came back, and the thirty-six share three dates, because that is when they registered.

That is what a member list is supposed to look like, and it is worth remembering that none of those dates was recovered. They were inferred, from a first post, a first private message and a first log entry, and then from the order the ids were handed out in.

The schema parity

The last piece of work was the least dramatic and probably the most valuable.

The recovered board came out of the 2016 backup, so it had the 3.1 schema, the MyISAM engine, and the columns of the extensions the old board had. It was compared against a real stock phpBB 3.3.19 install, made with the same installer image, so the comparison is against what phpBB actually creates rather than against a reading of the schema file. The differences were then closed.

The engine is the one that matters. Every table is on InnoDB now. MyISAM has no crash recovery, so a table that is being written to when the machine goes down can be left with a hole in it. That is exactly what happened. InnoDB would not have stopped the hardware failing, but it would have stopped the forum being left with a hole in it. The conversion took about two seconds.

Four indexes were missing and two were wrong. phpbb_posts.post_username was the important one, because the board shows 21,064 posts under a placeholder name and search.php looks posts up by that name. An exact author search went from 87 ms to 0.27 ms.

Twenty-six tables had the wrong collation or the wrong row format, and 38 columns had the wrong type, nullability or default. They were brought to what phpBB creates.

The plan was proved first, on a complete copy of the board, served through nginx and driven like a real forum. Only then was it applied to the live one.

Three things were kept on purpose. The three custom profile field columns, because the fields are active and phpBB reads the columns by name. The nine AutoGroups columns, because the extension is going to be used again and the configuration is the record of which 274 memberships were placed automatically. And the recovered topic titles table, because it is the only record of the dropped topics.

The pipeline

The whole recovery is nine scripts, and they run in a fixed order. Every one of them is safe to run again, so the pipeline is reproducible from nothing, which matters more than it sounds like it should. A recovery you cannot repeat is a recovery you cannot check, and it is a recovery you cannot retry when the third script turns out to have been wrong.

# 1. Recover the 2016 raw data directory, repair it, and dump it to SQL.
scripts/recover-forum-db.sh

# 2. Recreate the tables that could not be recovered.
scripts/generate-missing-tables.py > backup_recover/recovered/missing-tables.sql

# 3. Load the recovered data into the local MariaDB.
scripts/load-recovered-db.sh

# 4. Merge the 2013 dump into it, rebuilding the missing topics and
#    loading the members recovered from the index.
scripts/merge-forum-data.sh

# 5. Snapshot the result (writes a checksummed .sql).
scripts/snapshot-recovered-db.sh <label>

# 6. Migrate the schema from 3.1.9 to the 3.3.19 the container runs.
scripts/migrate-forum-schema.sh

# 7. Repair the data that the schema migration cannot fix.
scripts/repair-recovered-forum.sh

# 8. Snapshot the migrated result.
scripts/snapshot-recovered-db.sh migrated

# 9. Write a deployment bundle for the cluster.
scripts/export-for-deployment.sh <label>

Two of those deserve a note. Step 4 is the merge, and it is where the 2013 dump and the 2016 recovery are joined, which is the point at which the schema mapping has to be right. Step 7 is the repair, and it is 25 steps long, because each fault was found by looking for a page that did not work rather than by reading the code.

Step 9 writes the bundle for the cluster, and it is not just a dump. The recovered database points at the local stack, because that is where the recovery was done, so the bundle rewrites the server settings to the production host. The rewrite is at the end of the dump, after the table data, so it is the last word on the values.

The bundle is not tracked by git. It holds the password hash of every member, and it is the only copy of the accounts.

All nine are published, so the ones described here can be read in full and copied, in a repository of their own. It is a companion to this post rather than a project. There is no issue tracker, no support and no release cadence, and the scripts were written once, for one board. Everything specific to that board has been generalised, and the rest is exactly as it ran.

What is left to do

The board is on the cluster now, running in a container. The deployment is covered in Running phpBB on Kubernetes: Containerising a Legacy PHP Forum (Part 1) and Running phpBB on Kubernetes: Containerising a Legacy PHP Forum (Part 2) — that work assumed a board to serve, and making one was the other half of the job.

The pieces that are still open are the ones that need a decision rather than a fix:

• The media album. It needs either a hot backup of its database or a decision to publish it without one.
• The 1,234 names. They are not in any source. They could be reconstructed as a guess, and the guess would be wrong for some of them.
• The join dates. They are inferences, and they are written to a backup table so they can be taken back out. Whether to leave them is a question about what a member list is for.
• The extensions. The record of what the old board had was removed from the database and kept outside it. The Tapatalk push records, the Garage data and the Ads rows are all in files, and none of them are coming back into the forum.
• A second copy of the backups. This is the one that matters most and it is the one that is not done. Everything is on one laptop. The backup_recover/ directory is in .gitignore, so none of the 1.6 GB is in the repository or on the remote, and the three source archives have no copy anywhere else. A loss of that machine is a loss of the forum.

That last one is the lesson I would carry forward, and it is not a technical one. The recovery worked, and it worked because there were three partial backups that overlapped in useful ways. There was one complete backup, the webroot, and it had nothing to do with the database. The database had a hot copy that turned out to be damaged, and a truncated dump that turned out to be the more useful of the two.

Nobody decides to keep one copy of a thing and think about it. You either have backups that are somewhere else, or you have a hope.

References

• The New Stack: RKE2, Ghost, and Finding Balance — the failure, and the replatform around it.
• Running phpBB on Kubernetes: Containerising a Legacy PHP Forum (Part 1) — the container image, the version problem, and the storage.
• Running phpBB on Kubernetes: Containerising a Legacy PHP Forum (Part 2) — the performance measurement, and the deployment.
• The recovered board — the forum itself, locked and read only.
• The board, in April 2026 — the old board, from the Wayback Machine. This is where the original counts come from.