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
- The original bug report against MySQL from 2005: https://bugs.mysql.com/bug.php?id=10327
- The fix in MariaDB from 2016: https://jira.mariadb.org/browse/MDEV-5535
- Discussion of workarounds in the MySQL manual: https://dev.mysql.com/doc/refman/8.0/en/temporary-table-problems.html
- Discussion on stack overflow: https://stackoverflow.com/questions/343402/getting-around-mysql-cant-reopen-table-error