Page MenuHomePhabricator

Special:BrowseData with SQL error browsing a category having filters
Closed, ResolvedPublic

Description

Hi Yaron (and all readers :-)

we are using Semantic Drilldown (Version 1.2.4) together with Semantic MediaWiki (Version 1.8) and browsing data using http://terms.gbif.org/wiki/Special:BrowseData/Concept on our Terms-Wiki a database query syntax error has occurred. This happens only with the specified Category:Concept (http://terms.gbif.org/wiki/Class:Concept) which has 6 filters on it (the debug output is given below). The browsing of data works fine until I add the filter Issued (http://terms.gbif.org/wiki/Filter:Issued) which also sets „Month“ as time period. I tested step by step and figured out that only filter specifications that set [[Uses time period::Month]] or [[Uses time period::Year]] cause this SQL error. I hope the debug message gives you a point to start from. We also did the upgrade procedure migrate from 1.7.1 to 1.8.0

php SMW_refreshData.php -v -b SMWSQLStore3 -fp
php SMW_refreshData.php -v -b SMWSQLStore3


I read the SMDrillDown PHP code, I'm not sure if it has to do with temporary tables?


Can this be solved? Thanks a lot!!!

kind regards
Andreas

Background: Category:Concept has 6 filters specified, 2 among them [[Uses time period::Month]] these [[Uses time period::Month]] or [[Uses time period::Year]] causes the following debug output (we have a MediaWiki 1.20.2 core installation)

A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:
SELECT YEAR(), MONTH(), count(*) FROM semantic_drilldown_values sdv JOIN smw_di_blob a ON sdv.id = a.s_id JOIN smw_object_ids p_ids ON a.p_id = p_ids.smw_id WHERE p_ids.smw_title = 'dcterms:issued' GROUP BY YEAR(), MONTH() ORDER BY YEAR(), MONTH()
from within function "". Database returned error "1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), MONTH(), count(*) FROM semantic_drilldown_values sdv JOIN smw_di_blob a ' at line 1 (localhost)".

Backtrace:

#0 /usr/share/mediawiki20/includes/db/Database.php(916): DatabaseBase->reportQueryError('You have an err...', 1064, '?SELECT YEAR(),...', '', false)
#1 /usr/share/mediawiki20/extensions/SemanticDrilldown/includes/SD_Filter.php(244): DatabaseBase->query('?SELECT YEAR(),...')
#2 /usr/share/mediawiki20/extensions/SemanticDrilldown/includes/SD_Filter.php(269): SDFilter->getTimePeriodValues()
#3 /usr/share/mediawiki20/extensions/SemanticDrilldown/specials/SD_BrowseData.php(724): SDFilter->getAllValues()
#4 /usr/share/mediawiki20/extensions/SemanticDrilldown/specials/SD_BrowseData.php(922): SDBrowseDataPage->printUnappliedFilterLine(Object(SDFilter), '/wiki/Special:B...')
#5 /usr/share/mediawiki20/includes/QueryPage.php(527): SDBrowseDataPage->getPageHeader()
#6 /usr/share/mediawiki20/extensions/SemanticDrilldown/specials/SD_BrowseData.php(121): QueryPage->execute('Concept')
#7 /usr/share/mediawiki20/includes/SpecialPage.php(611): SDBrowseData->execute('Concept')
#8 /usr/share/mediawiki20/includes/SpecialPageFactory.php(494): SpecialPage->run('Concept')
#9 /usr/share/mediawiki20/includes/Wiki.php(290): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#10 /usr/share/mediawiki20/includes/Wiki.php(536): MediaWiki->performRequest()
#11 /usr/share/mediawiki20/includes/Wiki.php(446): MediaWiki->main()
#12 /usr/share/mediawiki20/index.php(59): MediaWiki->run()
#13 {main}


Version: REL1_20-branch
Severity: critical
URL: http://terms.gbif.org/wiki/Special:BrowseData/Concept

Details

Reference
bz43260

Event Timeline

bzimport raised the priority of this task from to High.Nov 22 2014, 12:56 AM
bzimport set Reference to bz43260.

Hi

browsing in above example also by specifying a string for a filter (we have filter Label that represents a string) a SQL error occurs as well. This happens when I browse for label “de:Datengrundlage”, see
http://terms.gbif.org/w/index.php?_search_Label=de%3ADatengrundlage&title=Special%3ABrowseData%2FConcept&_single=&limit=500

The error message now is not detailed right now (19.12.2012 13:41; I have to switch it on in the evening and post debug messages then):

A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:
(SQL query hidden)
from within function "SDBrowseDataPage::reallyDoQuery". Database returned error "1242: Subquery returns more than 1 row (localhost)".

Andreas

Hi Andreas,

This seems like two unrelated problems in SD. The first one, with dates, comes from the fact that you were using date-type filtering on a property of type "String". That's not allowed - although SD should display an appropriate error message for that, instead of just letting the SQL query crash.

The second one seems like a real issue. (Or, more real.) Could you please add the following line to LocalSettings.php, at least temporarily:

$wgShowSQLErrors = true;

...and put here what the actual query is?

Hi Yaron,

(1) you are rigth the first one can be resolved by setting the filters property to date (not string as I did) and you are rigth too that SD should display an error message or would be a nice feature to have.

(2) filtering by a string shows the debug message below (filter Label in our case, I switched $wgShowSQLErrors = true; just temporarily on):
http://terms.gbif.org/w/index.php?_search_Label=de%3ADatengrundlage&title=Special%3ABrowseData%2FConcept&_single=&limit=500

A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:
SELECT DISTINCT ids.smw_title AS title, ids.smw_title AS value, ids.smw_title AS t, ids.smw_namespace AS namespace, ids.smw_namespace AS ns, ids.smw_id AS id, ids.smw_iw AS iw, ids.smw_sortkey AS sortkey FROM smw_object_ids ids JOIN smw_fpt_inst insts ON ids.smw_id = insts.s_id AND ids.smw_namespace != 14 JOIN smw_di_blob a0 ON ids.smw_id = a0.s_id WHERE insts.o_id IN (SELECT smw_id FROM smw_object_ids cat_ids WHERE smw_namespace = 14 AND (smw_title = 'Concept')) AND a0.p_id = (SELECT smw_id FROM smw_object_ids WHERE smw_title = 'skos:prefLabel' AND smw_namespace = 102) AND (a0.o_hash LIKE '%de:Datengrundlage%') ORDER BY sortkey LIMIT 251
from within function "SDBrowseDataPage::reallyDoQuery". Database returned error "1242: Subquery returns more than 1 row (localhost)".

Backtrace:

#0 /usr/share/mediawiki20/includes/db/Database.php(916): DatabaseBase->reportQueryError('Subquery return...', 1242, 'SELECT DISTINCT...', 'SDBrowseDataPag...', false)
#1 /usr/share/mediawiki20/includes/QueryPage.php(397): DatabaseBase->query('SELECT DISTINCT...', 'SDBrowseDataPag...')
#2 /usr/share/mediawiki20/includes/QueryPage.php(488): QueryPage->reallyDoQuery(251, 0)
#3 /usr/share/mediawiki20/extensions/SemanticDrilldown/specials/SD_BrowseData.php(121): QueryPage->execute('Concept')
#4 /usr/share/mediawiki20/includes/SpecialPage.php(611): SDBrowseData->execute('Concept')
#5 /usr/share/mediawiki20/includes/SpecialPageFactory.php(494): SpecialPage->run('Concept')
#6 /usr/share/mediawiki20/includes/Wiki.php(290): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#7 /usr/share/mediawiki20/includes/Wiki.php(536): MediaWiki->performRequest()
#8 /usr/share/mediawiki20/includes/Wiki.php(446): MediaWiki->main()
#9 /usr/share/mediawiki20/index.php(59): MediaWiki->run()
#10 {main}

Hi,

Okay - the issue that causes all this is, I think, a bug in SMW, where more than one "SMW ID" is being set for the same page. My guess is that this problem happened during your database upgrade. Specifically, one of the following queries is returning more than one row:

SELECT smw_id FROM smw_object_ids WHERE smw_namespace = 14 AND smw_title =
'Concept'

SELECT smw_id FROM smw_object_ids WHERE smw_title = 'skos:prefLabel' AND smw_namespace = 102

I would be curious which of these is generating the problem (my guess is the 2nd one), but in any case, again this is something Semantic Drilldown should be handling better - this time by ignoring the problem, instead of displaying an error message. I just added a change in Gerrit, that's meant to fix this problem, here:

https://gerrit.wikimedia.org/r/#/c/39609/

If you have Git, I'd recommend pulling the latest code. Otherwise, I guess you could just add the changes manually. Please let me know if that worked.

Hi Yaron,

(1) I did use git HEAD of SemanticDrilldown but it did not help in the end. You mentioned the problem lies in the update procedure somehow, so
(2) I did first on Special:SMWAdmin clicked on, started [Initialise or upgrade tables] and
(3) clicked on/started [Start updating data]. After that
(4) the error occurred: MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' (maybe a JOIN can do it? See similar problem http://stackoverflow.com/questions/12434525/this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subquery)

(5) I switched back to SemanticDrilldown 1.2.4.

Below is the debug output of HEAD of SemanticDrilldown and http://terms.gbif.org/w/index.php?title=Special:BrowseData/Concept was:

A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:
SELECT DISTINCT ids.smw_title AS title, ids.smw_title AS value, ids.smw_title AS t, ids.smw_namespace AS namespace, ids.smw_namespace AS ns, ids.smw_id AS id, ids.smw_iw AS iw, ids.smw_sortkey AS sortkey FROM smw_object_ids ids JOIN smw_fpt_inst insts ON ids.smw_id = insts.s_id AND ids.smw_namespace != 14 WHERE insts.o_id IN (SELECT smw_id FROM smw_object_ids cat_ids WHERE smw_namespace = 14 AND (smw_title = 'Concept') LIMIT 1) ORDER BY sortkey LIMIT 501
from within function "SDBrowseDataPage::reallyDoQuery". Database returned error "1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' (localhost)".

Backtrace:

#0 /usr/share/mediawiki20/includes/db/Database.php(916): DatabaseBase->reportQueryError('This version of...', 1235, 'SELECT DISTINCT...', 'SDBrowseDataPag...', false)
#1 /usr/share/mediawiki20/includes/QueryPage.php(397): DatabaseBase->query('SELECT DISTINCT...', 'SDBrowseDataPag...')
#2 /usr/share/mediawiki20/includes/QueryPage.php(488): QueryPage->reallyDoQuery(501, 0)
#3 /usr/share/mediawiki20/extensions/SemanticDrilldown/specials/SD_BrowseData.php(124): QueryPage->execute('Concept')
#4 /usr/share/mediawiki20/includes/SpecialPage.php(611): SDBrowseData->execute('Concept')
#5 /usr/share/mediawiki20/includes/SpecialPageFactory.php(494): SpecialPage->run('Concept')
#6 /usr/share/mediawiki20/includes/Wiki.php(290): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#7 /usr/share/mediawiki20/includes/Wiki.php(536): MediaWiki->performRequest()
#8 /usr/share/mediawiki20/includes/Wiki.php(446): MediaWiki->main()
#9 /usr/share/mediawiki20/index.php(59): MediaWiki->run()
#10 {main}

Oops! You're right - it always causes problems when I check in code without testing it first, but somehow I keep doing it. Anyway, I just checked in a fix that uses "MAX" instead of "LIMIT" - it at least doesn't crash.

By the way, if possible, could you run those two queries I listed above? I'm still curious which one returns more than one row.

Yaron,

SELECT smw_id FROM smw_object_ids WHERE smw_namespace = 14 AND smw_title =
'Concept'
returns 1 and

SELECT smw_id FROM smw_object_ids WHERE smw_title = 'skos:prefLabel' AND
smw_namespace = 102
returs 38 results

Your FIX I will try but I can't right now.

Andreas

Dear Yaron

it is not fixed yet :-(

http://terms.gbif.org/wiki/Special:BrowseData/Concept?_single&Modified=April_2009
… returns the debug messages below

I used your latest fix in HEAD with your git comment
Fix - replaced "SELECT ... LIMIT 1" with "SELECT MAX" for subqueries
Change-Id: If6810a391641d2fb7cbb0feeba13bde80f039542

We still run that HEAD version but debug is switched of.

Is it the following subquery that causes trouble?
SELECT smw_id FROM smw_object_ids WHERE smw_title = 'terms-internal:dcterms-modified' AND smw_namespace = 102
…it returns 9 results maybe this "AND a0.p_id = (SELECT smw_id …)" should be altered to "AND a0.p_id IN(SELECT smw_id …)". At least it returns a result set an no error but I don't have such a deep code understanding

Andreas

A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:
SELECT DISTINCT ids.smw_title AS title, ids.smw_title AS value, ids.smw_title AS t, ids.smw_namespace AS namespace, ids.smw_namespace AS ns, ids.smw_id AS id, ids.smw_iw AS iw, ids.smw_sortkey AS sortkey FROM smw_object_ids ids JOIN smw_fpt_inst insts ON ids.smw_id = insts.s_id AND ids.smw_namespace != 14 JOIN smw_di_time a0 ON ids.smw_id = a0.s_id WHERE insts.o_id IN (SELECT smw_id FROM smw_object_ids cat_ids WHERE smw_namespace = 14 AND (smw_title = 'Concept')) AND a0.p_id = (SELECT smw_id FROM smw_object_ids WHERE smw_title = 'terms-internal:dcterms-modified' AND smw_namespace = 102) AND (YEAR(SUBSTR(o_serialized, 3, 100)) = 2009 AND MONTH(SUBSTR(o_serialized, 3, 100)) = 4 ) ORDER BY sortkey LIMIT 251
from within function "SDBrowseDataPage::reallyDoQuery". Database returned error "1242: Subquery returns more than 1 row (localhost)".

Backtrace:

#0 /usr/share/mediawiki20/includes/db/Database.php(916): DatabaseBase->reportQueryError('Subquery return...', 1242, 'SELECT DISTINCT...', 'SDBrowseDataPag...', false)
#1 /usr/share/mediawiki20/includes/QueryPage.php(397): DatabaseBase->query('SELECT DISTINCT...', 'SDBrowseDataPag...')
#2 /usr/share/mediawiki20/includes/QueryPage.php(488): QueryPage->reallyDoQuery(251, 0)
#3 /usr/share/mediawiki20/extensions/SemanticDrilldown/specials/SD_BrowseData.php(124): QueryPage->execute('Concept')
#4 /usr/share/mediawiki20/includes/SpecialPage.php(611): SDBrowseData->execute('Concept')
#5 /usr/share/mediawiki20/includes/SpecialPageFactory.php(494): SpecialPage->run('Concept')
#6 /usr/share/mediawiki20/includes/Wiki.php(290): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#7 /usr/share/mediawiki20/includes/Wiki.php(536): MediaWiki->performRequest()
#8 /usr/share/mediawiki20/includes/Wiki.php(446): MediaWiki->main()
#9 /usr/share/mediawiki20/index.php(59): MediaWiki->run()
#10 {main}

(In reply to comment #8)

Dear Yaron,

is there any solution yet to this bug? Is the SSQL-code snippet from …

"AND a0.p_id = (SELECT smw_id …)"

… to …

"AND a0.p_id IN(SELECT smw_id …)"

… a solution?

Andreas

Hi Andreas,

Sorry I never responded - this totally slipped my mind.

The basic issue is that there should only be one entry, i.e. one SMW ID for each page. The fact that you have nine (!) for this property is bad. SD could do more to counteract such errors (more additions of "MAX()" are needed), but that still doesn't solve the basic problem.

Have you run SMW_refreshData.php at any point? It may help in this case. You can run it from the web interface as well, by going to Special:SMWAdmin and clicking on the "Start updating data" button.

Hi Yaron,

I did refresh all data with "Start updating data" but still the same:

A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:
(SQL query hidden)
from within function "SDBrowseDataPage::reallyDoQuery". Database returned error "1242: Subquery returns more than 1 row (localhost)".

:-/

Andreas, I was having the same issues as you describe here, then upgraded to SD 1.3 and instead of filters producing an error message, no results are returned ("There are no results for this report"). Could you upgrade and tell us what happens?

Should this be marked as a SMW bug as well?

Marking this as "fixed", since this exact SQL issue no longer seems to be a problem in the latest version of SD.