Page MenuHomePhabricator
Create Task
Maniphest T312744

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

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

Details

SubjectRepoBranchLines +/-
Drop broken postgres/patch-oathauth_users-drop-id-nextval.sqlmediawiki/extensions/OATHAuthREL1_38+0 -6
Drop broken postgres/patch-oathauth_users-drop-id-nextval.sqlmediawiki/extensions/OATHAuthREL1_39+0 -6
Drop broken postgres/patch-oathauth_users-drop-id-nextval.sqlmediawiki/extensions/OATHAuthmaster+0 -6
Customize query in gerrit

Event Timeline

Mig5net created this task.Jul 11 2022, 4:44 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 11 2022, 4:44 AM
JJMC89 added a project: PostgreSQL.Jul 11 2022, 5:23 AM
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
gerritbot added a comment.Jul 11 2022, 9:09 PM

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

gerritbot added a project: Patch-For-Review.Jul 11 2022, 9:10 PM
Legoktm claimed this task.Jul 11 2022, 9:16 PM
Legoktm added a project: MW-1.38-release.
Legoktm added a comment.Jul 11 2022, 9:38 PM

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 🤔)

Legoktm added a comment.Jul 12 2022, 5:11 AM

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?

Ladsgroup subscribed.Jul 12 2022, 1:54 PM

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.

gerritbot added a comment.Sep 30 2022, 8:16 AM

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

gerritbot added a comment.Sep 30 2022, 8:17 AM

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

gerritbot added a comment.Sep 30 2022, 8:18 AM

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

gerritbot added a comment.Sep 30 2022, 8:23 AM

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

gerritbot added a comment.Sep 30 2022, 8:23 AM

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

Maintenance_bot removed a project: Patch-For-Review.Sep 30 2022, 8:30 AM
Ladsgroup closed this task as Resolved.Sep 30 2022, 8:31 AM
ReleaseTaggerBot added a project: MW-1.40-notes (1.40.0-wmf.4; 2022-10-03).Sep 30 2022, 9:00 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL