Last modified: 2007-03-26 12:52:14 UTC

Wikimedia Bugzilla is closed!

Wikimedia migrated from Bugzilla to Phabricator. Bug reports are handled in Wikimedia Phabricator.
This static website is read-only and for historical purposes. It is not possible to log in and except for displaying bug reports and their history, links might be broken. See T11103, the corresponding Phabricator task for complete and up-to-date bug report information.
Bug 9103 - SQL error on SpecialProtectedPages with PostgreSQL
SQL error on SpecialProtectedPages with PostgreSQL
Status: RESOLVED FIXED
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
unspecified
PC Linux
: Normal normal (vote)
: ---
Assigned To: Greg Sabino Mullane
:
Depends on:
Blocks: postgres
  Show dependency treegraph
 
Reported: 2007-02-25 19:14 UTC by Mathias Behrle
Modified: 2007-03-26 12:52 UTC (History)
1 user (show)

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


Attachments

Description Mathias Behrle 2007-02-25 19:14:38 UTC
This one is similar to 9097, but IMHO too different to add it there. Running a
fresh install of 1.10A (SVN) on PostgreSQL I get the following error(s), when
entering Protected Pages in Special Pages:

A database error has occurred Query: SELECT page_id, page_restrictions.*,
page_title,page_namespace FROM page_restrictions,page WHERE
(pr_expiry>'2007-02-25 17:19:42 GMT') AND (page_id=pr_page) GROUP BY page_id
ORDER BY pr_id DESC LIMIT 51 Function: IndexPager::reallyDoQuery
(ProtectedPagesPager) Error: 1 ERROR: column "pr_id" does not exist

Backtrace:

#0 /var/www/wiki/includes/Database.php(672):
DatabasePostgres->reportQueryError('ERROR: column ...', 1, 'SELECT page_id...',
'IndexPager::rea...', false)
#1 /var/www/wiki/includes/Database.php(1068): Database->query('SELECT
page_id...', 'IndexPager::rea...')
#2 /var/www/wiki/includes/Pager.php(184): Database->select(Array, 'page_id,
page_r...', Array, 'IndexPager::rea...', Array)
#3 /var/www/wiki/includes/Pager.php(102): IndexPager->reallyDoQuery('', 51, false)
#4 /var/www/wiki/includes/Pager.php(296): IndexPager->doQuery()
#5 /var/www/wiki/includes/SpecialProtectedpages.php(27): IndexPager->getNumRows()
#6 /var/www/wiki/includes/SpecialProtectedpages.php(137):
ProtectedPagesForm->showList()
#7 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialProtectedpages(NULL,
Object(SpecialPage))
#8 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#9 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#10 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#11 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#12 {main}


After having created column "pr_id" the next error is:

A database error has occurred Query: SELECT page_id, page_restrictions.*,
page_title,page_namespace FROM page_restrictions,page WHERE
(pr_expiry>'2007-02-25 17:26:33 GMT') AND (page_id=pr_page) GROUP BY page_id
ORDER BY pr_id DESC LIMIT 51 Function: IndexPager::reallyDoQuery
(ProtectedPagesPager) Error: 1 ERROR: column "page_restrictions.pr_page" must
appear in the GROUP BY clause or be used in an aggregate function

Backtrace:

#0 /var/www/wiki/includes/Database.php(672):
DatabasePostgres->reportQueryError('ERROR: column ...', 1, 'SELECT page_id...',
'IndexPager::rea...', false)
#1 /var/www/wiki/includes/Database.php(1068): Database->query('SELECT
page_id...', 'IndexPager::rea...')
#2 /var/www/wiki/includes/Pager.php(184): Database->select(Array, 'page_id,
page_r...', Array, 'IndexPager::rea...', Array)
#3 /var/www/wiki/includes/Pager.php(102): IndexPager->reallyDoQuery('', 51, false)
#4 /var/www/wiki/includes/Pager.php(296): IndexPager->doQuery()
#5 /var/www/wiki/includes/SpecialProtectedpages.php(27): IndexPager->getNumRows()
#6 /var/www/wiki/includes/SpecialProtectedpages.php(137):
ProtectedPagesForm->showList()
#7 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialProtectedpages(NULL,
Object(SpecialPage))
#8 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#9 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#10 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#11 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#12 {main}
Comment 1 Rob Church 2007-02-25 19:16:36 UTC
Another schema breakage from Werdna...
Comment 2 Aaron Schulz 2007-02-25 19:35:09 UTC
The PostgreSQL initial query is incomplete, it lacks pr_id and the sequence. Try
dropping the page_restrictions table and running the following query perhaps:

CREATE SEQUENCE pr_id_val;
CREATE TABLE page_restrictions (
  pr_id      INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('pr_id_val'),
  pr_page    INTEGER          NULL  REFERENCES page (page_id) ON DELETE CASCADE,
  pr_type    TEXT         NOT NULL,
  pr_level   TEXT         NOT NULL,
  pr_cascade SMALLINT     NOT NULL,
  pr_user    INTEGER          NULL,
  pr_expiry  TIMESTAMPTZ      NULL
);

Currently we have an ALTER query afterwords:

ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY
(pr_page,pr_type);

Though I am not sure why that contraint is added to PostgreSQL schemas while no
pr_id is added.
Comment 3 Aaron Schulz 2007-02-25 19:52:32 UTC
Fixed in r20053.
Comment 4 Mathias Behrle 2007-02-25 20:33:06 UTC
Thanks for looking into it, but it is not yet fixed:

1)
You forgot to put column
    pr_pagetype text
in tables.sql

2)
with above mentionned column added it is possible to protect pages, but going to
SpecialPages:Protected Pages gives now:

A database error has occurred Query: SELECT page_id, page_restrictions.*,
page_title,page_namespace FROM page_restrictions,page WHERE
(pr_expiry>'2007-02-25 20:28:58 GMT') AND (page_id=pr_page) GROUP BY page_id
ORDER BY pr_id DESC LIMIT 51 Function: IndexPager::reallyDoQuery
(ProtectedPagesPager) Error: 1 ERROR: column "page_restrictions.pr_id" must
appear in the GROUP BY clause or be used in an aggregate function

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: column ...', 1, 'SELECT page_id...',
'IndexPager::rea...', false)
#1 /var/www/wiki/includes/Database.php(1073): Database->query('SELECT
page_id...', 'IndexPager::rea...')
#2 /var/www/wiki/includes/Pager.php(184): Database->select(Array, 'page_id,
page_r...', Array, 'IndexPager::rea...', Array)
#3 /var/www/wiki/includes/Pager.php(102): IndexPager->reallyDoQuery('', 51, false)
#4 /var/www/wiki/includes/Pager.php(296): IndexPager->doQuery()
#5 /var/www/wiki/includes/SpecialProtectedpages.php(27): IndexPager->getNumRows()
#6 /var/www/wiki/includes/SpecialProtectedpages.php(137):
ProtectedPagesForm->showList()
#7 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialProtectedpages(NULL,
Object(SpecialPage))
#8 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#9 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#10 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#11 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#12 {main}
Comment 5 Aaron Schulz 2007-02-25 20:45:00 UTC
1) pr_pagetype is bug 9097, I'll see if I can look at that later.

2) see http://archives.postgresql.org/pgsql-general/2004-02/msg01199.php

It seems like this could occure with pr_id values that are the same, but since
the query should list it as UNIQUE, that should not happen. Did you view your
table, is this the case? If not, I suppose we can use max(pr_id) or DISTINCT ON,
but it still seems odd.
Comment 6 Greg Sabino Mullane 2007-03-18 00:40:37 UTC
I'm still not quite clear on why we are using pr_id at all. If it's merely to
allow the display to order by time added, why not make the new column a
timestamp field instead? (The table already has a primary key).
Comment 7 Andrew Garrett 2007-03-18 01:24:20 UTC
(In reply to comment #6)
> I'm still not quite clear on why we are using pr_id at all. If it's merely to
> allow the display to order by time added, why not make the new column a
> timestamp field instead? (The table already has a primary key).

Because the field was introduced AFTER the table was first created; and I didn't
want to necessitate pre-filling a timestamp field with bogus data. With pr_id,
SQL (mysql, at least) pre-filled the data field with autonumbering when the
update was first run.

In retrospect, I could have run IN the updater 'UPDATE page_restrictions SET
pr_timestamp=CURRENT_TIMESTAMP', but I don't see the point in changing this now.

Unassigning. I don't have time to sort this out at the moment - my academic work
takes priority over volunteer development.
Comment 8 Greg Sabino Mullane 2007-03-18 01:40:38 UTC
Thanks for the explanation, that makes sense. I'll see if I can figure out
exactly what the page is trying to show and modify it for Postgres next.
Comment 9 Greg Sabino Mullane 2007-03-19 14:58:30 UTC
Fixed in 20560, although it may have to be separated for mysql optimization, so
not resolving this yet.
Comment 10 Greg Sabino Mullane 2007-03-26 01:59:29 UTC
Hearing no objections, marking this as resolved.
Comment 11 Mathias Behrle 2007-03-26 11:17:03 UTC
Reopening since it not seems to be (completely?) resolved:
To avoid the error it is necessary to have a column pr_id in table
page_restrictions. AFAIS this is now contained in
/wiki/maintenance/mysql5/tables.sql as   
  -- Field for an ID for this restrictions row (sort-key for
Special:ProtectedPages)                                                        
                                                 
  pr_id int unsigned NOT NULL auto_increment

but it is not in /wiki/maintenance/postgres/tables.sql .

The definition would be:
ALTER TABLE page_restrictions ADD COLUMN pr_id int4;
ALTER TABLE page_restrictions ALTER COLUMN pr_id SET STORAGE PLAIN;
ALTER TABLE page_restrictions ALTER COLUMN pr_id SET NOT NULL;
ALTER TABLE page_restrictions ALTER COLUMN pr_id SET DEFAULT
nextval('page_restrictions_pr_id_seq'::regclass);

and is working for me this way.
Comment 12 Greg Sabino Mullane 2007-03-26 12:41:02 UTC
> To avoid the error it is necessary to have a column pr_id in table
...
> but it is not in /wiki/maintenance/postgres/tables.sql .

Sure it is, line 116. Added as of comment #3 above.

Comment 13 Mathias Behrle 2007-03-26 12:52:14 UTC
You are right, sorry for the noise!

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


Navigation
Links