Page MenuHomePhabricator

Special:BrowseData causes error on MySQL 5.7 slave
Open, Needs TriagePublic

Description

When using Semantic Drilldown extension on a multi-database MediaWiki installation with MySQL 5.7 slave, visiting Special:BrowseData causes the following database error:

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 semantic_drilldown_values ( id INT NOT NULL )
Function: SDBrowseDataPage:createTempTable
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. (geo-db-smw-slave.query.consul)

This is because of restrictions on GTID-based replication in newer versions of MySQL. A workaround is to use auto-commit mode for temporary table operations, as implemented in Semantic MediaWiki itself.

Event Timeline

TK-999 created this task.Sep 4 2017, 10:24 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 4 2017, 10:24 AM

Change 375785 had a related patch set uploaded (by TK-999; owner: TK-999):
[mediawiki/extensions/SemanticDrilldown@master] Use auto-commit for temporary table operations

https://gerrit.wikimedia.org/r/375785

Change 375785 merged by Yaron Koren:
[mediawiki/extensions/SemanticDrilldown@master] Use auto-commit for temporary table operations

https://gerrit.wikimedia.org/r/375785

MW 1.31.0
SMW 2.5.6
PHP 7.0.30
MySQL 5.7.23
SD 2.0.2 (2252e92)
When I try to access Special:BrowseData, I get error message :

TemporaryTableManager::queryWithAutoCommit: Expected mass commit of all peer transactions (DBO_TRX set)
Seb35 added a subscriber: Seb35.Aug 30 2018, 3:18 PM

I observed it can be "fixed" by adding a second parameter to the function commit in TemporaryTableManager::queryWithAutoCommit, but I do not master the transactions in MySQL nor the way it is implemented in MediaWiki, so perhaps I’m wrong about this fix.

$this->databaseConnection->commit( __METHOD__, Database::FLUSHING_ALL_PEERS );
Morlins added a subscriber: Morlins.Nov 8 2018, 4:00 PM