Last modified: 2007-03-29 14:43:27 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}
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.
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}
(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.
Adjusted SpecialContributions to use a standard timestamp format and do some to_char manipulation as needed for Postgres. See r20808.
