MySQL Bugs: #91377: Can't Initialize MySQl if internal_tmp_disk_storage

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:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	8.0.11	OS:	CentOS (7.2)
Assigned to:		CPU Architecture:	x86
Tags:	regression

Description:
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

Hello Dubois,

Thank you for the report!

Thanks,
Umesh

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.

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.

Closed.