MySQL系统变量lower_case_table_names使用问题

背景

在MySQL中，表是和操作系统中的文件对应的，而文件名在有的操作系统下是区分大小写的（比如linux），有的是不区分大小写（比如Windows），表名与文件名的大小写对应关系，MySQL 是通过lower_case_Table_names 这个变量来控制的。

这个变量的有效取值是0，1，2，按照官方文档的解释：

0表示，表在文件系统存储的时候，对应的文件名是按建表时指定的大小写存的，MySQL 内部对表名的比较也是区分大小写的；

1表示，表在文件系统存储的时候，对应的文件名都小写的，MySQL 内部对表名的比较是转成小写的，即不区分大小写；

2表示，表在文件系统存储的时候，对应的文件名是按建表时指定的大小写存的，但是 MySQL 内部对表名的比较是转成小写的，即不区分大小写。

0适用于区分大小写的系统，1都适用，2适用于不区分大小写的系统。

如果在开始使用MySQL选定了一个合适的值后，就不要改变，不然的话在之后使用中就会出现问题。

1 问题描述

这里给出一个在使用过程中改变lower_case_table_names导致drop database失败的案例。

首先使lower_case_table_names = 0情况下启动mysqld，并执行以下的语句：

create database test;

use test;

create table ta(id int not null);

create table TA(id int not null);

create table sb(id int not null);

create table SB(id int not null);

查看数据目录下的表文件

ls test

db.opt ta.frm ta.ibd TA.frm TA.ibd sb.frm sb.ibd SB.frm SB.ibd

修改lower_case_table_names=1，重启mysqld服务，执行以下语句：

drop database test;

ERROR 1010 (HY000): Error dropping database (can't rmdir './test', errno: 39)

show tables;

+----------------+

| Tables_in_test |

+----------------+

| SB |

| TR |

| sb |

| tr |

+----------------+

查看数据文件目录：

ls test

TA.frm SB.frm

查看binlog

show binlog events in 'mysql-bin.000031';

mysql-bin.000031 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= '69d61745-2457-11e9-a0e4-fa163ee76280:152' |

mysql-bin.000031 | 259 | Query | 1 | 373 | use `test`; DROP TABLE IF EXISTS `tr`,`sb`,`sb`,`tr`

备机报错：

2019-01-30T01:08:54.767468Z 680553 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction '998abe73-4e7d-11e8-aa37-fa163e8f4184:4736461' at master log mysql-bin.006130, end_log_pos 37347792; Error 'Not unique table/alias: 'act_ru_variable'' on query.

因此可以看到，随意修改lower_case_table_names变量的值，会导致备机异常场景。

3 问题分析

drop database语句的代码执行流程如下图。mysqld 在执行 drop database 操作的时候，是调用 mysql_rm_db 这个函数，此函数会先调用mysql_rm_table_no_locks函数删除db下的所有表，然后调用ha_drop_database函数将db删掉。

为了找出对应db下的所有表，mysqld 是通过遍历数据库目录下的文件来做的，具体是用 find_db_tables_and_rm_known_files 这个函数，遍历数据库目录下的所有文件，然后构造出要 drop的table列表，然而在构造删除列表过程中，会有这样一个判断:

if (lower_case_table_names)

table_list->table_name_length= my_casedn_str(files_charset_info,

const_cast(table_list->table_name));

这段代码的含义是如果lower_case_table_names非0的话，就把 table_name 转成小写的，这样TA和TB的table_name被转化为ta和tb，这样生成的 table_list 中的对应的表是 ta,ta,tb,tb。之后拿着这样的 table_list 通过 mysql_rm_table_no_locks 一个个删表，这样就只把ta,tb 给删了，所以数据目录中的TA.ibd和TA.frm还有TB.ibd和TB.frm文件就没有被删除。

ha_drop_database函数主要作用是删除db库。其代码逻辑是首先会检测是否table_list为空，如果不为空，表明还有table没有被删除，就会执行row_drop_database_for_mysql函数，进行二次删除表操作。主要函数如下：

while ((table_name = dict_get_first_table_name_in_db(name)))

{

err = row_drop_table_for_mysql(table_name, trx, TRUE);

}

dict_get_first_table_name_in_db函数遍历数据目录查找存在的文件并获取文件名称，这块不会对文件名进行大小写处理，因此table_name为TA,TB。然后执行row_drop_table_for_mysql函数删除掉大写表的相关数据和数据文件，不会删除.frm文件，.frm文件是通过mysql_file_delete函数删除的，因此导致看到的现象为大写名字的表结构文件没有被删除。

在list表都删除完后，调用rm_dir_w_symlink来删除db目录，此时test目录下残留TA和TB表对应的表结构文件，这个函数会调用系统的 rmdir 函数，而当目录非空的时候，rmdir是执行失败的。所以我们看到最终的错误提示 Error dropping database (can't rmdir './db1', errno: 39)

如果binlog打开，会将执行语句记录binlog。记录binlog有两种情况：

之前的drop database成功，则记录在binlog中的语句为执行语句，

drop database database_name

如果失败，则记录在binlog中的语句为drop table if exists table_name,table_name1..语句。相应的代码如下：

所以记录到binlog中的语句为DROP TABLE IF EXISTS `tr`,`sb`,`sb`,`tr`，此语句中包含重复表名，因此在备机回放binlog时执行该语句就会报错。

4 建议

不要轻易的改变lower_case_table_names的值，如果真要改的话，要先检查下已有的表是否有大小写的问题，保证目前的表名和要改的模式是一致的，比如从区分大小写改为不区分大小写，那就不应该有大写表存在，如果有的话，要先把大写表rename成小写的，如果本来有共存同名的大写表和小写表，就要想办法去掉一个。

应用不要依赖于 mysql 的表名转换机制，应用里的sql语句应该和表名一致，在不区分大小写的时候，应用里对同一个表的使用，不要既有大写表名，也有小写表名。

同时，mysql8.0中对该参数进行了修改，禁止用户对该参数进行修改，官网说明如下：

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。