Reported in IRC, when upgrading from 1.15.5 to 1.18.1
More information to come (IRC is on different machine...)
Version: 1.18.x
Severity: normal
Reported in IRC, when upgrading from 1.15.5 to 1.18.1
More information to come (IRC is on different machine...)
Version: 1.18.x
Severity: normal
Status | Subtype | Assigned | Task | |
---|---|---|---|---|
· · · | ||||
Invalid | None | T2384 [REPLACED BY TAG] PostgreSQL/pgsql support (tracking) | ||
Resolved | None | T35689 Upgrade to 1.19 on Postgres fails due to incomplete query when trying to defer foreign key for externallinks | ||
· · · |
<ThomasLocke> Hey guys. Trying to update to 1.18.1 from 1.15.5 but am getting stuck at the database error: A database query syntax error has occurred. The last attempted database query was: "ALTER TABLE externallinks DROP CONSTRAINT ".
<Reedy> ThomasLocke, do you get a message the line before saying "Altering column "...
<Reedy> If so, can you paste the whole line?
<Reedy> I can see where iit's seemingly happening
<ThomasLocke> Altering column 'externallinks.el_from' to be DEFERRABLE INITIALLY DEFERRED
<ThomasLocke> ALTER /* */ TABLE externallinks DROP CONSTRAINT
<Reedy> the offending line is (in trunk at least) line 513 of includes/installer/PostgresUpdater.php
<Reedy> it unconditionally used $fi->conname() no matter it's value
Getting the user to do:
SELECT attnotnull, attlen, COALESCE(conname, '') AS conname, COALESCE(condeferred, 'f') AS deferred, COALESCE(condeferrable, 'f') AS deferrable, CASE WHEN typname = 'int2' THEN 'smallint' WHEN typname = 'int4' THEN 'integer' WHEN typname = 'int8' THEN 'bigint' WHEN typname = 'bpchar' THEN 'char' ELSE typname END AS typname FROM pg_class c JOIN pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_attribute a ON (a.attrelid = c.oid) JOIN pg_type t ON (t.oid = a.atttypid) LEFT JOIN pg_constraint o ON (o.conrelid = c.oid AND a.attnum = ANY(o.conkey) AND o.contype = 'f') WHERE relkind = 'r' AND nspname='mediawiki' AND relname='externallinks' AND attname='elfrom';
via sql.php gave "Query OK, 0 row(s)"...
Can't reproduce;
On a fresh MediaWiki 1.15.5 install on PostgreSQL 9.1.3 the query returns:
SELECT attnotnull, attlen, COALESCE(conname, '') AS conname,
COALESCE(condeferred, 'f') AS deferred, COALESCE(condeferrable, 'f') AS
deferrable, CASE WHEN typname = 'int2' THEN 'smallint' WHEN typname = 'int4'
THEN 'integer' WHEN typname = 'int8' THEN 'bigint' WHEN typname = 'bpchar' THEN
'char' ELSE typname END AS typname FROM pg_class c JOIN pg_namespace n ON
(n.oid = c.relnamespace) JOIN pg_attribute a ON (a.attrelid = c.oid) JOIN
pg_type t ON (t.oid = a.atttypid) LEFT JOIN pg_constraint o ON (o.conrelid =
c.oid AND a.attnum = ANY(o.conkey) AND o.contype = 'f') WHERE relkind = 'r' AND
nspname='mediawiki' AND relname='externallinks' AND attname='el_from';
------+--------+----------------------------+----------+------------+---------
t | 4 | externallinks_el_from_fkey | f | f | integer
Would be good to know which PostgreSQL version is installed there (and preferably on which the original was installed).
This query works for me even as above
on MediaWiki 1.7.3 installed on PostgreSQL 8.2.23
Please reopen if there is more information.
I think I nailed it down when testing for gerrit change 3841. It happens when updater invokes
array( 'changeFkeyDeferrable', 'externallinks', 'el_from', 'page(page_id) ON DELETE CASCADE' ),
but there is no foreign key constraint on el_from.
It's strange since I could trace this definition:
el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
back to this commit:
commit 6832afd9ea7d280e0e63648424de26e6ead6bf56
Author: Brion Vibber <brion@users.mediawiki.org>
Date: Sun Apr 30 17:36:16 2006 +0000
Somehow managed to forget to check this in...
So it seems like the constraint should always be there.
There is a small issue though with PostgresUpdater::changeFkeyDeferrable and DatabasePostgres::FieldInfo::conname()
conname() returns empty string instead of NULL because of
COALESCE(conname, '') AS conname
easy fix...
Submitted gerrit change 3889 to propose a fix.
commit fa6ab166863f911e927720878763cda7e945f96d Change-Id: I7ca351e07d228afdf4a5c3bef365f42a27c9ac45