Wikimedia\Rdbms\DBQueryError from line 1195 of /srv/mw/core/includes/libs/rdbms/database/Database.php: Error 42601: ERROR:  syntax error at or near "PRIMARY"
LINE 1: ...gresUpdater::renameIndex  */ INDEX si_page RENAME TO PRIMARY
                                                                ^

Function: MediaWiki\Installer\PostgresUpdater::renameIndex
Query: ALTER INDEX si_page RENAME TO PRIMARY

#0 /srv/mw/core/includes/libs/rdbms/database/Database.php(1179): Wikimedia\Rdbms\Database->getQueryException('ERROR:  syntax ...', '42601', 'ALTER INDEX si_...', 'MediaWiki\\Insta...')
#1 /srv/mw/core/includes/libs/rdbms/database/Database.php(1153): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('ERROR:  syntax ...', '42601', 'ALTER INDEX si_...', 'MediaWiki\\Insta...')
#2 /srv/mw/core/includes/libs/rdbms/database/Database.php(647): Wikimedia\Rdbms\Database->reportQueryError('ERROR:  syntax ...', '42601', 'ALTER INDEX si_...', 'MediaWiki\\Insta...', false)
#3 /srv/mw/core/includes/libs/rdbms/database/DBConnRef.php(127): Wikimedia\Rdbms\Database->query(Object(Wikimedia\Rdbms\Query), 'MediaWiki\\Insta...', 0)
#4 /srv/mw/core/includes/libs/rdbms/database/DBConnRef.php(302): Wikimedia\Rdbms\DBConnRef->__call('query', Array)
#5 /srv/mw/core/includes/installer/PostgresUpdater.php(669): Wikimedia\Rdbms\DBConnRef->query('ALTER INDEX si_...', 'MediaWiki\\Insta...')
#6 /srv/mw/core/includes/installer/DatabaseUpdater.php(595): MediaWiki\Installer\PostgresUpdater->renameIndex('searchindex', 'si_page', 'PRIMARY', false, 'patch-searchind...')
#7 /srv/mw/core/includes/installer/DatabaseUpdater.php(548): MediaWiki\Installer\DatabaseUpdater->runUpdates(Array, false)
#8 /srv/mw/core/maintenance/update.php(195): MediaWiki\Installer\DatabaseUpdater->doUpdates(Array)
#9 /srv/mw/core/maintenance/includes/MaintenanceRunner.php(703): UpdateMediaWiki->execute()
#10 /srv/mw/core/maintenance/run.php(51): MediaWiki\Maintenance\MaintenanceRunner->run()
#11 {main}

What should have happened instead?:

No error.

Other information (browser name/version, screenshots, etc.):

The patch is documented as not actually doing anything on PostgreSQL, it's just for consistency.

Details

Related Changes in Gerrit:

Subject	Author	Repo	Branch	Lines +/-
Add migrateSearchindex functions	Reedy	mediawiki/core	REL1_43	+51 -2
Add migrateSearchindex functions	TheDJ	mediawiki/core	master	+51 -2
Repair searchindex update on Postgres	TheDJ	mediawiki/core	master	+31 -6

Customize query in gerrit

Related Objects

Mentioned Here: T231827: Data too long for column 'si_title'
T249976: Convert searchindex UNIQUE to PK
rMWa2ba7ee14d6b: Searchindex primary key, title length and utf8mb4

Event Timeline

tstarling created this task.Nov 12 2024, 1:44 AM

Restricted Application added a subscriber: • Aklapper. · View Herald TranscriptNov 12 2024, 1:44 AM

Note that index names in PG have schema scope, so even if PRIMARY were a valid name, you could only have one such index across all MW tables. It needs to be globally unique name. I think the name as installed would be searchindex_pkey, and manually renaming to that does seem to work.

Reedy added projects: MediaWiki-Installer, MW-1.43-release.Nov 12 2024, 2:40 AM

Peachey88 added a project: PostgreSQL.Nov 12 2024, 2:45 AM

I guess the main error in patch is assuming that renameIndex() conditionally runs a patch file. That's what it does in DatabaseUpdater, but it's overridden by PostgresUpdater to do its own special query. The patch file parameter is ignored.

ugh, this shouldn't be renameIndex at all. it should maybe do modifyTable or something. I accidentally copied this out of an older alternate version of the patch and it just happened to not immediately break mysql.

When I get back home tonight, I'l see if I can cleanup this mess.

So on mysql, the change has run, via renameIndex. This depends on a MySQL side effect, where the primary key has an index named 'PRIMARY'. This is WRONG, it should probably have used modifyTable. However, changing this now, will cause breakages for people who have run the MySQL index, as it will rerun the upgrade unconditionally, which will fail. I think i'll add a maintenance script in the update itself and then hopefully, we can eventually get rid of that pretty quickly.

On postgres, nothing has happened, so we can safely change it to modifyTable

Also, I think we should special case the name PRIMARY in the main renameIndex function, cause this might cause trouble more often this way.

Change #1090549 had a related patch set uploaded (by TheDJ; author: TheDJ):

[mediawiki/core@master] Repair searchindex update on Postgres

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

gerritbot added a project: Patch-For-Review.Nov 12 2024, 8:42 PM

Change #1090549 abandoned by TheDJ:

[mediawiki/core@master] Repair searchindex update on Postgres

Reason:

ugh. forgot about clean installs.. I guess modifyTable can't work any longer with abstractSchema changes, as it doesn't have a condition to guard on non-repeatable changes

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

I think the best solution is to use a custom function or maintenance script for both MySQL and Postgres with proper tests on the situations:

1.43 or 1.44 already converted via mysql renameIndex
1.43 or 1.44 not yet converted, applyPatch
1.43 or 1.44 fresh install

But i don't have more time today, so that will have to wait to tomorrow.

Maintenance_bot removed a project: Patch-For-Review.Nov 12 2024, 9:30 PM

Change #1090970 had a related patch set uploaded (by TheDJ; author: TheDJ):

[mediawiki/core@master] [WIP] Add migrateSearchindex functions

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

gerritbot added a project: Patch-For-Review.Nov 13 2024, 9:52 PM

Attempt #2. Wasn't able to test postgres yet, as my mac decided that it wants to crash php on every attempt to do anything with postgres.

I'll setup a docker for it instead:
https://www.mediawiki.org/wiki/MediaWiki-Docker/Configuration_recipes/Alternative_databases#Postgres_(single_database_server)

Reedy updated the task description. (Show Details)Nov 22 2024, 5:15 PM

We are close to the 1.43.0-rc.0 date. This seems to be still a relevant blocker, what can we do to support unblock it or move on without it in case I'm missing any context?

Someone needs to review the patch

Change #1090970 merged by jenkins-bot:

[mediawiki/core@master] Add migrateSearchindex functions

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

Maintenance_bot removed a project: Patch-For-Review.Nov 26 2024, 5:30 PM

Change #1098107 had a related patch set uploaded (by Reedy; author: TheDJ):

[mediawiki/core@REL1_43] Add migrateSearchindex functions

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

gerritbot added a project: Patch-For-Review.Nov 26 2024, 5:58 PM

ReleaseTaggerBot added a project: MW-1.44-notes (1.44.0-wmf.6; 2024-12-03).Nov 26 2024, 6:00 PM

Change #1098107 merged by jenkins-bot:

[mediawiki/core@REL1_43] Add migrateSearchindex functions