Bug #91377 Can't Initialize MySQl if internal_tmp_disk_storage_engine is set to MYISAM
Submitted: 22 Jun 2018 14:11 Modified: 1 Feb 2019 10:04
Reporter: Hugo Dubois Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Options Severity:S2 (Serious)
Version:8.0.11 OS:CentOS (7.2)
Assigned to: CPU Architecture:x86
Tags: regression

[22 Jun 2018 14:11] Hugo Dubois
When you initialize MySQL with internal_tmp_disk_storage_engine set to "MYISAM" (because of this other bug https://bugs.mysql.com/bug.php?id=82556), MySQL fail with this error:

2018-06-22T15:58:30.366556+01:00 1 [Note] [MY-010007] [Server] Installed data dictionary with version 80011
mysqld: 'Table function' requires @@internal_tmp_disk_storage_engine=InnoDB
2018-06-22T15:58:30.396464+01:00 0 [ERROR] [MY-010943] [Server] System views initialization failed.
2018-06-22T15:58:30.396482+01:00 0 [ERROR] [MY-010119] [Server] Aborting

How to repeat:
-> /usr/sbin/mysqld   --datadir=/DATA --basedir=/usr --internal-tmp-disk-storage-engine='MYISAM' --initialize-insecure --user=mysql
2018-06-22T14:04:15.334736Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.11) initializing of server in progress as process 17254
mysqld: 'Table function' requires @@internal_tmp_disk_storage_engine=InnoDB
2018-06-22T14:04:16.318015Z 0 [ERROR] [MY-010943] [Server] System views initialization failed.
2018-06-22T14:04:16.318031Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-06-22T14:04:18.036850Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.11)  MySQL Community Server - GPL.

Suggested fix:
Use internal_tmp_disk_storage_engine=InnoDB automatically when MySQL is bootstrapped ? or use other fix
[23 Jun 2018 7:02] MySQL Verification Team
Hello Dubois,

Thank you for the report!

[30 Aug 2018 9:11] Øystein Grøvlen
Posted by developer:
Note that in 8.0.11, Bug#82556 should only be a problem if you use BLOB/TEXT/GEOMETRY/JSON.
Hence, if the internal temporary tables are only using other data types, there should be no need to set internal_tmp_disk_storage_engine anymore.
In upcoming MySQL 8.0.13, new TempTable engine will also support large objects.  (See https://mysqlserverteam.com/mysql-8-0-support-for-blobs-in-temptable-engine/)
Then, there should be no need to set internal_tmp_disk_storage_engine at all.
[1 Feb 2019 10:04] Jon Stephens
Fixed in MySQL 8.0.16 by WL#11974, which removes the use of MyISAM for internal temporary tables as well as the internal_tmp_disk_storage_engine system variable.