云数据库RDS

    disabled_storage_engines参数实现表引擎自动转换

    背景

    disabled_storage_engines参数是5.7.8版本新引入的参数,它用来设置在CREATE TABLE时被禁用的存储引擎。 举例:创建新表时禁用MyISAM和MEMORY引擎

    [mysqld]
    disabled_storage_engines="MyISAM,MEMORY"

    问题现象

    借助disabled_storage_engines参数,可以在建表时实现存储引擎自动转换。例如:执行如下建表SQL

    CREATE TABLE tb_01 (
    id int(11) NOT NULL AUTO_INCREMENT,
    age int(11) NOT NULL DEFAULT '0',
    name varchar(64) NOT NULL DEFAULT '',
    PRIMARY KEY (id)
    ) ENGINE=MyISAM;

    创建完成后有warnings返回,并且表的存储引擎自动转换成为InnoDB引擎:

    image.png

    原因分析

    当在sql_mode中不设置NO_ENGINE_SUBSTITUTION,且CREATE TABLE中的ENGINE子句指定的存储引擎被参数disabled_storage_engines禁用,MySQL会把新建表的引擎改为默认存储引擎(InnoDB)。

    解决方案

    按照下面三个步骤设置参数,新建表时可以把指定的存储引擎转换成InnoDB引擎:

    1. 设置被禁用的存储引擎,例如:禁用MyISAM和MEMORY引擎:
    [mysqld]
    disabled_storage_engines="MyISAM,MEMORY"

    image.png

    1. sql_mode设置中不含NO_ENGINE_SUBSTITUTION:
    SET sql_mode='';

    image.png

    1. 设置默认存储引擎是InnoDB:
    SET GLOBAL default_storage_engine=InnoDB;

    image.png

    结论建议

    • 从稳定性和性能多方面考虑,针对MySQL数据库中的存储引擎,推荐使用InnoDB引擎
    • 建议设置disabled_storage_engines参数,把MyISAM、MEMORY等存储引擎禁用,并且能够建表时自动转换成InnoDB引擎
    • MySQL5.7.23之前的版本存在Bug,disabled_storage_engines参数设置后不会实现自动转换,Bug说明如下:
    Bug #27502530
    With the NO_ENGINE_SUBSTITUTION SQL mode disabled, an error (rather than substitution) occurred for CREATE TABLE and ALTER TABLE if the desired engine was disabled using the disabled_storage_engines system variable. 
    • 分区表无法实现自动引擎转换功能,创建被禁用引擎的分区表会报错,原因是MySQL只能对分区表的主表进行引擎转换,但不能对分区表的子表进行转换,导致报错:
    ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL
    上一篇
    sql_mode参数使用详解之NO_ENGINE_SUBSTITUTION
    下一篇
    MySQL之interactive_timeout和wait_timeout