Description:
when creating a temp table that would have an invalid storage engine, the fallback is MyISAM, rather than the value of default_tmp_storage_engine which would be expected. that's probably just hardcoded somewhere in very old code, and not noticed, as it's not a terribly common case.
How to repeat:
for example, a monitoring script that pulls snapshots of events_statements_summary_by_digest, which has engine "PERFORMANCE_SCHEMA" - not a valid choice to create.
show global variables like 'default%engine';
/*
default_storage_engine InnoDB
default_tmp_storage_engine InnoDB
*/
show create table performance_schema.events_statements_summary_by_digest;
/*
CREATE TABLE `events_statements_summary_by_digest` (
`SCHEMA_NAME` varchar(64) DEFAULT NULL,
...
`LAST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
*/
create temporary table t like performance_schema.events_statements_summary_by_digest;
show create table t;
/*
CREATE TEMPORARY TABLE `t` (
`SCHEMA_NAME` varchar(64) DEFAULT NULL,
...
`LAST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
*/
Suggested fix:
dig out wherever this is in the code (just have a nice long grep for string literal 'myisam' if need be) and have it respect the much newer variable setting.
