WL#11974: Remove MyISAM as the engine used for on-disk internal temporary tables

Affects: Server-8.0 — Status: Complete

Description
Requirements
Dependent Tasks
High Level Architecture
Low Level Design

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.

WL#11452: Support for BLOBs in temptable engine

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.