ShoutWiki Hub talk:2021 Data Recovery

ShoutWiki — express yourself and be heard!
Jump to navigation Jump to search

This page is for any page that has missing/broken revision data from approximately October 2020. This will show as a page with history and correct byte size on the history page, but, will render completely blank.

Known "Broken" Pages[edit source]

  • User talk:--miki-- - unrecoverable at this time.
    • It didn't have any relevant content anyway, only {{Talkheader}}. --miki-- (talk) 00:21, 12 April 2021 (UTC)
  • [[Lcawte Test Wiki]] – no content shown
  • [[User talk:Jakeira]]

From Forum:I found more broken pages[edit source]

So these are indeed broken, and recovering them is pretty much literally like looking for a needle in a rather large haystack. I tried looking up the pages on the Internet Archive, but the only one archived there is Forum:I don't seem to be an admin on my new wiki and it's from a couple weeks ago, i.e. broken.
I tried with Forum:I don't seem to be an admin on my new wiki as I figured that maybe the author had properly signed their message; given that their only edit(s) seems to be to that Forum: page, I figured there wouldn't be too many false positives or anything. Alas, it seems that they didn't sign their post and some other phrases I figured they might have used came up with nothing.
User:Normal Lcawte is User:Lcawte's non-privileged testing account. I'm pretty sure that's what the page said prior to this issue, and I'm sure he can just recreate it if he so wishes.
The other two user pages seem to be super short, User:Provaagriwiki's content might've been as simple as "Test." and no idea about the other, it was/is even shorter (two bytes).
tl,dr: Not feasible right now unfortunately. It's like looking for a needle in a haystack, but unlike previously, we have no idea about the needle, none whatsoever: what's it look like, how big, how sharp? And so on. Sorry. --Jack PhoenixShoutWiki Staff (Contact) 14:02, 21 May 2022 (UTC)

Previously broken but now fixed[edit source]

The solution[edit source]

This was such a fun bug to fix. Here's how I did it...

  1. First I queried the page table for information about the affected page; just the page_latest field (which points to the rev_id field in the revision table, would've sufficed): SELECT * FROM page WHERE page_title='List_of_wikis';
  2. Then I queried the revision table using the page_latest value returned by the previous query: SELECT * FROM revision WHERE rev_id = 31636; — again, all I needed was really the value of one field, rev_sha1 (this assumes that the field in question is populated, as it has been for a long time for all even remotely recent MediaWiki installations, but for whatever reasons some legacy wikis may have entries where rev_sha1 is NULL)
  3. Using the SHA-1 hash returned by the previous query, I'd then continue to the content table: SELECT * FROM content WHERE content_sha1='7lwbujsg1877xvp3pkerar9mbl47gg4';
  4. For this particular page (List of wikis), the content_address was set to tt:159269. tt stands for "text table". I already had a hunch as to what its value in the text table would be, but let's verify anyway: SELECT * FROM text WHERE old_id=159269;. As I suspected, the DB server told me that there was nothing in the text table for that ID. Now for the fun part, which is digging up the correct old_id from that same table!
  5. I went to the Internet Archive and used the super handy Wayback Machine to see if they had any archived versions of this page; they did, luckily. So I chose one of the more recent versions to see the page text.
  6. Then I picked a partial sentence from the page and used a highly inefficient (but acceptable for this single-use use case) LIKE SQL query to scan the text table for entries containing those words: SELECT old_id FROM text WHERE old_text LIKE '%to find all the wikis%'; — in doing this, I did assume (correctly, if I may note) that there wouldn't be too many pages where those words would be found in that precise order. I was right. The SQL query returned a bunch of revision IDs, as expected; 18, to be precise, but only two in the known-to-be-affected range, 157XXX. I took a quick look at the latest one to see if it was about right: SELECT old_text FROM text WHERE old_id = 157306;
  7. As expected, the query returned page text for what was indeed the List of wikis page. Yay! At this point we know everything we need to in order to hopefully make the page accessible & usable again.
  8. Now I just had to tell the content table to use the correct text table entry ID for loading the content: UPDATE content SET content_address='tt:157306' WHERE content_sha1='7lwbujsg1877xvp3pkerar9mbl47gg4'; (I'm aware that SHA-1 is not considered a cryptographically secure hashing algorithm, it's sufficient for ensuring data integrity, which is what MediaWiki does with all these SHA-1 hashes found in various database tables; obviously MediaWiki does not use SHA-1 for cryptographic security.)
  9. Then I loaded the actual page (List of wikis) in my browser to see...nothing? Weird.
  10. Perhaps the cache needs a bit of tender loving care in the form of a ?action=purge? Appended that to the URL, clicked "OK" and voilà, the page became viewable!

This also basically explains why [[User talk:Jakeira]] and [[Lcawte Test Wiki]] are still "gone": despite my best efforts, I've not been able to locate the relevant entries from the text table (yet), and that table does not store any SHA-1 hashes, so while I'm easily able to query the page table for the current (latest) revision SHA-1 hash, that information won't help me to locate the "missing" data (remember that content.content_address points to an incorrect entry in the text table!). --Jack PhoenixShoutWiki Staff (Contact) 12:36, 20 April 2021 (UTC)

Maybe the text simply is missing from database? For those pages restored, the text appears to be from the previous revision instead of current. --miki-- (talk) 16:37, 20 April 2021 (UTC)
Could well be, unfortunately. It goes without saying that this level of database corruption should never ever happen, yet...it did. So if some data loss occurred, I'm not shocked, but at least it appears to have been relatively contained and somewhat manageable. Huge kudos to Lewis for dealing with the hard portion of the data recovery process! --Jack PhoenixShoutWiki Staff (Contact) 22:19, 20 April 2021 (UTC)
FYI, you don't have to click OK on action=purge on Hub. Just wait for the page to load. That's been true since 2016. A couple questions:
  1. Revision histories are missing their summaries. Is it possible to recover those?
  2. CreateWiki was blamed for the database corruption. If true, why was CreateWiki (i.e., what SQL was it using) screwing with the hashes of so many pages, etc?
--Saftzie (talk) 17:23, 20 April 2021 (UTC)
I'm somewhat impatient and I wasn't logged in at the time. ;-)
  1. Potentially. Haven't yet investigated. The most likely cause for this is that one or both of the identifiers in the revision_comment_temp table are incorrect.
  2. Yes and no. Based on all the evidence we have, it seems likely that CreateWiki was indeed behind this incident, though I would put the blame on all those lovely little subtly (and sometimes not-so-subtly) breaking changes included in a major MediaWiki upgrade.
    CreateWiki's logic and basic idea behind the special page has remained unchanged since day 1: it provides a special page form to allow a (privileged) user to create a new wiki on the farm. The major conceptual thing to understand here is that we operate — not necessarily simultaneously — across two databases, Hub's (as Hub is the only wiki with CreateWiki installed) and the new wiki's.
    Relatively recently, circa MW 1.33 if memory serves me correctly, the way how cross-database operations are (not) supported in MediaWiki changed as a result of intentional core changes. For us, this broke things like ProblemReports, and, of course, CreateWiki. Specifically this issue is related to the post-starter import cleanup CreateWiki does. This cleanup operation touches tables like revision, text, comment, revision_comment_temp, revision_actor_temp and image. The relevant SQL queries were meant to run in the context of the newly-created wiki and that had remained true all the way up until MediaWiki 1.34 happened. Then they suddenly started to be executed in the context of this wiki, Hub.
    Of course at times like these, questions like "why did we even write CreateWiki as a proper MediaWiki extension that uses all of MediaWiki's APIs the way it should?" come to mind. These kind of issues are also the reason why major MediaWiki version upgrades are becoming more and more painful with every new release: if and when you don't have the Wikimedia Foundation's resources to throw into a problem, things suddenly get a lot more complicated. --Jack PhoenixShoutWiki Staff (Contact) 22:19, 20 April 2021 (UTC)

New Reports[edit source]

None currently. Click here to report a broken page

Retrieved from "https://www.shoutwiki.com/w/index.php?title=ShoutWiki_Hub_talk:2021_Data_Recovery&oldid=32487"