Test fails with "Can't reopen table" error.
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	daniel
	Jun 22 2020, 2:55 PM

Description

This is a tracking ticket for problems and workarounds related to the following issue:

MySQL and older versions of MariaDB (and possibly other database systems) do not allow a temporary table to be used more than once in a query. Any attempt to self-join a temporary table, or to use a temporary table in a join as well as a sub-query, or two sub-queries, will result in "Error 1137: Can't reopen table".
MediaWiki's framework for phpunit tests "shadows" all database tables with temporary tables.
Thus, all query that use any table more than once will fail in phpunit tests.

There is no real fix as long as we support databases that have this problem. This ticket provides a central place for discussing the issue and best practices around it. It exists so people running into this issue will find useful information and can benefit from past experience. This ticket also serves as a marker that relevant code can link to, allowing such code to be easily identified and located.

Workaround for this include:

Skip the test under MySQL
Force the use of real tables instead of temporary tables
Copy the relevant (temporary) table and use the copy for one of the usages in the query

Details

	Subject	Repo	Branch	Lines +/-
	Tests: Only skip tests related to the temp table reopen issue in MySQL	mediawiki/core	master	+18 -59
	Fix "can't reopen tables" test error for Special:BrokenRedirects	mediawiki/core	master	+4 -4

Customize query in gerrit

Related Objects

Mentioned In: T259123: mediawiki/core REL1_34 CI failure due to DB/PHP errors
Mentioned Here: T248191: Can't reopen table in wikidb-unittest_ (from SpecialPageFatalTest)

Event Timeline

daniel created this task.Jun 22 2020, 2:55 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJun 22 2020, 2:55 PM

daniel updated the task description. (Show Details)Jun 22 2020, 2:57 PM

daniel updated the task description. (Show Details)

daniel awarded a token.Jun 22 2020, 3:00 PM

Change 607070 had a related patch set uploaded (by Daniel Kinzler; owner: Daniel Kinzler):
[mediawiki/core@master] Track worarounds for "can't reopen tables" error

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

gerritbot added a project: Patch-For-Review.Jun 22 2020, 3:36 PM

BPirkle triaged this task as Medium priority.Jun 24 2020, 8:19 PM

BPirkle moved this task from Inbox to Future Initiatives/Small Projects on the Platform Engineering board.

Change 607070 merged by jenkins-bot:
[mediawiki/core@master] Fix "can't reopen tables" test error for Special:BrokenRedirects

https://gerrit.wikimedia.org/r/c/mediawiki/core/ /607070

ReleaseTaggerBot added a project: MW-1.35-notes (1.35.0-wmf.40; 2020-07-07).Jun 30 2020, 6:00 PM

Maintenance_bot removed a project: Patch-For-Review.Jul 1 2020, 8:18 PM

Change 617134 had a related patch set uploaded (by Reedy; owner: Daniel Kinzler):
[mediawiki/core@REL1_34] Fix "can't reopen tables" test error for Special:BrokenRedirects

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

gerritbot added a project: Patch-For-Review.Jul 29 2020, 1:08 PM

Reedy mentioned this in T259123: mediawiki/core REL1_34 CI failure due to DB/PHP errors.Jul 29 2020, 1:20 PM

Naike added a project: Platform Engineering Roadmap Decision Making.Oct 9 2020, 12:49 PM

Not Tracking-Neverending per definition. Also, all related Gerrit patches are merged or abandoned.

Naike moved this task from Untriaged to Icebox on the Platform Engineering Roadmap Decision Making board.Nov 16 2020, 3:24 PM

CCicalese_WMF removed a project: Platform Engineering.Feb 24 2021, 11:37 PM

I ran into this problem when writing a big integration test, and I used the workaround number 2 (force the use of real tables instead of temporary tables). There doesn't seem to be a supported way to do it in MediaWikiIntegrationTestCase, so I hacked it like this: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/DiscussionTools/+/771974/17/tests/phpunit/ThreadItemStoreTest.php#20

	public function getCliArg( $offset ) {
		// Work around MySQL bug (T256006)
		if ( $offset === 'use-normal-tables' ) {
			return true;
		}
		return parent::getCliArg( $offset );
	}

The test seems to work fine now, but let me know if this is a bad idea.

Restricted Application added a project: Performance-Team. · View Herald TranscriptJul 21 2022, 2:37 PM

Krinkle removed a project: Performance-Team.Jul 21 2022, 3:28 PM

Krinkle moved this task from Untriaged to Rdbms: MySQL support on the MediaWiki-libs-Rdbms board.

FWIW, the version of mariadb that this is fixed is 10.2 and we all are already on 10.4 and partially 10.6 so it should be okay to drop the workaround for testing mariadb (if the version of mariadb in CI is old, we should bump it anyway) but I don't know how would I feel about breaking it for everyone who is testing under mysql (and there seems to be no plan by mysql to fix this). There are way to skip a test if it's mysql (and not mariadb). Let me give this a try.

Change 928685 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[mediawiki/core@master] Tests: Only skip tests related to the temp table reopen issue in MySQL

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

gerritbot added a project: Patch-For-Review.Jun 9 2023, 12:05 AM

Tests pass ^ CI is on MariaDB 10.3 which has the fix.

In T256006#8916870, @Ladsgroup wrote:

Tests pass ^ CI is on MariaDB 10.3 which has the fix.

Yay!

In T256006#8916870, @Ladsgroup wrote:

Tests pass ^ CI is on MariaDB 10.3 which has the fix.

And MW requires 10.3+, so we can merge it.

Change 928685 merged by jenkins-bot:

[mediawiki/core@master] Tests: Only skip tests related to the temp table reopen issue in MySQL

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

Ladsgroup closed this task as Resolved.Jun 9 2023, 12:48 PM

Ladsgroup claimed this task.

ReleaseTaggerBot added a project: MW-1.41-notes (1.41.0-wmf.13; 2023-06-13).Jun 9 2023, 1:00 PM

Change 935467 had a related patch set uploaded (by Bartosz Dziewoński; author: Bartosz Dziewoński):

[mediawiki/extensions/DiscussionTools@master] ThreadItemStoreTest: Update workaround for T256006

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

Change 935467 merged by jenkins-bot: