Topic on Project:Support desk/Flow

So my wiki has gone through a few decades of upgrades and I would consider its database surely rife for optimization. I noticed this as I am unable to upgrade to the latest LTS, so now I am trying to isolate and fix as many problems with the database as I can.

One is shoddy files and other corruptions. Most maintenance scripts don't seem to really fix this.

One problem I have is similar to this one https://stackoverflow.com/questions/34143056/main-page-error-of-mediawiki

I have a file that displays properly but has no 0 revision registered for some reason and I cannot edit it, either. None of the "fixes" I can find on here and elsewhere on the web really help. I appreciate any pointers to maintenance scripts, hacks and other tips to really (really) fix flawed database structures etc...

Can you post the entry in the page table for that page, along with anything in the revision table where rev_page is equal to the page_id for that page?

What version of mediawiki are you using?

Common causes of this are referential integrity errors, either with the page_latest field in page, the rev_comment_id field in revision or rev_actor field in revision (the last being most common as the actor migration upgrade script is fragile)

First of all thanks for your interest in helping me. Ok let's see (would love a maintenance script that just goes through my entire database and either fixes everything or gives me a very detailed report what needs fixing).

"Entry in the page table for that page":

page_namespace 6 pagetitle 4A61636B70616C616E63652E6A7067 is_redirect 0 is_new 1 page_touched 20230118090223 page_latest 5734 page_len page_links_updated 3230313530343032313635383433

"anything in the revision table where rev_page is equal to page_id"

rev_id 5734 rev_page 2524 rev_commend_id 0 rev_actor 0 rev_timestamp 20060208174920 rev_minor_edit 0 rev_deleted 0 rev_len 0 rev_parent_id 0 rev_sha1 70686F6961633968346D383432787134357370377336753231657465657131 (this is not unique, I see a few adjacent revisions that have the same hash, but I could tink of reasons why that may be so, but not sure if it's supposed to be non-unique)

Mediawiki 1.38.2

I may have used delete old revisions many many years ago. And one of the things I have tried very recently is the migrateactors php script which helps in assiging content that has no actor assigned

So the rev_actor being 0 and rev_comment_id being 0 indicate that update.php didnt do the migration properly.

The actor migration script generally doesnt work anymore. Its best to run it around 1.33. If you dont have backups to that point you would probably have to manually fix it by updating rev_actor to point to some actor.

what do I point it to? this all means very little to me not being familiar with the product's sql architecture. Going back to 1.33 is not an option of course....

(wondering if this - horribly as usual documented - maintenance script might help? Manual:FindMissingActors.php )

Bawolff, I have a very concrete question, maybe you can point me in the right direction: from all I can gather, once restoring to a pre 1.35 backup state is off the table, there is no real way to "fix" this. However, if it only affects a manageable number of pages/files, I wonder if the following could be achieved: is there a way to manually remove all traces of these pages/files from the database, so that these become "nonexistent" again and can be re-created or re-uploaded (in the case of the files) thereby creating new/fresh and correct database entries for these content items? That would be a manual way of fixing this in order to have the next upgrade to 1.39.2 not throw any errors :)

You could delete the entry in the page table i suppose, ymmv.

hm, can't even find it. Nothing in the database seems to be in plain text, as far as I understand it it is all hashed, but can't find one of the files in question even by looking for their hashed value... so weird

They aren't hashed. Some db tools will display page titles in hexadecimal form

How weird, but indeed the sql query the db tool shows on top of the screen when opening the pages table says "

SELECT *, HEX(`page_title`) AS `page_title`, HEX(`page_content_model`) AS `page_content_model`, HEX(`page_links_updated`) AS `page_links_updated`, HEX(`page_lang`) AS `page_lang`   FROM `swdb_page`LIMIT 50

talking to my hoster now why that is the database wide default, so weird

it seems to be default behavior for "Adminer" to show as Hex if the column type is binary. Now of course it sucks the hoster uses Adminer, but before I go into further trouble I just want to make sure and ask this: binary is the correct format for the table column(s), right?

Yes. We basically use it to tell the DB to be hands off about the value.

Hello, I found the same problem. I can nothing do with 1 page. I don't need this page. Deleting doesn't work. https://simpsonspedia.net/index.php?title=Homer&redirect=no

I found a experimental php-script. https://fossies.org/dox/mediawiki-1.39.1/DeletePage_8php_source.html

My first idea is a DB backup and than delete the entry in the table "page". But I think this isn't a clean way.

Next idea is using this experimental script after DB backup.

-/

Best regards

Hi Bawolf. Appreciate your input. If my problem is limited to just a handful pages/files, you mentioned they could be pointed to to "some actor". Any advice as to which?

The alternative would be to delete all mention of this content and create it anew (thereby correctly establishing that content and its relationships in the database like any new content created)

it doesn't really matter which actor. You could create a new user for that purpose.

I could just assign all these to me, but what values doe rev_actor and rev_comment_id etc. get? forgive me but it's all a bit overwhelming without prior in depth knowledge of the database structure. My goal would be for none of these to be 0, right? ev_minor_edit 0 rev_deleted 0 rev_len 0 rev_parent_id 0 isn't a problem?

sorry to be a bother, still looking for a step by step sort of instruction how to possibly "fix" this manually (at least as a "hack" - I don't care who these revisions are assigned to as long as the database is no longer practically corrupted and these files uneditable)

Still wondering how to solve this. There's something in phabricator that's not developed yet about re-calculating the content blobs and thus restoring the lost connections between the tables. If I know the handful of content items affected, could I do it manually? I wonder if I attempt another upgrade now will it work or is https://phabricator.wikimedia.org/T328169 the thing that needs to be done first.

At your current state, most you can get is assigning all those xx_actor and xx_commend_id an existing id from the actor and comment table respectively. For example, choose a system user like "maintenance script" or create a new user just for this purpose, and a comment that's empty or has a meaningful text like "imported revision" or "restored revision" (you can create a new page and use this edit summary to create it. That should make your revisions accessible again if they're broken due to inconsitencies in those fields.

My SQL skills are rather basic, I may need a more detailed instruction on how to do it. 1) is there a systematic way to catch all the cases that are corrupted, I keep finding wiki content by accident that is affected 2) which fields in the database do I change to which variables/numbers/ids etc without causing further breakage? And I don't think I understand what you mean by creating an empty comment etc. To be clear: affect content is entirely non-editable from the front end... Thanks for you help!