List of steps to reproduce (step by step, including full links if applicable):
- Try upgrading a PostgreSQL-based MediaWiki installation to 1.36.1:
- php update.php
What happens?:
Update script fails with
Changing column type of 'watchlist.wl_namespace' from 'integer' to 'INT' Wikimedia\Rdbms\DBQueryError from line 1719 of w/includes/libs/rdbms/database/Database.php : Error 23505: ERROR: could not create unique index "wl_user_namespace_title" DETAIL: Key (wl_namespace, wl_title, wl_user)=(0, string omitted, number omitted) is duplicated. Function: PostgresUpdater::changeField Query: ALTER TABLE watchlist ALTER wl_namespace TYPE INT USING 0
Reason: there is an error in the schema update:
[ 'changeField', 'watchlist', 'wl_namespace', 'INT', 0 ],
This forces PostgreSQL to set the value of wl_namespace to 0 for every row, which is impossible for an indexed field: there is a unique index,
CREATE UNIQUE INDEX wl_user ON mediawiki.watchlist USING btree (wl_user, wl_namespace, wl_title)
and the watchlist table by design will almost always (if someone used the Watch Page function) have rows with differing wl_namespace values:
MediaWiki works with the assumption that when people watch pages they either watch both the talk page and the normal page, or neither. That means that for each watched page MediaWiki always automatically adds two entries: One for the page and one for its talk page. E.g. when the user watches the Main Page, then there will be two rows in the database table: One for the Main Page in namespace 0 and one for the Main Page in namespace 1 (which is the according talk page). Missing page rows are added by the maintenance script update.php.
What should have happened instead?:
Script should have finished execution without errors.
Software version (if not a Wikimedia wiki), browser information, screenshots, other information, etc:
MediaWiki 1.35.2 → 1.36.1 (basic schema upgrade).