Last modified: 2012-08-04 20:49:10 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 19948 - Special:UnusedProperties produces database error for PostgreSQL
Special:UnusedProperties produces database error for PostgreSQL
Status: RESOLVED FIXED
Product: MediaWiki extensions
Classification: Unclassified
Semantic MediaWiki (Other open bugs)
unspecified
All All
: Normal major (vote)
: ---
Assigned To: Markus Krötzsch
internal
: patch, patch-need-review
Depends on:
Blocks: postgres
  Show dependency treegraph
 
Reported: 2009-07-26 18:51 UTC by Nick
Modified: 2012-08-04 20:49 UTC (History)
11 users (show)

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


Attachments
MediaWiki Local Settings (5.14 KB, application/octet-stream)
2009-07-26 18:51 UTC, Nick
Details
Postgresql unused properties SQL fixes (2.75 KB, patch)
2011-02-07 15:04 UTC, John Morton
Details
Postgresql unused properties SQL fixes, 1.6.1 (2.61 KB, patch)
2011-09-27 06:10 UTC, John Morton
Details

Description Nick 2009-07-26 18:51:26 UTC
Created attachment 6392 [details]
MediaWiki Local Settings

This is a new installation of mediawiki, semantic mediawiki and forms, so I've not seen it fully working yet.

Perhaps I have a misconfiguration? I've attempted the update script as advised at http://www.mediawiki.org/wiki/Manual:Upgrading#Shell. I've also performed a reinstall as directed at http://www.mediawiki.org/wiki/Manual:Upgrading#Alternative_2:_Re-run_the_installer.

Below I pasted in the relevant output from the Special:CreateTemplate, Special:Version and attached a sanitized source of LocalSettings.php.

System Information:
OS: Arch Linux, updated daily
Apache: Apache/2.2.11 (Unix) mod_ssl/2.2.11 OpenSSL/0.9.8k DAV/2 SVN/1.6.3 Server
Semantic MediaWiki & Forms: SVN rev 53770


index.php/Special:CreateTemplate
Internal error
A database error has occurred
Query: DELETE smw_tmp_unusedprops.* FROM smw_tmp_unusedprops, smw_rels2 INNER JOIN smw_ids ON p_id=smw_id WHERE title=smw_title AND smw_iw=''
Function: SMW::getUnusedPropertySubjects
Error: 1 ERROR: syntax error at or near "smw_tmp_unusedprops"
LINE 1: ...ETE /* SMW::getUnusedPropertySubjects 10.0.0.8 */ smw_tmp_un...
^
Backtrace:
#0 /srv/http/mediawiki/includes/db/Database.php(616): DatabasePostgres->reportQueryError('ERROR: syntax ...', 1, 'DELETE smw_tmp_...', 'SMW::getUnusedP...', false)
#1 /srv/http/mediawiki/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2.php(1006): Database->query('DELETE smw_tmp_...', 'SMW::getUnusedP...')
#2 /srv/http/mediawiki/extensions/SemanticForms/specials/SF_CreateTemplate.php(41): SMWSQLStore2->getUnusedPropertiesSpecial(Object(SMWRequestOptions))
#3 /srv/http/mediawiki/extensions/SemanticForms/specials/SF_CreateTemplate.php(89): SFCreateTemplate::getAllPropertyNames()
#4 /srv/http/mediawiki/extensions/SemanticForms/specials/SF_CreateTemplate.php(27): doSpecialCreateTemplate()
#5 /srv/http/mediawiki/includes/SpecialPage.php(559): SFCreateTemplate->execute(NULL)
#6 /srv/http/mediawiki/includes/Wiki.php(229): SpecialPage::executePath(Object(Title))
#7 /srv/http/mediawiki/includes/Wiki.php(59): MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage), Object(WebRequest))
#8 /srv/http/mediawiki/index.php(116): MediaWiki->initialize(Object(Title), NULL, Object(OutputPage), Object(User), Object(WebRequest))
#9 {main}

index.php/Special:Version
Version
...
Installed software
Product 	Version
MediaWiki 	1.15.1
PHP 	5.2.10 (apache2handler)
PostgreSQL 	8.3.7
Installed extensions
Special pages
Semantic Forms (Version 1.8) 	Forms for adding and editing semantic data 	Yaron Koren and others
Parser hooks
Semantic MediaWiki (Version 1.5e-SVN) 	Making your wiki more accessible - for machines and humans (online documentation) 	Klaus Lassleben, Markus Krötzsch, Denny Vrandecic, S Page, and others. Maintained by AIFB Karlsruhe.
Extension functions
sfgParserFunctions, sfgSetupExtension and smwfSetupExtension
Parser extension tags
<ask> and <pre>
Parser function hooks
anchorencode, arraymap, arraymaptemplate, ask, basepagename, basepagenamee, concept, declare, defaultsort, displaytitle, filepath, formatdate, formatnum, forminput, formlink, fullpagename, fullpagenamee, fullurl, fullurle, gender, grammar, info, int, language, lc, lcfirst, localurl, localurle, namespace, namespacee, ns, numberingroup, numberofactiveusers, numberofadmins, numberofarticles, numberofedits, numberoffiles, numberofpages, numberofusers, numberofviews, padleft, padright, pagename, pagenamee, pagesincategory, pagesize, plural, protectionlevel, set, set_recurring_event, show, special, subjectpagename, subjectpagenamee, subjectspace, subjectspacee, subpagename, subpagenamee, tag, talkpagename, talkpagenamee, talkspace, talkspacee, uc, ucfirst and urlencode
Comment 1 Nick 2009-07-26 20:33:42 UTC
Found this in /var/log/everything.log:
Jul 26 15:04:17 box httpd: PHP Warning:  pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR:  syntax error at or near &quot;smw_tmp_unusedprops&quot;
Jul 26 15:04:17 box LINE: 1: ...ETE /* SMW::getUnusedPropertySubjects 10.0.0.8 */ smw_tmp_un...
Jul 26 15:04:17 box ^: in /srv/http/mediawiki/includes/db/DatabasePostgres.php on line 580
Comment 2 Yaron Koren 2009-07-26 20:38:56 UTC
Ah, so you're using a Postgres database. This sounds like a bug in either Semantic MediaWiki's or Semantic Forms' handling of Postgres. What happens when you go to the page "Special:UnusedProperties" - do you get the same error?
Comment 3 Nick 2009-07-26 20:52:09 UTC
Yes it looks like the same error.

index.php/Special:UnusedProperties
Internal error
A database error has occurred
Query: DELETE smw_tmp_unusedprops.* FROM smw_tmp_unusedprops, smw_rels2 INNER JOIN smw_ids ON p_id=smw_id WHERE title=smw_title AND smw_iw=''
Function: SMW::getUnusedPropertySubjects
Error: 1 ERROR: syntax error at or near "smw_tmp_unusedprops"
LINE 1: ...ETE /* SMW::getUnusedPropertySubjects 10.0.0.8 */ smw_tmp_un...
^
Backtrace:
#0 /srv/http/mediawiki/includes/db/Database.php(616): DatabasePostgres->reportQueryError('ERROR: syntax ...', 1, 'DELETE smw_tmp_...', 'SMW::getUnusedP...', false)
#1 /srv/http/mediawiki/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2.php(1006): Database->query('DELETE smw_tmp_...', 'SMW::getUnusedP...')
#2 /srv/http/mediawiki/extensions/SemanticMediaWiki/specials/QueryPages/SMW_SpecialUnusedProperties.php(65): SMWSQLStore2->getUnusedPropertiesSpecial(Object(SMWRequestOptions))
#3 /srv/http/mediawiki/extensions/SemanticMediaWiki/specials/QueryPages/SMW_QueryPage.php(49): SMWUnusedPropertiesPage->getResults(Object(SMWRequestOptions))
#4 /srv/http/mediawiki/extensions/SemanticMediaWiki/specials/QueryPages/SMW_SpecialUnusedProperties.php(18): SMWQueryPage->doQuery(0, 50)
#5 [internal function]: smwfDoSpecialUnusedProperties(NULL, Object(SMWSpecialPage))
#6 /srv/http/mediawiki/includes/SpecialPage.php(771): call_user_func('smwfDoSpecialUn...', NULL, Object(SMWSpecialPage))
#7 /srv/http/mediawiki/includes/SpecialPage.php(559): SpecialPage->execute(NULL)
#8 /srv/http/mediawiki/includes/Wiki.php(229): SpecialPage::executePath(Object(Title))
#9 /srv/http/mediawiki/includes/Wiki.php(59): MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage), Object(WebRequest))
#10 /srv/http/mediawiki/index.php(116): MediaWiki->initialize(Object(Title), NULL, Object(OutputPage), Object(User), Object(WebRequest))
#11 {main}
Comment 4 Yaron Koren 2009-07-26 22:29:57 UTC
Okay, I changed the bug title, and the extension (to Semantic MediaWiki), since this is really an SMW bug - I'm hoping this will work.
Comment 5 Yaron Koren 2009-07-26 22:37:08 UTC
Also re-assigning to Markus...
Comment 6 Markus Krötzsch 2009-08-01 16:57:45 UTC
Did you actually initialize the database after installing SMW? You should find a number of tables starting with "smw..." in your database, and basic operations such as adding simple annotations should work without errors. If this is the problem, please follow the SMW installation instructions (using either Special:SMWAdmin or SMW_setup.php). Also please make sure that the installation works normally without SMW enabled before switching on the extension.

If all of those basic checks are okay, then we can continue debugging. Do you already have any idea why the given query could be illegal in Postgres?

P.S.: Note that Postgres support in SMW is still somewhat experimental due to the smaller size of our Postgres user base. If you find Postgres support to be too shaky for your application then switching to MySQL might be the best option for now.
Comment 7 Markus Krötzsch 2010-01-10 15:02:00 UTC
More information is needed here. What happens if you run the query:

 DELETE smw_tmp_unusedprops.* FROM smw_tmp_unusedprops, smw_rels2 INNER JOIN smw_ids ON p_id=smw_id WHERE title=smw_title AND smw_iw=''

directly in Postgres (in the context of your wiki database)? You will have to create the table smw_tmp_unusedprops first. For this purpose, run the query:

 CREATE TEMPORARY TABLE smw_tmp_unusedprops ( title text );

Comment 8 Aneta 2010-01-11 08:52:27 UTC
1) The access rights of user for schemas (public and wiki) are correct. 
2) 
EXEC: CREATE TEMPORARY TABLE smw_tmp_unusedprops ( title text );
OK (La consulta se ejecutó con éxito sin resutado en 16 ms.)

EXEC: DELETE smw_tmp_unusedprops.* FROM smw_tmp_unusedprops, smw_rels2 INNER JOIN
smw_ids ON p_id=smw_id WHERE title=smw_title AND smw_iw=''
ERROR:  error de sintaxis en o cerca de «smw_tmp_unusedprops»
LINE 1: DELETE smw_tmp_unusedprops.* FROM smw_tmp_unusedprops, smw_r...
               ^
********** Error **********
ERROR: error de sintaxis en o cerca de «smw_tmp_unusedprops»
Estado SQL:42601
Caracter: 8
----

3) I have modified the request.

EXEC: select * FROM smw_tmp_unusedprops where title in (select smw_title from smw_tmp_unusedprops, smw_rels2 INNER JOIN
smw_ids ON p_id=smw_id WHERE title=smw_title AND smw_iw='')

OK (empty result - my "smw_rels2" is empty but smw_ids is not)
Comment 9 OverlordQ 2010-01-11 08:58:03 UTC
In Postgres you cannot specify more then one table in a DELETE statement.
Comment 10 Nic Bf 2010-03-18 20:59:29 UTC
I have re-written the delete statement to comply with the Postgres sql syntax:

delete from smw_tmp_unusedprops
  using smw_rels2 inner join smw_ids on smw_rels2.p_id = smw_ids.smw_id
  where smw_ids.smw_iw='' and smw_title=smw_tmp_unusedprops.title;

I tested it on the command line. It worked.
Comment 11 Markus Krötzsch 2010-05-27 14:03:08 UTC
The proposed query did not work in MySQL, since it does not include smw_tmp_unusedprops in the USING part. Trying to find a query that works in either case, I came up with the following:

DELETE FROM smw_tmp_unusedprops
 USING smw_tmp_unusedprops INNER JOIN smw_rels2 INNER JOIN smw_ids
 ON p_id=smw_id AND title=smw_title AND smw_iw='';

I hope this still works on Postgres (if not, please re-open the bug).

The next SMW release 1.5.1 that will include this change is scheduled for Monday, May 31.
Comment 12 J Kishore Kumar 2010-12-31 17:52:17 UTC
I can confirm this bug with Semantic Mediawiki 1.5.4 and Postgresql 8.5.4

When opening Special:UnusedProperties page with $wgShowExceptionDetails = true;
I get the following,

A database error has occurred
Query: DELETE FROM smw_tmp_unusedprops USING smw_tmp_unusedprops INNER JOIN
smw_rels2 INNER JOIN smw_ids ON p_id=smw_id AND title=smw_title AND smw_iw=''
Function: SMW::getUnusedPropertySubjects
Error: 1 ERROR: syntax error at end of input
LINE 1: ...OIN smw_ids ON p_id=smw_id AND title=smw_title AND smw_iw=''
^
Backtrace:

#0 /home/jkk/mediawiki-1.16.0/w/includes/db/Database.php(538):
DatabasePostgres->reportQueryError('ERROR: syntax ...', 1, 'DELETE FROM
smw...', 'SMW::getUnusedP...', false)
#1
/home/jkk/mediawiki-1.16.0/w/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2.php(1106):
DatabaseBase->query('DELETE FROM smw...', 'SMW::getUnusedP...')
#2
/home/jkk/mediawiki-1.16.0/w/extensions/SemanticMediaWiki/specials/QueryPages/SMW_SpecialUnusedProperties.php(100):
SMWSQLStore2->getUnusedPropertiesSpecial(Object(SMWRequestOptions))
#3
/home/jkk/mediawiki-1.16.0/w/extensions/SemanticMediaWiki/specials/QueryPages/SMW_QueryPage.php(49):
SMWUnusedPropertiesPage->getResults(Object(SMWRequestOptions))
#4
/home/jkk/mediawiki-1.16.0/w/extensions/SemanticMediaWiki/specials/QueryPages/SMW_SpecialUnusedProperties.php(45):
SMWQueryPage->doQuery(0, 50)
#5 /home/jkk/mediawiki-1.16.0/w/includes/SpecialPage.php(559):
SMWSpecialUnusedProperties->execute(NULL)
#6 /home/jkk/mediawiki-1.16.0/w/includes/Wiki.php(254):
SpecialPage::executePath(Object(Title))
#7 /home/jkk/mediawiki-1.16.0/w/includes/Wiki.php(64):
MediaWiki->handleSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#8 /home/jkk/mediawiki-1.16.0/w/index.php(117):
MediaWiki->performRequestForTitle(Object(Title), NULL, Object(OutputPage),
Object(User), Object(WebRequest))
#9 /home/jkk/mediawiki-1.16.0/w/index.php5(1): require('/home/jkk/media...')
#10 {main}
Comment 13 John Morton 2011-01-26 14:07:30 UTC
It appears that Postgresql doesn't like USING to include the table in FROM, while mysql requires it. I think both support using a subquery, but that might be expensive. Maybe including more data in the temporary table might be the way forward.

I ended up changing both delete queries and removed "TEMPORARY" from the table drop to get Special:UnusedProperties working.
Comment 14 John Morton 2011-02-07 15:04:29 UTC
Created attachment 8095 [details]
Postgresql unused properties SQL fixes

I've split the two delete queries into postgres and other, and fixed up the postgres side to work. Also dropped the TEMPORARY syntatic sugar from the DROP TABLE as it's not necessary under mysql, and not supported by postgresql.

Works for me; between that and the #26202 patch, Postgresql backend works well.
Comment 15 Brandon Plewe 2011-02-16 18:38:42 UTC
I was having this issue, and John's patch worked great for me too.  Put it in the code!
Comment 16 OverlordQ 2011-02-16 19:02:52 UTC
If it hasn't been committed yet it's neither resolved nor fixed.
Comment 17 p858snake 2011-04-30 00:10:13 UTC
*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*
Comment 18 Brandon Plewe 2011-08-05 23:37:25 UTC
This problem is still in SMW 1.6, and John's patch still works (with some minor changes to match the latest code).  When will this patch (or something more robust) be committed?
Comment 19 John Morton 2011-09-27 06:10:06 UTC
Created attachment 9105 [details]
Postgresql unused properties SQL fixes, 1.6.1

A fresh patch against 1.6.1. What's holding up this bug's resolution?
Comment 20 Sumana Harihareswara 2011-11-04 18:42:05 UTC
I added the "need-review" tag so developers know that this patch needs reviewing.  I've added Greg Sabino Mullane & Andy Lester to the cc list so they'll know the patch needs reviewing; perhaps one of them can help test and review it?
Comment 21 Markus Krötzsch 2012-02-11 20:08:04 UTC
Sorry for keeping you waiting. Patch applied in r111256. I hope this fixes the issue in PostgreSQL.

I kept the "TEMPORARY" keyword for non-postgres databases, since I am not certain that DROP can be done by users that have only DROP TEMPORARY privileges, even if the dropped table happens to be temporary.

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


Navigation
Links