Last modified: 2007-03-29 14:43:27 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.
First Last Prev Next    This bug is not in your last search results.
Bug 9131 - Some GET parameter cause SQL errors in PostgreSQL (timestamps)
Some GET parameter cause SQL errors in PostgreSQL (timestamps)
Status: RESOLVED FIXED
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
unspecified
PC Linux
: Low minor (vote)
: ---
Assigned To: Greg Sabino Mullane
:
Depends on:
Blocks: postgres
  Show dependency tree / graph
 
Reported: 2007-02-28 17:38 UTC by Mathias Behrle
Modified: 2007-03-29 14:43 UTC (History)
1 user (show)

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

Attachments
Add an attachment (proposed patch, testcase, etc.)

Description Mathias Behrle 2007-02-28 17:38:49 UTC 
I am currently at rev 20080:
From time to time I discover errors in my postgres logs, that are caused by
Google Bot. While these errors are not relevant for the regular use of
mediawiki, they shouldn't nevertheless be caused by any input syntax (and AFAIS
are not relevant with mysql as far as I could test on wikipedia itself).

The first one is caused by &offset
First example:
A database error has occurred Query: SELECT rev_timestamp FROM page, revision
WHERE page_id = rev_page AND rev_timestamp > '' AND rev_user_text='Mathiasb'
ORDER BY rev_timestamp ASC LIMIT 20 OFFSET 0 Function: Error: 1 ERROR: invalid
input syntax for type timestamp with time zone: ""

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: invalid...', 1, 'SELECT rev_time...',
'', false)
#1 /var/www/wiki/includes/SpecialContributions.php(112): Database->query('SELECT
rev_time...')
#2 /var/www/wiki/includes/SpecialContributions.php(240):
ContribsFinder->getPreviousOffsetForPaging()
#3 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialContributions(NULL,
Object(UnlistedSpecialPage))
#4 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#5 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#6 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#7 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#8 {main}

"GET
/wiki/index.php?title=Spezial:Beitr%C3%A4ge&target=Mathiasb&offset=2006-10-16+11%3A50%3A23&limit=20&go=prev
HTTP/1.1" 500 697 "-" "Mozilla/5.0 (compatible; Googlebot/2.1;


The second one is caused by parameter &go=prev:

URL:
index.php?title=Spezial:Beitr%C3%A4ge&target=Mathiasb&offset=2006-10-16+11%3A50%3A23&limit=20&go=prev

A database error has occurred Query: SELECT rev_timestamp FROM page, revision
WHERE page_id = rev_page AND rev_timestamp > '' AND rev_user_text='Mathiasb'
ORDER BY rev_timestamp ASC LIMIT 20 OFFSET 0 Function: Error: 1 ERROR: invalid
input syntax for type timestamp with time zone: ""

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: invalid...', 1, 'SELECT rev_time...',
'', false)
#1 /var/www/wiki/includes/SpecialContributions.php(112): Database->query('SELECT
rev_time...')
#2 /var/www/wiki/includes/SpecialContributions.php(240):
ContribsFinder->getPreviousOffsetForPaging()
#3 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialContributions(NULL,
Object(UnlistedSpecialPage))
#4 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#5 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#6 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#7 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#8 {main}
Comment 1 Greg Sabino Mullane 2007-03-01 16:32:52 UTC 
Fixed in r20104, thanks for the report.

Basically, we need to do a regex check for the timestamps Postgres uses as
opposed to the integer used by MySQL for timestamps.
Comment 2 Mathias Behrle 2007-03-01 17:15:44 UTC 
Thanks a lot for the fix!

I think I reported not clear enough, and the errors I described are due to
different problems. I searched once again in my apache log and found the
following URLs left, which now still cause errors:


1)
wiki/index.php?title=Spezial:Gesperrte_IPs&limit=250&offset=50

A database error has occurred Query: SELECT ipblocks.*,user_name FROM
ipblocks,mwuser WHERE (ipb_expiry>'2007-03-01 16:56:17 GMT') AND
(ipb_by=user_id) AND (ipb_timestamp<'50') ORDER BY ipb_timestamp DESC LIMIT 251
Function: IndexPager::reallyDoQuery (IPBlocklistPager) Error: 1 ERROR: date/time
field value out of range: "50" HINT: Perhaps you need a different "datestyle"
setting.

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: date/ti...', 1, 'SELECT ipblock...',
'IndexPager::rea...', false)
#1 /var/www/wiki/includes/Database.php(1073): Database->query('SELECT
ipblock...', 'IndexPager::rea...')
#2 /var/www/wiki/includes/Pager.php(184): Database->select(Array,
'ipblocks.*,user...', Array, 'IndexPager::rea...', Array)
#3 /var/www/wiki/includes/Pager.php(102): IndexPager->reallyDoQuery('50', 251,
false)
#4 /var/www/wiki/includes/Pager.php(296): IndexPager->doQuery()
#5 /var/www/wiki/includes/SpecialIpblocklist.php(204): IndexPager->getNumRows()
#6 /var/www/wiki/includes/SpecialIpblocklist.php(43): IPUnblockForm->showList('')
#7 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialIpblocklist(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}

2)
wiki/index.php?title=Spezial:Beitr%C3%A4ge&target=Mathiasb&limit=20&go=prev

A database error has occurred Query: SELECT rev_timestamp FROM page, revision
WHERE page_id = rev_page AND rev_timestamp > '' AND rev_user_text='Mathiasb'
ORDER BY rev_timestamp ASC LIMIT 20 OFFSET 0 Function: Error: 1 ERROR: invalid
input syntax for type timestamp with time zone: ""

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: invalid...', 1, 'SELECT rev_time...',
'', false)
#1 /var/www/wiki/includes/SpecialContributions.php(112): Database->query('SELECT
rev_time...')
#2 /var/www/wiki/includes/SpecialContributions.php(249):
ContribsFinder->getPreviousOffsetForPaging()
#3 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialContributions(NULL,
Object(UnlistedSpecialPage))
#4 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#5 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#6 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#7 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#8 {main}
Comment 3 Rob Church 2007-03-01 22:01:25 UTC 
(In reply to comment #1)
> Basically, we need to do a regex check for the timestamps Postgres uses as
> opposed to the integer used by MySQL for timestamps.

It would be a much better idea if we continue to use one standard timestamp
format in interfaces, etc. You can adapt the internal timestamp format
conversion functions to support PostgreSQL timestamps if desired.
Comment 4 Greg Sabino Mullane 2007-03-29 14:43:27 UTC 
Adjusted SpecialContributions to use a standard timestamp format and do some
to_char manipulation as needed for Postgres. See r20808.
Note You need to log in before you can comment on or make changes to this bug.
First Last Prev Next    This bug is not in your last search results.

Navigation
Links