Bug #90208 option 'default(_tmp)_storage_engine' does not work as expected

• Submitted: 24 Mar 2018 14:59
• Modified: 27 Mar 2018 6:16
• Reporter: Coref Lava
• Status: Verified
• Category: MySQL Server: Options
• Severity: S2 (Serious)
• Version: 5.7.20, 5.7.21, 5.6.39
• OS: Any
• CPU Architecture: Any
• Tags: default_storage_engine, engine, Options, temporary

[24 Mar 2018 14:59] Coref Lava

Description:
option `default_storage_engine` is designed for non-temporary tables, and option `default_tmp_storage_engine` is designed for temporary tables.

The bug is, when create query assigned with explicit 'engine=default', non-temporary table creation will use option `default_tmp_storage_engine`, and temporary table creation is applied with `default_storage_engine`.

How to repeat:
```sql
set @@session.default_storage_engine = 'INNODB';
set @@session.default_tmp_storage_engine = 'MEMORY';

create table t1 (i int) engine='default';
create temporary table t2(i int) engine='default';
create table t3 (i int);
create temporary table t4 (i int);
```

Table t1 and t4 will use engine=memory, while t2 and t3 will use engine=innodb

Suggested fix:
in sql/handler.cc:

plugin_ref ha_resolve_by_name(THD *thd, const LEX_STRING *name, bool is_temp_table) {
...
    return is_temp_table ? ha_default_temp_plugin(thd) : ha_default_plugin(thd);
...
}

[27 Mar 2018 6:16] MySQL Verification Team

Hello Coref,

Thank you for the report.

Thanks,
Umesh