Semanticmediawiki: Temporary tables created on replica db not allowed

[hexmode]

Setup and configuration

• SMW version: 3.1.5 (a30b089)
• MW version: 1.34.0
• PHP version: 7.3.19 (fpm-fcgi)
• DB system and version: Percona XtraDB Cluster 5.7.23-23-57-log

Stack trace

Just set up exception logging and noticed the following:
```
2020-07-13 14:15:13 /w/api.php WikimediaRdbmsDBReadOnlyRoleError fro
m line 968 of .../includes/libs/rdbms/database/Database.php: Write operations are not allowed on repli
ca database connections

0 .../includes/libs/rdbms/database/Database.php(1186): WikimediaRdbmsDatabase->assertIsWritableMast

er()

1 .../includes/libs/rdbms/database/Database.php(1148): WikimediaRdbmsDatabase->executeQuery(string,

string, integer)

2 .../includes/libs/rdbms/database/DBConnRef.php(68): WikimediaRdbmsDatabase->query(string, string,

integer)

3 .../includes/libs/rdbms/database/DBConnRef.php(292): WikimediaRdbmsDBConnRef->__call(string, arra

y)

4 .../extensions/SemanticMediaWiki/src/MediaWiki/Connection/Database.php(364): WikimediaRdbmsDBConnRef->

query(string, string, integer)
#5 .../extensions/SemanticMediaWiki/src/SQLStore/TableBuilder/TemporaryTableBuilder.php(59): SMWMediaWiki
ConnectionDatabase->query(string, string, boolean)

6 .../extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(234): SMWSQLSto

reTableBuilderTemporaryTableBuilder->create(string)

7 .../extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(127): SMWSQLSto

reQueryEngineQuerySegmentListProcessor->disjunction(SMWSQLStoreQueryEngineQuerySegment)

8 .../extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(145): SMWSQLSto

reQueryEngineQuerySegmentListProcessor->segment(SMWSQLStoreQueryEngineQuerySegment)

9 .../extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(121): SMWSQLSto

reQueryEngineQuerySegmentListProcessor->table(SMWSQLStoreQueryEngineQuerySegment)

10 .../extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QuerySegmentListProcessor.php(114): SMWSQLSt

oreQueryEngineQuerySegmentListProcessor->segment(SMWSQLStoreQueryEngineQuerySegment)

11 .../extensions/SemanticMediaWiki/src/SQLStore/QueryEngine/QueryEngine.php(198): SMWSQLStoreQueryEngin

eQuerySegmentListProcessor->process(integer)

12 .../extensions/SemanticMediaWiki/src/SQLStore/SQLStore.php(374): SMWSQLStoreQueryEngineQueryEngine->

getQueryResult(SMWQuery)
...
...

[kghbln]

Isn't this something for https://www.semantic-mediawiki.org/wiki/Help:$smwgLocalConnectionConf ?

[hexmode]

Oops! I was going to put that setting here, but here is what I have:

    $smwgLocalConnectionConf = [
        'mw.db' => [
            'read' => DB_REPLICA,
            'write' => DB_MASTER
        ],
        # needed because we have the to keep the queries on the same db
        'mw.db.queryengine' => [
            'read' => DB_MASTER,
            'write' => DB_MASTER,
        ] ];

[kghbln]

Thanks for posting this. Appears that this Pecuniar XtraDB database engine is a real pain in the you know where. I suspect that this case cannot be supported, at least out of the box, since SMW needs temporary tables for generating results which at the same time cannot be replicated. I may however be mistaken here.

[hexmode]

You could be right, but, on further examination, I see that this error occurs when the site is read-only, which means that there would be no DB_MASTER above, only DB_REPLICA.

FWIW, Percona is just MySQL with clustering (and some more strictness about semantics), so this probably affects MySQL as well.

[kghbln]

so this probably affects MySQL as well.

Lucky us that it this did not show for regular MySQL yet.

[mszabo-wikia]

This is not a Percona/MySQL issue. MediaWiki has been blocking write operations (incl. temporary table writes) on a read-only replica connection for some time now.

The check occurs here: https://github.com/wikimedia/mediawiki/blob/master/includes/libs/rdbms/database/Database.php#L1258

[mszabo-wikia]

Looks like this changed in https://phabricator.wikimedia.org/T183265, back in 2017.

[mszabo-wikia]

https://phabricator.wikimedia.org/T259362, https://gerrit.wikimedia.org/r/c/mediawiki/core/+/617741 opened.

[mszabo-wikia]

@hexmode out of curiosity, are you using a replicated setup here, or is it a single-node environment?

[JeroenDeDauw]

So this is an issue from something like MW 1.32 (guess) till 1.34 but will be fine on MW 1.35+. Not sure something should be done on SMW side then. @hexmode you could always apply @mszabo-wikia his patch.

[mszabo-wikia]

Yeah. The patch may need a little tweaking for < 1.35 but the gist of it should remain the same.

[JeroenDeDauw]

@mszabo-wikia thanks so much for digging to the root of this and fixing it.

Closing the issue as there does not seem to be anything left to do on SMW side.