Page MenuHomePhabricator

Drilldown 2.0.2 and Postgresql-Conflict: wrong SQL-Statement "CONVERT"
Closed, ResolvedPublic

Description

My System:
MW: 1.25.5 PHP: 5.6.23 PostgresSQL: 9.4.6 SMW: 2.3.1 SM Drilldown: 2.0.2
My Problem:
Whenever I want to use Special:BrowseData there is an issue with my Database:

CREATE TEMPORARY TABLE semantic_drilldown_filter_values AS SELECT s_id AS id, (IF(o_blob IS NULL, o_hash, CONVERT(o_blob using utf8))) AS value FROM "smw_di_blob" JOIN "smw_object_ids" p_ids ON "smw_di_blob".p_id = p_ids.smw_id WHERE p_ids.smw_title = 'WorkflowStatus'

I searched for this issue and found there ist no

CONVERT(o_blob using utf8)

function in PostgreSQL.

MyBacktrace:

#0 /srv/www/htdocs/SemanticMediaWiki/includes/db/DatabasePostgres.php(539): DatabaseBase->reportQueryError('ERROR:  syntax ...', '42601', '\tCREATE TEMPORA...', 'DatabaseBase::q...', false)

#1 /srv/www/htdocs/SemanticMediaWiki/includes/db/Database.php(1205): DatabasePostgres->reportQueryError('ERROR:  syntax ...', '42601', '\tCREATE TEMPORA...', 'DatabaseBase::q...', false)

#2 /srv/www/htdocs/SemanticMediaWiki/extensions/SemanticDrilldown/includes/SD_Filter.php(426): DatabaseBase->query('\tCREATE TEMPORA...')

#3 /srv/www/htdocs/SemanticMediaWiki/extensions/SemanticDrilldown/specials/SD_BrowseData.php(970): SDFilter->createTempTable()

#4 /srv/www/htdocs/SemanticMediaWiki/extensions/SemanticDrilldown/specials/SD_BrowseData.php(1187): SDBrowseDataPage->printUnappliedFilterLine(Object(SDFilter), '/XXX...')

#5 /srv/www/htdocs/SemanticMediaWiki/includes/specialpage/QueryPage.php(537): SDBrowseDataPage->getPageHeader()

#6 /srv/www/htdocs/SemanticMediaWiki/extensions/SemanticDrilldown/specials/SD_BrowseData.php(127): QueryPage->execute('Kapitel')

#7 /srv/www/htdocs/SemanticMediaWiki/includes/specialpage/SpecialPage.php(384): SDBrowseData->execute('Kapitel')

#8 /srv/www/htdocs/SemanticMediaWiki/includes/specialpage/SpecialPageFactory.php(582): SpecialPage->run('Kapitel')

#9 /srv/www/htdocs/SemanticMediaWiki/includes/MediaWiki.php(263): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))

#10 /srv/www/htdocs/SemanticMediaWiki/includes/MediaWiki.php(634): MediaWiki->performRequest()

#11 /srv/www/htdocs/SemanticMediaWiki/includes/MediaWiki.php(482): MediaWiki->main()

#12 /srv/www/htdocs/SemanticMediaWiki/index.php(41): MediaWiki->run()

#13 /srv/www/htdocs/SemanticMediaWiki/index.php5(26): require('/srv/www/htdocs...')

#14 {main}

Is this a real issue?

Event Timeline

m-art-in created this task.Jul 6 2016, 2:08 PM
Restricted Application added subscribers: Zppix, Aklapper. · View Herald TranscriptJul 6 2016, 2:08 PM
m-art-in updated the task description. (Show Details)Jul 6 2016, 2:08 PM
m-art-in updated the task description. (Show Details)
m-art-in renamed this task from Drilldown 2.0.2 and Postgresql-Conflict: Wriong SQL-Statement "CONVERT" to Drilldown 2.0.2 and Postgresql-Conflict: wrong SQL-Statement "CONVERT".Jul 6 2016, 3:38 PM

MW: 1.25.5

Off-topic and unrelated, but please use supported software instead of ancient versions with security issues. Thanks for considering!

MW: 1.25.5

Off-topic and unrelated, but please use supported software instead of ancient versions with security issues. Thanks for considering!

Don't thinks its a bug of MW 1.25.5 but of Drilldown 2.0.2

Meanwhile I updated to:
MediaWiki 1.27.1
PHP 7.0.7 (apache2handler)
PostgreSQL 9.4.9
SMW 2.4.1

Still with the same "42601 ERROR"

m-art-in triaged this task as Medium priority.Jan 19 2017, 2:23 PM
m-art-in added a project: PostgreSQL.

SQL-Statement which cases the error:

CREATE TEMPORARY TABLE semantic_drilldown_filter_values AS SELECT s_id AS id, (IF(o_blob IS NULL, o_hash, CONVERT(o_blob using utf8))) AS value FROM "smw_di_blob" JOIN "smw_object_ids" p_ids ON "smw_di_blob".p_id = p_ids.smw_id WHERE p_ids.smw_title = 'Sachbereich'

@m-art-in: As you set a priority for this task, do you plan to work on fixing this? Thanks. :)

Yaron_Koren closed this task as Resolved.Jan 20 2017, 4:51 AM

Sorry, I must have missed this before. It's fixed now, I think. (Feel free to re-open if not.)

m-art-in reopened this task as Open.Jan 23 2017, 8:25 AM

Sorry I have to reopen the task. Just installed from the latest git. The Convert() bug is solved, but now there is an other database error 42883:

42883 ERROR: function if(boolean, text, bytea) does not exist LINE 2: AS SELECT s_id AS id, (IF(o_blob IS NULL, o_hash, o_blob)) ... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

The SQL query:

CREATE TEMPORARY TABLE semantic_drilldown_filter_values AS SELECT s_id AS id, (IF(o_blob IS NULL, o_hash, o_blob)) AS value FROM "smw_di_blob" JOIN "smw_object_ids" p_ids ON "smw_di_blob".p_id = p_ids.smw_id WHERE p_ids.smw_title = 'Sachbereich'
m-art-in closed this task as Resolved.Jan 30 2017, 8:39 AM