Page MenuHomePhabricator

Special:BrowseData causes error on MySQL 5.7 slave
Closed, ResolvedPublic

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
Seb35 added a comment.Jan 16 2020, 5:58 PM

It seems this bug was fixed by a55cea48c64a (17 December 2018), at least Special:BrowseData does not more show us the internal error with a fresh SemanticDrilldown (with a master-only database, no slaves). Can someone confirm?

Seb35 added a comment.Mar 10 2020, 5:48 PM

And there is a spin-off in MW 1.34 with an exception on the same page saying Cannot write; target role is DB_REPLICA from Wikimedia\Rdbms\Database::executeQuery().

This was fixed in rESDDb4d8b42bf10b.

@TK-999 can you confirm the issue is fixed (with appropriate patches depending on your current MW version)?

Aklapper closed this task as Resolved.Mar 22 2020, 1:29 AM
Aklapper removed a project: Patch-For-Review.

No reply, hence assuming this is fixed as per previous comments. Please reopen if it is not. Thanks!