Last modified: 2013-10-23 18:17:32 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 33689 - Upgrade to 1.19 on Postgres fails due to incomplete query when trying to defer foreign key for externallinks
Upgrade to 1.19 on Postgres fails due to incomplete query when trying to defe...
Status: RESOLVED FIXED
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
1.18.x
All All
: Low normal (vote)
: 1.19.0 release
Assigned To: Nobody - You can work on this!
:
Depends on:
Blocks: postgres
  Show dependency treegraph
 
Reported: 2012-01-12 20:17 UTC by Sam Reed (reedy)
Modified: 2013-10-23 18:17 UTC (History)
2 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments

Description Sam Reed (reedy) 2012-01-12 20:17:17 UTC
Reported in IRC, when upgrading from 1.15.5 to 1.18.1

More information to come (IRC is on different machine...)
Comment 1 Sam Reed (reedy) 2012-01-12 20:18:06 UTC
<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
Comment 2 Sam Reed (reedy) 2012-01-12 20:53:43 UTC
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)"...
Comment 3 Marcin Cieślak 2012-03-08 20:13:16 UTC
... AND atname='el_from'; ?
Comment 4 Marcin Cieślak 2012-03-16 01:36:49 UTC
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).
Comment 5 Marcin Cieślak 2012-03-16 01:42:39 UTC
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.
Comment 6 Marcin Cieślak 2012-03-28 19:58:34 UTC
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...
Comment 7 Marcin Cieślak 2012-03-28 21:28:36 UTC
Submitted Gerrit change #3889 to propose a fix.

    commit fa6ab166863f911e927720878763cda7e945f96d

    Change-Id: I7ca351e07d228afdf4a5c3bef365f42a27c9ac45

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links