Semanticmediawiki: MySQL error with #ask query on any property having a subproperty

[robbienam]

Setup and configuration

• SMW version: 3.1.5
• MW version: 1.34.0
• PHP version: 7.3.18 (cgi-fcgi)
• DB system: MySQL 5.7

Issue

I encounter a mySQL DB error when I try to perform any #ask inline query that selects on a property which itself has a subproperty. The semantic query in such cases apparently involves creating a temporary table, which mySQL says "violates GTID consistency" (Error 1787).

Stack trace

[XuC8KkrQOn0AAKTCrFQAAAAG] /dict/index.php?title=Test_query&action=submit Wikimedia\Rdbms\DBQueryError from line 1603 of /homepages/32/d413138210/htdocs/dict/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: CREATE TEMPORARY TABLE `t2`( id INT UNSIGNED KEY ) ENGINE=MEMORY
Function: SMW\SQLStore\TableBuilder\TemporaryTableBuilder::create
Error: 1787 Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions. (db5000265498.hosting-data.io)
Backtrace:

#0 /homepages/32/d413138210/htdocs/dict/includes/libs/rdbms/database/Database.php(1574): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#1 /homepages/32/d413138210/htdocs/dict/includes/libs/rdbms/database/Database.php(1152): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#2 /homepages/32/d413138210/htdocs/dict/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->query(string, string, integer)
#3 /homepages/32/d413138210/htdocs/dict/includes/libs/rdbms/database/DBConnRef.php(292): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#4 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/MediaWiki/Connection/Database.php(364): Wikimedia\Rdbms\DBConnRef->query(string, string, boolean)
#5 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/TableBuilder/TemporaryTableBuilder.php(59): SMW\MediaWiki\Connection\Database->query(string, string, boolean)
#6 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/HierarchyTempTableBuilder.php(111): SMW\SQLStore\TableBuilder\TemporaryTableBuilder->create(string)
#7 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(354): SMW\SQLStore\QueryEngine\HierarchyTempTableBuilder->fillTempTable(string, string, string, integer)
#8 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(131): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->hierarchy(SMW\SQLStore\QueryEngine\QuerySegment)
#9 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(145): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->segment(SMW\SQLStore\QueryEngine\QuerySegment)
#10 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(121): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->table(SMW\SQLStore\QueryEngine\QuerySegment)
#11 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(145): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->segment(SMW\SQLStore\QueryEngine\QuerySegment)
#12 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(121): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->table(SMW\SQLStore\QueryEngine\QuerySegment)
#13 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(114): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->segment(SMW\SQLStore\QueryEngine\QuerySegment)
#14 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QueryEngine.php(198): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->process(integer)
#15 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/SQLStore.php(374): SMW\SQLStore\QueryEngine\QueryEngine->getQueryResult(SMWQuery)
#16 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/SQLStore.php(362): SMW\SQLStore\SQLStore->fetchQueryResult(SMWQuery)
#17 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/includes/query/SMW_QueryProcessor.php(338): SMW\SQLStore\SQLStore->getQueryResult(SMWQuery)
#18 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/ParserFunctions/AskParserFunction.php(361): SMWQueryProcessor::getResultFromQuery(SMWQuery, array, integer, integer)
#19 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/ParserFunctions/AskParserFunction.php(196): SMW\ParserFunctions\AskParserFunction->doFetchResultsFromFunctionParameters(array, array)
#20 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/ParserFunctionFactory.php(402): SMW\ParserFunctions\AskParserFunction->parse(array)
#21 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(3816): SMW\ParserFunctionFactory->SMW\{closure}(Parser, string, string)
#22 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(3519): Parser->callParserFunction(PPFrame_Hash, string, array)
#23 /homepages/32/d413138210/htdocs/dict/includes/parser/PPFrame_Hash.php(254): Parser->braceSubstitution(array, PPFrame_Hash)
#24 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(3330): PPFrame_Hash->expand(PPNode_Hash_Tree, integer)
#25 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(1489): Parser->replaceVariables(string)
#26 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(593): Parser->internalParse(string)
#27 /homepages/32/d413138210/htdocs/dict/includes/content/WikitextContent.php(368): Parser->parse(string, Title, ParserOptions, boolean, boolean, NULL)
#28 /homepages/32/d413138210/htdocs/dict/includes/content/AbstractContent.php(555): WikitextContent->fillParserOutput(Title, NULL, ParserOptions, boolean, ParserOutput)
#29 /homepages/32/d413138210/htdocs/dict/includes/Revision/RenderedRevision.php(264): AbstractContent->getParserOutput(Title, NULL, ParserOptions, boolean)
#30 /homepages/32/d413138210/htdocs/dict/includes/Revision/RenderedRevision.php(236): MediaWiki\Revision\RenderedRevision->getSlotParserOutputUncached(WikitextContent, boolean)
#31 /homepages/32/d413138210/htdocs/dict/includes/Revision/RevisionRenderer.php(215): MediaWiki\Revision\RenderedRevision->getSlotParserOutput(string)
#32 /homepages/32/d413138210/htdocs/dict/includes/Revision/RevisionRenderer.php(152): MediaWiki\Revision\RevisionRenderer->combineSlotOutput(MediaWiki\Revision\RenderedRevision, array)
#33 [internal function]: MediaWiki\Revision\RevisionRenderer->MediaWiki\Revision\{closure}(MediaWiki\Revision\RenderedRevision, array)
#34 /homepages/32/d413138210/htdocs/dict/includes/Revision/RenderedRevision.php(198): call_user_func(Closure, MediaWiki\Revision\RenderedRevision, array)
#35 /homepages/32/d413138210/htdocs/dict/includes/Storage/DerivedPageDataUpdater.php(1290): MediaWiki\Revision\RenderedRevision->getRevisionParserOutput()
#36 /homepages/32/d413138210/htdocs/dict/includes/Storage/PageUpdater.php(725): MediaWiki\Storage\DerivedPageDataUpdater->getCanonicalParserOutput()
#37 /homepages/32/d413138210/htdocs/dict/includes/page/WikiPage.php(1930): MediaWiki\Storage\PageUpdater->saveRevision(CommentStoreComment, integer)
#38 /homepages/32/d413138210/htdocs/dict/includes/EditPage.php(2341): WikiPage->doEditContent(WikitextContent, CommentStoreComment, integer, boolean, User, string, array, integer)
#39 /homepages/32/d413138210/htdocs/dict/includes/EditPage.php(1617): EditPage->internalAttemptSave(array, boolean)
#40 /homepages/32/d413138210/htdocs/dict/includes/EditPage.php(682): EditPage->attemptSave(array)
#41 /homepages/32/d413138210/htdocs/dict/includes/actions/EditAction.php(55): EditPage->edit()
#42 /homepages/32/d413138210/htdocs/dict/includes/actions/SubmitAction.php(38): EditAction->show()
#43 /homepages/32/d413138210/htdocs/dict/includes/MediaWiki.php(511): SubmitAction->show()
#44 /homepages/32/d413138210/htdocs/dict/includes/MediaWiki.php(302): MediaWiki->performAction(Article, Title)
#45 /homepages/32/d413138210/htdocs/dict/includes/MediaWiki.php(900): MediaWiki->performRequest()
#46 /homepages/32/d413138210/htdocs/dict/includes/MediaWiki.php(527): MediaWiki->main()
#47 /homepages/32/d413138210/htdocs/dict/index.php(44): MediaWiki->run()
#48 {main}

Steps to reproduce

Create some property, for example: Property:HasObject.
Create a subproperty of this property: Property:HasDirectObject [[subproperty of::Property:HasObject]]. Perform an inline query using the parent property:

{{#ask:
[[HasObject::+]]
|?HasObject
}}

This produces the database error. If you remove the subproperty, the same inline query produces no error.

[kghbln]

Thanks for reporting. We had https://github.com/SemanticMediaWiki/SemanticMediaWiki/issues/3606 before with a similar error. Not sure if your are using a multi-database environment.

[kghbln]

Perhaps setting $smwgQTemporaryTablesAutoCommitMode can help the cause.

[robbienam]

Thank you for your prompt reply and suggestions! I appreciate your help.

Thanks for reporting. We had #3606 before with a similar error. Not sure if your are using a multi-database environment.

My installation is on a shared hosting service. Should I ask them about this error? My mediawiki install itself uses only one database.

I looked at the #3606 issue page but it was beyond my technical understanding.

Perhaps setting $smwgQTemporaryTablesAutoCommitMode can help the cause.

I tried setting:

$smwgQTemporaryTablesAutoCommitMode = true;

Under this new setting a different error was generated by the same #ask query. See backtrace below:

[XuHSkkrQOn0AAIgW8VoAAAAc] /dict/index.php?title=Test_query&action=submit Wikimedia\Rdbms\DBUnexpectedError from line 4069 of /homepages/32/d413138210/htdocs/dict/includes/libs/rdbms/database/Database.php: SMW\MediaWiki\Connection\Database::query: expected mass commit of all peer transactions (DBO_TRX set)

Backtrace:

#0 /homepages/32/d413138210/htdocs/dict/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->commit(string)
#1 /homepages/32/d413138210/htdocs/dict/includes/libs/rdbms/database/DBConnRef.php(643): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#2 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/MediaWiki/Connection/Database.php(357): Wikimedia\Rdbms\DBConnRef->commit(string)
#3 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/TableBuilder/TemporaryTableBuilder.php(59): SMW\MediaWiki\Connection\Database->query(string, string, boolean)
#4 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/HierarchyTempTableBuilder.php(111): SMW\SQLStore\TableBuilder\TemporaryTableBuilder->create(string)
#5 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(354): SMW\SQLStore\QueryEngine\HierarchyTempTableBuilder->fillTempTable(string, string, string, integer)
#6 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(131): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->hierarchy(SMW\SQLStore\QueryEngine\QuerySegment)
#7 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(145): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->segment(SMW\SQLStore\QueryEngine\QuerySegment)
#8 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(121): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->table(SMW\SQLStore\QueryEngine\QuerySegment)
#9 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(145): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->segment(SMW\SQLStore\QueryEngine\QuerySegment)
#10 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(121): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->table(SMW\SQLStore\QueryEngine\QuerySegment)
#11 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(114): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->segment(SMW\SQLStore\QueryEngine\QuerySegment)
#12 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QueryEngine.php(198): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->process(integer)
#13 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/SQLStore.php(374): SMW\SQLStore\QueryEngine\QueryEngine->getQueryResult(SMWQuery)
#14 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/SQLStore/SQLStore.php(362): SMW\SQLStore\SQLStore->fetchQueryResult(SMWQuery)
#15 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/includes/query/SMW_QueryProcessor.php(338): SMW\SQLStore\SQLStore->getQueryResult(SMWQuery)
#16 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/ParserFunctions/AskParserFunction.php(361): SMWQueryProcessor::getResultFromQuery(SMWQuery, array, integer, integer)
#17 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/ParserFunctions/AskParserFunction.php(196): SMW\ParserFunctions\AskParserFunction->doFetchResultsFromFunctionParameters(array, array)
#18 /homepages/32/d413138210/htdocs/dict/extensions/SemanticMediaWiki/src/ParserFunctionFactory.php(402): SMW\ParserFunctions\AskParserFunction->parse(array)
#19 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(3816): SMW\ParserFunctionFactory->SMW\{closure}(Parser, string, string)
#20 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(3519): Parser->callParserFunction(PPFrame_Hash, string, array)
#21 /homepages/32/d413138210/htdocs/dict/includes/parser/PPFrame_Hash.php(254): Parser->braceSubstitution(array, PPFrame_Hash)
#22 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(3330): PPFrame_Hash->expand(PPNode_Hash_Tree, integer)
#23 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(1489): Parser->replaceVariables(string)
#24 /homepages/32/d413138210/htdocs/dict/includes/parser/Parser.php(593): Parser->internalParse(string)
#25 /homepages/32/d413138210/htdocs/dict/includes/content/WikitextContent.php(368): Parser->parse(string, Title, ParserOptions, boolean, boolean, NULL)
#26 /homepages/32/d413138210/htdocs/dict/includes/content/AbstractContent.php(555): WikitextContent->fillParserOutput(Title, NULL, ParserOptions, boolean, ParserOutput)
#27 /homepages/32/d413138210/htdocs/dict/includes/Revision/RenderedRevision.php(264): AbstractContent->getParserOutput(Title, NULL, ParserOptions, boolean)
#28 /homepages/32/d413138210/htdocs/dict/includes/Revision/RenderedRevision.php(236): MediaWiki\Revision\RenderedRevision->getSlotParserOutputUncached(WikitextContent, boolean)
#29 /homepages/32/d413138210/htdocs/dict/includes/Revision/RevisionRenderer.php(215): MediaWiki\Revision\RenderedRevision->getSlotParserOutput(string)
#30 /homepages/32/d413138210/htdocs/dict/includes/Revision/RevisionRenderer.php(152): MediaWiki\Revision\RevisionRenderer->combineSlotOutput(MediaWiki\Revision\RenderedRevision, array)
#31 [internal function]: MediaWiki\Revision\RevisionRenderer->MediaWiki\Revision\{closure}(MediaWiki\Revision\RenderedRevision, array)
#32 /homepages/32/d413138210/htdocs/dict/includes/Revision/RenderedRevision.php(198): call_user_func(Closure, MediaWiki\Revision\RenderedRevision, array)
#33 /homepages/32/d413138210/htdocs/dict/includes/Storage/DerivedPageDataUpdater.php(1290): MediaWiki\Revision\RenderedRevision->getRevisionParserOutput()
#34 /homepages/32/d413138210/htdocs/dict/includes/Storage/PageUpdater.php(725): MediaWiki\Storage\DerivedPageDataUpdater->getCanonicalParserOutput()
#35 /homepages/32/d413138210/htdocs/dict/includes/page/WikiPage.php(1930): MediaWiki\Storage\PageUpdater->saveRevision(CommentStoreComment, integer)
#36 /homepages/32/d413138210/htdocs/dict/includes/EditPage.php(2341): WikiPage->doEditContent(WikitextContent, CommentStoreComment, integer, boolean, User, string, array, integer)
#37 /homepages/32/d413138210/htdocs/dict/includes/EditPage.php(1617): EditPage->internalAttemptSave(array, boolean)
#38 /homepages/32/d413138210/htdocs/dict/includes/EditPage.php(682): EditPage->attemptSave(array)
#39 /homepages/32/d413138210/htdocs/dict/includes/actions/EditAction.php(55): EditPage->edit()
#40 /homepages/32/d413138210/htdocs/dict/includes/actions/SubmitAction.php(38): EditAction->show()
#41 /homepages/32/d413138210/htdocs/dict/includes/MediaWiki.php(511): SubmitAction->show()
#42 /homepages/32/d413138210/htdocs/dict/includes/MediaWiki.php(302): MediaWiki->performAction(Article, Title)
#43 /homepages/32/d413138210/htdocs/dict/includes/MediaWiki.php(900): MediaWiki->performRequest()
#44 /homepages/32/d413138210/htdocs/dict/includes/MediaWiki.php(527): MediaWiki->main()
#45 /homepages/32/d413138210/htdocs/dict/index.php(44): MediaWiki->run()
#46 {main}

[kghbln]

My installation is on a shared hosting service. Should I ask them about this error? My mediawiki install itself uses only one database.

It could very well be that they have a setup in the background which causes pain. I am not sure if they will come up with a valuable response.

I tried setting:

Thanks for trying.

I tried to replicate on wiki using SMWSQLStore2 but was not able to replicate. This may confirm some setup issue at your end.

Let's wait for a developer to share some thoughts on this.

[akuckartz]

Can you upgrade to the latest version of SMW ? Do the errors still occur then ?