Page MenuHomePhabricator

Special:UnusedProperties produces database error for PostgreSQL
Closed, ResolvedPublic

Description

Author: jensen.nick

Description:
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


Version: unspecified
Severity: major
URL: internal

Attached:

Details

Reference
bz19948

Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 21 2014, 10:39 PM
bzimport set Reference to bz19948.

jensen.nick wrote:

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

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?

jensen.nick wrote:

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}

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.

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.

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 );

fanett wrote:

  1. The access rights of user for schemas (public and wiki) are correct.

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

  1. 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)

overlordq wrote:

In Postgres you cannot specify more then one table in a DELETE statement.

nic.backflip wrote:

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.

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.

justjkk wrote:

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}

jwm wrote:

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.

jwm wrote:

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.

attachment smw-unusedprops-fix-1.5.5.1.patch ignored as obsolete

plewe wrote:

I was having this issue, and John's patch worked great for me too. Put it in the code!

overlordq wrote:

If it hasn't been committed yet it's neither resolved nor fixed.

*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*

plewe wrote:

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?

jwm wrote:

Postgresql unused properties SQL fixes, 1.6.1

A fresh patch against 1.6.1. What's holding up this bug's resolution?

Attached:

sumanah wrote:

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?

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.

Jdforrester-WMF added a subscriber: Jdforrester-WMF.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.