We have updated the minimum version T161232: Bump MediaWiki's minimum supported MySQL Version to 5.5.8 and this could possibly be related to T34217: Cleanup MySQL 4 related remnants from core if the issues date back to that old version.

I would argue, from a DBA prespective, that using temporary tables for reads by SMW is the bug, and that not allowing them is a actually a feature. I don't handle any installations of Mediawiki with SMW, so I cannot argue anything about that. However, I would argue against any change allowing the creation of temporary tables in read only for the sanity of anyone using mediawiki in a replicated environment with STATEMENT based replication.

This is not my decision at all, but I wanted to give an explanation of my perspective.

In T202245#4513710, @jcrespo wrote:

I would argue against any change allowing the creation of temporary tables in read only for the sanity of anyone using mediawiki in a replicated environment with STATEMENT based replication.

I've only done a small amount of dba on replicated databases, so your perspective is helpful, @jcrespo.

Is my understanding that during "STATEMENT based replication" every SQL statement is copied to the replica (including "CREATE TEMPORARY..." statements) correct? Is the objection that this would create problems? Is there no way to stop this some of these statements from being copied?

Is there no way to stop this some of these statements from being copied?

The real solution is to stop using temp tables, migrating to ROW but (e.g. T109179) is not always feasible or practical. This is a specific example of leaks due to temp. tables we suffered: T173472 I don't think mediawiki can not support STATEMENT at the time.

There are too many issues to list all of them (this is a VERY large topic, so really cannot give a comprehensive summary), I hope you can see the main themes when doing searches like https://www.google.com/search?q=temporary+tables+replication+mysql (replication breakage, replication lag, data drift, consistency issues during normal operation and on crash -which makes backups more complicated, etc.- sorry I cannot be more specific. I don't feel strongly about anything of this, but I would be very scared to maintain explicit temporary tables on more than 1 server, so I wanted just to add some input on my experiences about the topic.

I am doing bunch of wiki updates on non-replicated database servers and it is rather disruptive that pages using SMW queries start failing with exceptions during the update because I set $wgReadOnly for web requests during the upgrade.

To reiterate, I believe this is a MediaWiki-extensions-Semantic-MediaWiki bug, not a Wikimedia-Rdbms one, so no one is working on the db side of things (but again, I only give here a non-canonical answer, not in charge of that component myself).

Original reported requested "more explanation than just this comment.", and I gave that. That doesn't mean your issue is invalid, @Nikerabbit, but I would consider resolving this and opening a new one with an appropiate workaround people would be ok with, rather than just a revert, like an additional, independent variable for allowing temporary tables to get more visibility and attention.

Just trying to help you to move forward. 0:-)

SMW is short on developers and I think avoiding temporary tables would be a lot of work if possible at all.

Did I understand correctly, that creating a configuration variable such as $wgAllowTemporaryTablesInReadOnly with appropriate warnings about replicated environments could be an acceptable solution? If so, I don't think that requires a new task, we can re-use this one.