MySQL "enforce_gtid_consistency=true" will cause a "Error: 1787 When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE ..."

[mwjames]

Setup and configuration

• SMW version: all
• MW version: 1.28.0-alpha
• PHP: 5.6.8
• DB: MySQL 5.6.24-log
• ICU: 54.1

Issue

Adding the following setting to my.ini

gtid_mode=ON
enforce_gtid_consistency=true
log_bin=/xampp/mysql/log/bin_log
log_slave_updates=true

Exception encountered, of type "RuntimeException"
[c8533ce4a56b1eab94b3b9a4] /mw-28-00/index.php/Main_Page RuntimeException from line 249 of ...\extensions\SemanticMediaWiki\src\MediaWiki\Database.php: A database error has occurred. Did you forget to run maintenance/update.php after upgrading? See: https://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script
Query: CREATE TEMPORARY TABLE `t3`( id INT UNSIGNED KEY ) ENGINE=MEMORY
Function: SMW\SQLStore\QueryEngine\QuerySegmentListProcessor::resolveHierarchyForSegment
Error: 1787 When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. (localhost)

#0 ...\includes\db\Database.php(901): DatabaseBase->reportQueryError('When @@GLOBAL.E...', 1787, 'CREATE TEMPORAR...', 'SMW\\SQLStore\\Qu...', false)
#1 ...\extensions\SemanticMediaWiki\src\MediaWiki\Database.php(247): DatabaseBase->query('CREATE TEMPORAR...', 'SMW\\SQLStore\\Qu...', false)
#2 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(303): SMW\MediaWiki\Database->query('CREATE TEMPORAR...', 'SMW\\SQLStore\\Qu...')
#3 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(242): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->resolveHierarchyForSegment(Object(SMW\SQLStore\QueryEngine\QuerySegment))
#4 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(123): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(Object(SMW\SQLStore\QueryEngine\QuerySegment))
#5 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(123): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(Object(SMW\SQLStore\QueryEngine\QuerySegment))
#6 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(181): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(Object(SMW\SQLStore\QueryEngine\QuerySegment))
#7 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(123): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(Object(SMW\SQLStore\QueryEngine\QuerySegment))
#8 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(112): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(Object(SMW\SQLStore\QueryEngine\QuerySegment))
#9 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QueryEngine.php(221): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doExecuteSubqueryJoinDependenciesFor(4)
#10 ...\extensions\SemanticMediaWiki\includes\storage\SQLStore\SMW_SQLStore3.php(402): SMW\SQLStore\QueryEngine\QueryEngine->getQueryResult(Object(SMWQuery))
#11 ...\extensions\SemanticMediaWiki\includes\storage\SQLStore\SMW_SQLStore3.php(392): SMWSQLStore3->fetchQueryResult(Object(SMWQuery))
#12 ...\extensions\SemanticMediaWiki\includes\query\SMW_QueryProcessor.php(455): SMWSQLStore3->getQueryResult(Object(SMWQuery))
#13 ...\extensions\SemanticMediaWiki\includes\parserhooks\AskParserFunction.php(194): SMWQueryProcessor::getResultFromQuery(Object(SMWQuery), Array, 2, 1)
#14 ...\extensions\SemanticMediaWiki\includes\parserhooks\AskParserFunction.php(115): SMW\AskParserFunction->doFetchResultsForRawParameters(Array)
#15 ...\extensions\SemanticMediaWiki\src\ParserFunctionFactory.php(277): SMW\AskParserFunction->parse(Array)
#16 [internal function]: SMW\ParserFunctionFactory->SMW\{closure}(Object(Parser), '[[Property:+]][...', '?Has property d...', '?Has type')
#17 ...\includes\parser\Parser.php(3801): call_user_func_array(Object(Closure), Array)
#18 ...\includes\parser\Parser.php(3536): Parser->callParserFunction(Object(PPFrame_DOM), '#ask', Array)
#19 ...\includes\parser\Preprocessor_DOM.php(1175): Parser->braceSubstitution(Array, Object(PPFrame_DOM))
#20 ...\includes\parser\Parser.php(3350): PPFrame_DOM->expand(Object(PPNode_DOM), 0)
#21 ...\includes\parser\Parser.php(1243): Parser->replaceVariables('{{Welcome}}\n......')
#22 ...\includes\parser\Parser.php(441): Parser->internalParse('{{Welcome}}\n......')
#23 ...\includes\content\WikitextContent.php(331): Parser->parse('{{Welcome}}\n......', Object(Title), Object(ParserOptions), true, true, 7990)
#24 ...\includes\content\AbstractContent.php(497): WikitextContent->fillParserOutput(Object(Title), 7990, Object(ParserOptions), true, Object(ParserOutput))
#25 ...\includes\poolcounter\PoolWorkArticleView.php(140): AbstractContent->getParserOutput(Object(Title), 7990, Object(ParserOptions))
#26 ...\includes\poolcounter\PoolCounterWork.php(123): PoolWorkArticleView->doWork()
#27 ...\includes\page\Article.php(666): PoolCounterWork->execute()
#28 ...\includes\actions\ViewAction.php(44): Article->view()
#29 ...\includes\MediaWiki.php(503): ViewAction->show()
#30 ...\includes\MediaWiki.php(288): MediaWiki->performAction(Object(Article), Object(Title))
#31 ...\includes\MediaWiki.php(745): MediaWiki->performRequest()
#32 ...\includes\MediaWiki.php(519): MediaWiki->main()
#33 ...\index.php(43): MediaWiki->run()
#34 {main}
Backtrace:
#0 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(303): SMW\MediaWiki\Database->query(string, string)
#1 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(242): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->resolveHierarchyForSegment(SMW\SQLStore\QueryEngine\QuerySegment)
#2 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(123): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(SMW\SQLStore\QueryEngine\QuerySegment)
#3 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(123): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(SMW\SQLStore\QueryEngine\QuerySegment)
#4 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(181): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(SMW\SQLStore\QueryEngine\QuerySegment)
#5 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(123): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(SMW\SQLStore\QueryEngine\QuerySegment)
#6 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php(112): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doResolveBySegment(SMW\SQLStore\QueryEngine\QuerySegment)
#7 ...\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QueryEngine.php(221): SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->doExecuteSubqueryJoinDependenciesFor(integer)
#8 ...\extensions\SemanticMediaWiki\includes\storage\SQLStore\SMW_SQLStore3.php(402): SMW\SQLStore\QueryEngine\QueryEngine->getQueryResult(SMWQuery)
#9 ...\extensions\SemanticMediaWiki\includes\storage\SQLStore\SMW_SQLStore3.php(392): SMWSQLStore3->fetchQueryResult(SMWQuery)
#10 ...\extensions\SemanticMediaWiki\includes\query\SMW_QueryProcessor.php(455): SMWSQLStore3->getQueryResult(SMWQuery)
#11 ...\extensions\SemanticMediaWiki\includes\parserhooks\AskParserFunction.php(194): SMWQueryProcessor::getResultFromQuery(SMWQuery, array, integer, integer)
#12 ...\extensions\SemanticMediaWiki\includes\parserhooks\AskParserFunction.php(115): SMW\AskParserFunction->doFetchResultsForRawParameters(array)
#13 ...\extensions\SemanticMediaWiki\src\ParserFunctionFactory.php(277): SMW\AskParserFunction->parse(array)
#14 [internal function]: SMW\ParserFunctionFactory->SMW\{closure}(Parser, string, string, string)
#15 ...\includes\parser\Parser.php(3801): call_user_func_array(Closure, array)
#16 ...\includes\parser\Parser.php(3536): Parser->callParserFunction(PPFrame_DOM, string, array)
#17 ...\includes\parser\Preprocessor_DOM.php(1175): Parser->braceSubstitution(array, PPFrame_DOM)
#18 ...\includes\parser\Parser.php(3350): PPFrame_DOM->expand(PPNode_DOM, integer)
#19 ...\includes\parser\Parser.php(1243): Parser->replaceVariables(string)
#20 ...\includes\parser\Parser.php(441): Parser->internalParse(string)
#21 ...\includes\content\WikitextContent.php(331): Parser->parse(string, Title, ParserOptions, boolean, boolean, integer)
#22 ...\includes\content\AbstractContent.php(497): WikitextContent->fillParserOutput(Title, integer, ParserOptions, boolean, ParserOutput)
#23 ...\includes\poolcounter\PoolWorkArticleView.php(140): AbstractContent->getParserOutput(Title, integer, ParserOptions)
#24 ...\includes\poolcounter\PoolCounterWork.php(123): PoolWorkArticleView->doWork()
#25 ...\includes\page\Article.php(666): PoolCounterWork->execute()
#26 ...\includes\actions\ViewAction.php(44): Article->view()
#27 ...\includes\MediaWiki.php(503): ViewAction->show()
#28 ...\includes\MediaWiki.php(288): MediaWiki->performAction(Article, Title)
#29 ...\includes\MediaWiki.php(745): MediaWiki->performRequest()
#30 ...\includes\MediaWiki.php(519): MediaWiki->main()
#31 ...\index.php(43): MediaWiki->run()
#32 {main}

Caused by

This is caused by MySQL's handling of Global Transaction ID's which states that "... Since only transactionally safe statements can be logged when --enforce-gtid-consistency is enabled, it follows that the operations listed here cannot be used with this option: CREATE TABLE ... SELECT statements CREATE TEMPORARY TABLE statements inside transactions ..."

[0] https://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html