WL#11974: Remove MyISAM as the engine used for on-disk internal temporary tables
The internal_tmp_disk_storage_engine system variable determines which storage engine the server uses to manage on-disk internal temporary tables. Permitted values are INNODB (the default) and MYISAM. Starting from 8.0, The TempTable storage engine handles overflow to disk and we will remove the internal_tmp_disk_storage_engine option. https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
F1 'internal_tmp_disk_storage_engine' system variable is removed. F2 MTR tests which use 'internal_tmp_disk_storage_engine' system variable are removed. F3 InnoDB engine is always used for on-disk internal temporary tables. F4 MTR tests which require MyISAM on-disk tmp table are removed.
There are several cases, explicit and implicit, when MySQL uses on-disk tmp tables.
Explicit cases: --- 1. 'big_tables' system variable. If the variable is set to 'true', on-disk tmp tables are always used.
2. SQL_BIG_RESULT modifier. Behavior is similar to 'big_tables' variable. MySQL directly uses disk-based temporary tables if they are created. ---
Implicit cases: --- 1. Bootstrap mode.
There is a comment in the use_tmp_disk_storage_engine() function:
/* During bootstrap, the heap engine is not available, so we force using
disk storage engine. This is especially hit when creating a I_S system view definition with a UNION in it. */
2. Information_schema tables.
A comment from setup_tmp_table_handler() function:
/* For information_schema tables we use the Heap engine because we do not allow user-created TempTable tables and even though information_schema tables are not user-created, an ingenious user may execute: CREATE TABLE myowntemptabletable LIKE information_schema.some; */
According to this comment HEAP engine is always used for I_S tables and if there are BLOB fields, the tables is automatically created as on-disk table. See use_tmp_disk_storage_engine().
3. HEAP engine is used as default internal in-memory engine.
HEAP table is converted to on-disk table in case of exceeding the memory limit.
4. Create a temporary table to weed out duplicate rowid combinations,
see create_duplicate_weedout_tmp_table() function.
This WL removes the use of MyISAM internal tmp tables only. As a consequence system variable 'internal_tmp_disk_storage_engine' will be removed and InnoDB tmp tables will be used if on-disk tables are required.