云数据库RDS

    lower_case_table_names参数使用详解

    背景

    百度云支持更改数据库表的大小写设置参数,该参数修改后需要重启实例生效:

    • lower_case_table_names=0,表示区分大小写,创建的库表会原样大小写保存在磁盘上
    • lower_case_table_names=1,表示不区分大小写,创建库表时MySQL会将所有的库表名转换成小写字母保存在磁盘上,且SQL语句中涉及到库表无论写成大写或者小写,都会被转化成小写进行查询和写入。

    参数设置请参考:百度云RDS参数设置指南

    问题现象

    某百度云RDS用户在控制台发起删除数据库操作后控制台页面一直处于『删除中』的状态,用户发工单授权给百度云售后工程师协助排查,发现数据库仍然存在。

    原因分析

    • 用户通过RDS控制台向数据库发送drop database操作,查看binlog日志并未看到drop database语句,但存在drop table的语句,如下:
    SET TIMESTAMP=1562737970/*!*/;
    DROP TABLE IF EXISTS `tb_02`,`tb_03`,`tb_01`/*!*/;

    因此判断数据库执行drop database命令失败,转而生成drop table操作,由于表名都是小写字母,导致删除含大写字母的表失败。

    • 查询当前数据库lower_case_table_names取值为1(不区分大小写),但是操作系统中的表文件名是包含大写字母的 因此判断用户曾经建表时设置数据库为区分大小写,并且创建了含大写字母的表。后面用户修改lower_case_table_names参数为不区分大小写,这样导致drop database时mysql找不到对应的表。

    解决方案

    得到用户授权后,由百度云工程师登录用户的数据库服务器,删除残留的含大写字母的表空间文件,并且重新执行drop database命令即可删除数据库。

    案例复现

    1. 当前参数环境lower_case_table_names=0(区分大小写),在baidu_dba库下创建三张表,其中一个表名大写、两个表名是小写,SQL语句如下:
    CREATE TABLE TB_01 (
       id int(11) NOT NULL AUTO_INCREMENT,
       name varchar(64) DEFAULT NULL,
       PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE tb_02 (
       id int(11) NOT NULL AUTO_INCREMENT,
       name varchar(64) DEFAULT NULL,
       PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE tb_03 (
       id int(11) NOT NULL AUTO_INCREMENT,
       name varchar(64) DEFAULT NULL,
       PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    创建完成后,显示如下: image.png

    1. 在控制台修改lower_case_table_names=1(不区分大小写),然后重启RDS实例
    2. 在控制台删除数据库:baidu_dba,会发现控制台显示处于删除中状态 image.png
    3. 登陆主实例发现baidu_dba库的确存在,但是该库下中只剩下一张TB_01表,另外两个表tb_02\tb_03被删除了 image.png

    结论建议

    • 参数lower_case_table_names,需要在初始化数据库时明确设置是否区分大小写,不建议在线上环境中随意频繁的更改该参数取值
    • 参数lower_case_table_names设置不区分大小写时,无论数据库创建的库表是否有大写字母,都会被转为小写字母存储在操作系统;因此如果此前有区分大小写时创建的大写库、表都会在不区分大小写参数环境下失效,导致表无法识别
    • 数据库迁移时,需要关注参数lower_case_table_names,确保源库与目标库的取值一致
    • 如果想在已有数据库中修改参数lower_case_table_names的取值,合理的操作步骤如下:
    第一种情况:针对仅表名存在大写字母的情况:
    第1步:当lower_case_tables_name=0时,执行rename table语句将含大写字母的表名改成小写
    第2步:设置lower_case_tables_name=1,重启实例生效
    
    第二种情况:针对库名存在大写字母的情况:
    第1步:当lower_case_tables_name=0时,使用mysqldump导出当前数据库的全部数据,然后删除老的数据库
    第2步:设置lower_case_tables_name=1,重启实例生效
    第3步:导入数据至实例,此时包含大写字母的库名已自动转换为小写
    上一篇
    local_infile参数使用详解
    下一篇
    explicit_defaults_for_timestamp参数使用详解