Last modified: 2012-08-04 20:48:57 UTC
On MediaWiki 1.16 and Semantic MediaWiki from svn on PostgreSQL 9.0.1 when i'm edit/create new page i'm always see this error:
A database error has occurred
Query: UPDATE page SET page_touched = '11012' WHERE page_namespace = '0' AND page_title = 'PPA'
Error: 1 ERROR: invalid input syntax for type timestamp with time zone: "11012"
LINE 1: ...re::updateData XVilka */ page SET page_touched = '11012' WH...
#0 /usr/home/droid-dev/www/includes/db/Database.php(538): DatabasePostgres->reportQueryError('ERROR: invalid...', 1, 'UPDATE page SE...', 'SMWStore::updat...', false)
#1 /usr/home/droid-dev/www/includes/db/Database.php(1212): DatabaseBase->query('UPDATE page SE...', 'SMWStore::updat...')
#2 /usr/home/droid-dev/www/extensions/SemanticMediaWiki/includes/storage/SMW_Store.php(238): DatabaseBase->update('page', Array, Array, 'SMWStore::updat...')
#3 /usr/home/droid-dev/www/extensions/SemanticMediaWiki/includes/SMW_ParseData.php(252): SMWStore->updateData(Object(SMWSemanticData))
#4 /usr/home/droid-dev/www/extensions/SemanticMediaWiki/includes/SMW_ParseData.php(406): SMWParseData::storeData(Object(ParserOutput), Object(Title), true)
#5 [internal function]: SMWParseData::onLinksUpdateConstructed(Object(LinksUpdate))
#6 /usr/home/droid-dev/www/includes/Hooks.php(133): call_user_func_array(Array, Array)
#7 /usr/home/droid-dev/www/includes/LinksUpdate.php(72): wfRunHooks('LinksUpdateCons...', Array)
#8 /usr/home/droid-dev/www/includes/Article.php(3317): LinksUpdate->LinksUpdate(Object(Title), Object(ParserOutput))
#9 /usr/home/droid-dev/www/includes/Article.php(2081): Article->editUpdates('== Primary Prot...', 'Created page wi...', true, '20110227174448', '953', true)
#10 /usr/home/droid-dev/www/includes/Article.php(1764): Article->doEdit('== Primary Prot...', '', 101)
#11 /usr/home/droid-dev/www/includes/EditPage.php(900): Article->insertNewArticle('== Primary Prot...', '', true, false, false, false, false)
#12 /usr/home/droid-dev/www/includes/EditPage.php(2552): EditPage->internalAttemptSave(false, false)
#13 /usr/home/droid-dev/www/includes/EditPage.php(389): EditPage->attemptSave()
#14 /usr/home/droid-dev/www/includes/EditPage.php(271): EditPage->edit()
#15 /usr/home/droid-dev/www/extensions/FCKeditor/FCKeditor.body.php(159): EditPage->submit()
#16 [internal function]: FCKeditor_MediaWiki->onCustomEditor(Object(Article), Object(User))
#17 /usr/home/droid-dev/www/includes/Hooks.php(133): call_user_func_array(Array, Array)
#18 /usr/home/droid-dev/www/includes/Wiki.php(545): wfRunHooks('CustomEditor', Array)
#19 /usr/home/droid-dev/www/includes/Wiki.php(70): MediaWiki->performAction(Object(OutputPage), Object(Article), Object(Title), Object(User), Object(WebRequest))
#20 /usr/home/droid-dev/www/index.php(117): MediaWiki->performRequestForTitle(Object(Title), Object(Article), Object(OutputPage), Object(User), Object(WebRequest))
Also similar error was in this bug: https://bugzilla.wikimedia.org/show_bug.cgi?id=26295
Thanks for reporting this. There are multiple known issues with the recent versions of SMW and PostGres. I will have a look at this as soon as the PostGres support of the MW 1.17 installer gets fixed.
I note that this error relates to the recent AutoRefreshSubject extension in SMW. It might be that it is not specific to Postgres.
How do you figure it's related to the AutoRefreshSubject stuff?
Its in the bug report: it gives the lines where the SQL error came from. Moreover, SMW does usually not touch any MW tables, and the bug is about an insert in the "page" table. Is this really necessary?
Indeed, now I see. It's an update to indicate the page cache is invalid (not an insert), and it's doing pretty much the same as the invalidation function in MW core, with the difference that the code in SMW is adding some number to the timestamp, to ensure it's bigger then the one at which the page is actually saved.
In any case, I'll have another go at the whole MW+SMW PostGres story tomorrow.
FWIW, removing the extra addition on the timestamp in SMW_Store.php:238 makes it work again.
I just changed the addition to only happen for mysql, should find a more decent fix later on though, because now the automatic refresh won't happen for postgres. Can you confirm the issue is 'fixed'?
Your modification is the same as I did. It's working for me, without further verifying cache behavior.
A better fix: SMW_Store.php +233
array( 'page_touched' => $dbw->timestamp(time() +9001) ),
Seems to work as well and should fix the cache problem.
Ah, nice, thanks :) Just committed that
This wasn't an issue in SMW 1.5.4, but seems to have reappeared in 1.5.6; when I put Pieter Kersten's patch back into SMW_Store.php, it works fine. Please check this for 1.6.
(In reply to comment #12)
> This wasn't an issue in SMW 1.5.4, but seems to have reappeared in 1.5.6; when
> I put Pieter Kersten's patch back into SMW_Store.php, it works fine. Please
> check this for 1.6.
I'm not sure what you are referring to. Please elaborate or open a new bug report.
I mean that I started seeing this bug when I upgraded SMW to 1.5.6. I was not seeing it in 1.5.4. I assume in April you committed Pieter's patch into what became 1.5.4, but somehow 1.5.6 had the older code. I was able to put the patch back in, and it worked. I just was hoping someone could check the code for SMW 1.6 to make sure the patched code is there.
The patch might have been reverted due to side effects. I don't really remember, so will have to look into this further. I really can't make this change and risk introducing this side effect at this point, as SMW 1.6 is planned to be released tomorrow. I'll look at it after the release.