Page MenuHomePhabricator

Patch patch-oathauth_users-drop-id-nextval.sql fails on PostgreSQL due to 'ALTER TABLE x CHANGE'
Closed, ResolvedPublicBUG REPORT

Description

Hi,

I tried to upgrade a Mediawiki 1.37.2 instance to 1.38.2 (the latest stable).

When I ran the php update.php update routine, the update failed with an SQL exception during the OATHAuth extension's SQL updates.

Steps to replicate the issue (include links if applicable):

  1. Install Mediawiki 1.37.2 with PostgreSQL (I use v13.7 on Debian 11 'Bullseye')
  2. Upgrade it to Mediawiki 1.38.2
  3. Run the update.php routine

What happens?:

...oathauth_users table already exists.
Modifying table oathauth_users...done.
Modifying id field of table oathauth_users...Wikimedia\Rdbms\DBQueryError from line 1606 of /srv/mediawiki/mediawiki-1.38.2/w/includes/libs/rdbms/database/Database.php: Error 42601: ERROR:  syntax error at or near "CHANGE"
LINE 1: ...rs-drop-id-nextval.sql )  */ TABLE oathauth_users CHANGE id ...
                                                             ^

Function: Wikimedia\Rdbms\Database::sourceFile( /srv/mediawiki/mediawiki-1.38.2/w/extensions/OATHAuth/sql/postgres/patch-oathauth_users-drop-id-nextval.sql )
Query: ALTER TABLE oathauth_users CHANGE id id INT NOT NULL


#0 /srv/mediawiki/mediawiki-1.38.2/w/includes/libs/rdbms/database/Database.php(1590): Wikimedia\Rdbms\Database->getQueryException('ERROR:  syntax ...', 42601, 'ALTER TABLE oat...', 'Wikimedia\\Rdbms...')
#1 /srv/mediawiki/mediawiki-1.38.2/w/includes/libs/rdbms/database/Database.php(1564): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('ERROR:  syntax ...', 42601, 'ALTER TABLE oat...', 'Wikimedia\\Rdbms...')
#2 /srv/mediawiki/mediawiki-1.38.2/w/includes/libs/rdbms/database/Database.php(1173): Wikimedia\Rdbms\Database->reportQueryError('ERROR:  syntax ...', 42601, 'ALTER TABLE oat...', 'Wikimedia\\Rdbms...', false)
#3 /srv/mediawiki/mediawiki-1.38.2/w/includes/libs/rdbms/database/Database.php(4954): Wikimedia\Rdbms\Database->query('ALTER TABLE oat...', 'Wikimedia\\Rdbms...')
#4 /srv/mediawiki/mediawiki-1.38.2/w/includes/libs/rdbms/database/Database.php(4889): Wikimedia\Rdbms\Database->sourceStream(Resource id #2592, NULL, NULL, 'Wikimedia\\Rdbms...', NULL)
#5 /srv/mediawiki/mediawiki-1.38.2/w/includes/libs/rdbms/database/DBConnRef.php(69): Wikimedia\Rdbms\Database->sourceFile('/srv/mediawiki/...')
#6 /srv/mediawiki/mediawiki-1.38.2/w/includes/libs/rdbms/database/MaintainableDBConnRef.php(35): Wikimedia\Rdbms\DBConnRef->__call('sourceFile', Array)
#7 /srv/mediawiki/mediawiki-1.38.2/w/includes/installer/DatabaseUpdater.php(704): Wikimedia\Rdbms\MaintainableDBConnRef->sourceFile('/srv/mediawiki/...')
#8 /srv/mediawiki/mediawiki-1.38.2/w/includes/installer/DatabaseUpdater.php(1019): DatabaseUpdater->applyPatch('/srv/mediawiki/...', true, 'Modifying id fi...')
#9 /srv/mediawiki/mediawiki-1.38.2/w/includes/installer/DatabaseUpdater.php(533): DatabaseUpdater->modifyField('oathauth_users', 'id', '/srv/mediawiki/...', true)
#10 /srv/mediawiki/mediawiki-1.38.2/w/includes/installer/DatabaseUpdater.php(501): DatabaseUpdater->runUpdates(Array, true)
#11 /srv/mediawiki/mediawiki-1.38.2/w/maintenance/update.php(193): DatabaseUpdater->doUpdates(Array)
#12 /srv/mediawiki/mediawiki-1.38.2/w/maintenance/doMaintenance.php(114): UpdateMediaWiki->execute()
#13 /srv/mediawiki/mediawiki-1.38.2/w/maintenance/update.php(264): require_once('/srv/mediawiki/...')
#14 {main}

What should have happened instead?:

The upgrade should have completed without error.

As far as I know, 'ALTER TABLE x CHANGE' , which works on MySQL, is not valid on PostgreSQL. https://dataschool.com/learn-sql/using-alter-in-postgresql/

Software version (skip for WMF-hosted wikis like Wikipedia):

Mediawiki 1.38.2
Debian 11.4 'Bullseye'
PostgreSQL 13.7
PHP 7.4.30

Event Timeline

Aklapper renamed this task from Patch patch-oathauth_users-drop-id-nextval.sql fails on PostgreSQL to Patch patch-oathauth_users-drop-id-nextval.sql fails on PostgreSQL due to 'ALTER TABLE x CHANGE'.Jul 11 2022, 9:50 AM

Change 812940 had a related patch set uploaded (by Legoktm; author: Legoktm):

[mediawiki/extensions/OATHAuth@master] Fix syntax in postgres/patch-oathauth_users-drop-id-nextval.sql

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

I did a quick search: https://codesearch.wmcloud.org/search/?q=CHANGE&i=nope&files=.*postgres.*%5C.sql%24&excludeFiles=&repos= seems like this is the only instance of this issue.

CI runs against postgres but we don't check patch files since we load the latest schema. Maybe we can have CI just execute the patch files and see that they don't bail out with a syntax error? (but if the patch file has some side effects that might wreck stuff 🤔)

Hmmm. On postgres 9.2, I ran:

        BEGIN;
	SET client_min_messages = 'ERROR';
	DROP SEQUENCE IF EXISTS oathauth_users_id_seq CASCADE;
	CREATE SEQUENCE oathauth_users_id_seq MINVALUE 0 START WITH 0;
	CREATE TABLE oathauth_users (
		-- User ID
		id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('oathauth_users_id_seq'),
		-- Module selected by the user
		module TEXT NULL,
		-- Module data
		data BYTEA NULL
	);
	COMMIT;

That's the same as https://gerrit.wikimedia.org/r/plugins/gitiles/mediawiki/extensions/OATHAuth/+/88552e65bfdc2f78ee8c7402def076969af70df5/sql/postgres/tables.sql - not that it already has NOT NULL.

wiki=# \d oathauth_users
                         Table "public.oathauth_users"
 Column |  Type   |                          Modifiers                          
--------+---------+-------------------------------------------------------------
 id     | integer | not null default nextval('oathauth_users_id_seq'::regclass)
 module | text    | 
 data   | bytea   | 
Indexes:
    "oathauth_users_pkey" PRIMARY KEY, btree (id)

Then I run the next patch:

wiki=# DROP SEQUENCE IF EXISTS oathauth_users_id_seq CASCADE;
DROP SEQUENCE
wiki=# \d oathauth_users
Table "public.oathauth_users"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 module | text    | 
 data   | bytea   | 
Indexes:
    "oathauth_users_pkey" PRIMARY KEY, btree (id)

Still integer NOT NULL. So what's the point of this patch in the first place?

My suggestion. Completely drop the sql file for PG. PostgresUpdater is different than MysqlUpdater and has way more sophisticated functions (such as changing nullability, etc.). Use those instead.

Change 812940 merged by jenkins-bot:

[mediawiki/extensions/OATHAuth@master] Drop broken postgres/patch-oathauth_users-drop-id-nextval.sql

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

Change 836987 had a related patch set uploaded (by Ladsgroup; author: Legoktm):

[mediawiki/extensions/OATHAuth@REL1_38] Drop broken postgres/patch-oathauth_users-drop-id-nextval.sql

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

Change 836988 had a related patch set uploaded (by Ladsgroup; author: Legoktm):

[mediawiki/extensions/OATHAuth@REL1_39] Drop broken postgres/patch-oathauth_users-drop-id-nextval.sql

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

Change 836988 merged by jenkins-bot:

[mediawiki/extensions/OATHAuth@REL1_39] Drop broken postgres/patch-oathauth_users-drop-id-nextval.sql

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

Change 836987 merged by jenkins-bot:

[mediawiki/extensions/OATHAuth@REL1_38] Drop broken postgres/patch-oathauth_users-drop-id-nextval.sql

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