I get the same error when upgrading from 1.39(.13) to 1.43(.3). At the moment rerunning the script results in a broken wiki (internal error 500).

I wrote some debug logs before starting the upgrade, and during upgrade this is logged:

ALTER INDEX site_type RENAME TO sites_type

No wonder the drop later fails...

The site_* indexes are dropped in rMW616744db1d7d: Schema: Drop unused and useless indexes of sites table (1.42). They were renamed from sites_* in rMW9907b56c9b4f: Rename all sites indexes (1.39).

In T374042#10984231, @joernc_unibi wrote:

I wrote some debug logs before starting the upgrade, and during upgrade this is logged:

ALTER INDEX site_type RENAME TO sites_type

That's supposed to happen in the opposite direction. Please provide more detailed logs.

Quick correction: For me the error occurs on site_group, not site_type. But I see the same command (ALTER INDEX, DROP) for both indexes.

Attached are the complete debug logs ($wgDebugLogFile) of running an update from 1.39.13 to 1.43.3.

The ALTER INDEX is in line 1511 and the DROP INDEX is in line 6095.

A \di on the database shows that the index was renamed:

public | site_domain                      | index | mwtephemeral01 | sites
public | site_forward                     | index | mwtephemeral01 | sites
public | site_global_key                  | index | mwtephemeral01 | sites
public | site_identifiers_pkey            | index | mwtephemeral01 | site_identifiers
public | site_language                    | index | mwtephemeral01 | sites
public | site_protocol                    | index | mwtephemeral01 | sites
public | site_source                      | index | mwtephemeral01 | sites
public | site_stats_pkey                  | index | mwtephemeral01 | site_stats
public | sites_group                      | index | mwtephemeral01 | sites
public | sites_pkey                       | index | mwtephemeral01 | sites
public | slot_revision_origin_role        | index | mwtephemeral01 | slots

... and site_type was successfully removed in line 6093 ... because the renaming from site_type to sites_type (line 1505) is reverted in line 2871. So either the renaming is unnecessary in the first place, or both indexes should be renamed twice.

These are all ALTER INDEXes from the log:

ALTER INDEX site_global_key RENAME TO sites_global_key
ALTER INDEX site_type RENAME TO sites_type
ALTER INDEX site_group RENAME TO sites_group
ALTER INDEX site_source RENAME TO sites_source
ALTER INDEX site_language RENAME TO sites_language
ALTER INDEX site_protocol RENAME TO sites_protocol
ALTER INDEX site_domain RENAME TO sites_domain
ALTER INDEX site_forward RENAME TO sites_forward
ALTER INDEX sites_global_key RENAME TO site_global_key
ALTER INDEX sites_type RENAME TO site_type
ALTER INDEX sites_source RENAME TO site_source
ALTER INDEX sites_language RENAME TO site_language
ALTER INDEX sites_protocol RENAME TO site_protocol
ALTER INDEX sites_domain RENAME TO site_domain
ALTER INDEX sites_forward RENAME TO site_forward

So only site(s)_group is missing from this back and forth.

I tried looking into the code where these renamings are happening, but I can't find anything, where site(s)_group is an outlier compared to the other indexes. Maybe someone with more knowledge of this code can take a look? At the moment I consider this a showstopper to migrate from one LTS version to the next.

One puzzle piece is probably a bug that lies somewhere in the past. A Wiki under 1.39.13, that has been updated over several versions since 1.35, has these indices:

 Schema |         Name          | Type  |      Owner      |      Table
--------+-----------------------+-------+-----------------+------------------
 public | site_domain           | index | mwpspielplatz01 | sites
 public | site_forward          | index | mwpspielplatz01 | sites
 public | site_global_key       | index | mwpspielplatz01 | sites
 public | site_identifiers_pkey | index | mwpspielplatz01 | site_identifiers
 public | site_language         | index | mwpspielplatz01 | sites
 public | site_protocol         | index | mwpspielplatz01 | sites
 public | site_source           | index | mwpspielplatz01 | sites
 public | site_stats_pkey       | index | mwpspielplatz01 | site_stats
 public | site_type             | index | mwpspielplatz01 | sites
 public | sites_group           | index | mwpspielplatz01 | sites
 public | sites_pkey            | index | mwpspielplatz01 | sites

sites_group is the outlier already here. I guess the subsequent update scripts assume that all indices are either site_ or sites_, but not a mix.

If I install a fresh Wiki with 1.39.13, the indices are all named uniformly:

 Schema |         Name          | Type  |     Owner      |      Table
--------+-----------------------+-------+----------------+------------------
 public | site_domain           | index | mwtephemeral01 | sites
 public | site_forward          | index | mwtephemeral01 | sites
 public | site_global_key       | index | mwtephemeral01 | sites
 public | site_group            | index | mwtephemeral01 | sites
 public | site_identifiers_pkey | index | mwtephemeral01 | site_identifiers
 public | site_language         | index | mwtephemeral01 | sites
 public | site_protocol         | index | mwtephemeral01 | sites
 public | site_source           | index | mwtephemeral01 | sites
 public | site_stats_pkey       | index | mwtephemeral01 | site_stats
 public | site_type             | index | mwtephemeral01 | sites
 public | sites_pkey            | index | mwtephemeral01 | sites

What I don't understand yet: The update of both wikis above fails with exactly the same error (essential what the OP already presented). I can fix the update of the second Wiki (the one freshly installed from 1.39.13) with the attached patch. The update of the first wiki, with the site/sites mix, still fails.

To continue this quest: These are the indices of a fairly recent 1.39 Wiki, that was deployed earlier this year with 1.39.11 and later updated to 1.39.12:

 Schema |         Name          | Type  |     Owner      |      Table
--------+-----------------------+-------+----------------+------------------
 public | site_domain           | index | mwpubmedizin01 | sites
 public | site_forward          | index | mwpubmedizin01 | sites
 public | site_global_key       | index | mwpubmedizin01 | sites
 public | site_identifiers_pkey | index | mwpubmedizin01 | site_identifiers
 public | site_language         | index | mwpubmedizin01 | sites
 public | site_protocol         | index | mwpubmedizin01 | sites
 public | site_source           | index | mwpubmedizin01 | sites
 public | site_stats_pkey       | index | mwpubmedizin01 | site_stats
 public | site_type             | index | mwpubmedizin01 | sites
 public | sites_group           | index | mwpubmedizin01 | sites
 public | sites_pkey            | index | mwpubmedizin01 | sites

The discrepancy between sites_group and the other indices is already present here.

I can only repeat myself: This is IMHO a major bug that prevents a clean update from LTS 1.39 to LTS 1.43, at least when using PostgreSQL as database.

Testing locally, I can reproduce that:

• downloading MediaWiki 1.39.13 from https://releases.wikimedia.org/mediawiki/1.39/mediawiki-1.39.13.zip;
• running maintenance/install.php with a Postgres database (I used MediaWiki-Docker);
• and then running maintenance/update.php

...results in the database having an index named sites_group.

An index with that name is not present prior to running update.php. (Before running update.php, the index is named site_group.)

Aaaah I think I might have found it.

Lurking in https://gerrit.wikimedia.org/r/c/mediawiki/core/+/649488/3/includes/installer/PostgresUpdater.php#887 (9907b56c9b) is a veeeeery small typo.
The line reads

[ 'renameIndex', 'sites', 'sites_group, ', 'site_group' ],

...when it should presumably read

[ 'renameIndex', 'sites', 'sites_group', 'site_group' ],

There's a comma and a space in the wrong place.

/cc @Ammarpad @Ladsgroup

In T374042#10985979, @Tgr wrote:

[...] They were renamed from sites_* in rMW9907b56c9b4f: Rename all sites indexes (1.39).

(Just for the record, the rename took place in 1.36, rather than 1.39. I also thought 1.39 at first, probably because the first branch that Gerrit says https://gerrit.wikimedia.org/r/649488 is "included in" is REL1_39; but I believe that's because the earlier release-branches have since been deleted and replaced with tags.)

Can I award an Eagle Eye scout badge to @A_smart_kitten ? I looked at that code (I simply removed it with the patch above) but that part eluded me!

I can confirm that an update of a fresh 1.39.13 install to 1.43.3 now succeeds. I'll need some more time to check a "used" Wiki :)

Thank you for catching it. If you create a patch I will review it ASAP.

Thinking about the best way to fix this for everyone (with regard to the currently-supported MW versions)...

• For MW 1.39, I'm thinking that it should hopefully be enough to just fix the typo in the renameIndex statement. If update.php is then run on a 1.39 wiki using Postgres, the index should then be correctly renamed.
• We should also fix the renameIndex typo for MW 1.43 & 1.44; which (unless I'm missing something) should resolve the issue for anyone who runs update.php on 1.43/1.44 for the first time. However, IMO, we should also cater for people who have already ran the upgrade script (& had it fail, and then maybe ran it again); given that it was our mistake in the first place that led to the upgrade script failing:
  • The error in the upgrade script occurred when running the second 'DROP INDEX' statement in the patch-sites-drop_indexes.sql file. This means that this index, and all the following indexes, won't have been dropped by update.php (due to the error being thrown).
  • However, as the first statement in that file - DROP INDEX site_type; - did succeed, and as the upgrade script checks for the existence of the 'site_type' index when deciding whether to apply this .sql patch, the other indexes in the file currently won't be dropped on any future runs of update.php. (To my understanding, this is what allows the second run of the update.php maintenance script to succeed -- the script doesn't re-attempt to drop these indexes, so it doesn't re-run into the problem of the site_group index not existing.)
  • Therefore, I'm thinking that - for 1.43 onwards - we should add additional statements to the Postgres update script, to re-attempt to delete each of the remaining indexes (where they still exist).
• For the not-yet-released MW 1.45, we're not able to fix this typo (as the statements which contained the typo have since been removed). I'm therefore thinking that (in addition to what I wrote in the bullet-points for 1.43/1.44), prior to the statements to drop the indexes, we should add an extra statement to rename sites_group to site_group.

What do you think @Ladsgroup / is there anything I'm missing? I hope I've worded things okay, but please ask if there's something you're not sure about/that I've written poorly!
If this sounds okay to you/others in principle, I'll aim to get some patches consistent with what I've written above uploaded as soon as I can/within the next few days :)

Change #1172070 had a related patch set uploaded (by A smart kitten; author: A smart kitten):

[mediawiki/core@master] PostgresUpdater: Re-attempt to rename the `sites_group` index; re-attempt to drop most of the dropped `sites` indexes

https://gerrit.wikimedia.org/r/1172070

I've uploaded a patch for master - I've also prepared patches for REL1_44/REL1_43 & REL1_39, but I haven't uploaded them yet in case there are any changes requested to this first patch :) (although I can also upload them now as well if that'd be helpful!)

Change #1172070 merged by jenkins-bot:

[mediawiki/core@master] PostgresUpdater: Re-attempt to rename the `sites_group` index; re-attempt to drop most of the dropped `sites` indexes

https://gerrit.wikimedia.org/r/1172070

Change #1175232 had a related patch set uploaded (by A smart kitten; author: A smart kitten):

[mediawiki/core@REL1_44] PostgresUpdater: Fix typo in `sites_group` index renaming; re-attempt to drop most of the dropped `sites` indexes

https://gerrit.wikimedia.org/r/1175232

Change #1175237 had a related patch set uploaded (by A smart kitten; author: A smart kitten):

[mediawiki/core@REL1_43] PostgresUpdater: Fix typo in `sites_group` index renaming; re-attempt to drop most of the dropped `sites` indexes

https://gerrit.wikimedia.org/r/1175237

Change #1175238 had a related patch set uploaded (by A smart kitten; author: A smart kitten):

[mediawiki/core@REL1_39] PostgresUpdater: Fix typo in `sites_group` index renaming instruction

https://gerrit.wikimedia.org/r/1175238

Change #1175238 merged by jenkins-bot:

[mediawiki/core@REL1_39] PostgresUpdater: Fix typo in `sites_group` index renaming instruction

https://gerrit.wikimedia.org/r/1175238

Change #1175237 merged by jenkins-bot:

[mediawiki/core@REL1_43] PostgresUpdater: Fix typo in `sites_group` index renaming; re-attempt to drop most of the dropped `sites` indexes

https://gerrit.wikimedia.org/r/1175237

Change #1175232 merged by jenkins-bot:

[mediawiki/core@REL1_44] PostgresUpdater: Fix typo in `sites_group` index renaming; re-attempt to drop most of the dropped `sites` indexes

https://gerrit.wikimedia.org/r/1175232

Thanks for the code-reviews @Ladsgroup!

As a side note, @joernc_unibi, I'm still unsure how/if this specific error would have caused your wiki to return 500 internal errors after a second run of update.php - maybe that's potentially due to another issue? Either way, if you're still experiencing an issue after these patches, please feel free to file a bug report :)

I guess the internal error was caused by the PHP cache and is not really related to this problem. I'm not sure if I manage to check the patches, my plan was to wait for the next proper release :) I see what I can do.

Anyway: THANKS for taking up this issue and providing a thorough fix for it!

I finally managed to test the patch from https://gerrit.wikimedia.org/r/c/mediawiki/core/+/1175232. Updating a wiki from 1.39.13 to 1.43.3 with the patch applied succeeds without errors. These are the remaining indices starting with "site(s)":

public | site_global_key                  | index | mwtephemeral01 | sites
public | site_identifiers_pkey            | index | mwtephemeral01 | site_identifiers
public | site_stats_pkey                  | index | mwtephemeral01 | site_stats
public | sites_pkey                       | index | mwtephemeral01 | sites