0
2
2
0
专栏/.../

tidb变更大小写敏感问题的总结

 xxxxxxxx  发表于  2023-06-05

1 背景

近期,我们线上的tidb集群就遇到一个变更忽略大小写的需求,本来以为一个改表工单就解决了,但是业务反馈工单完成后,大小写仍旧敏感,本文就来总结一下这类需求可能遇到的问题以及如何避坑,仅供参考。

本文演示的实验环境是tidb 4.0.13版本。

tidb集群默认是大小写敏感,如果初始化集群的时候没有启用相关参数【new_collations_enabled_on_first_bootstrap】,后期想变更为忽略大小写就十分麻烦,可能还存在一定的风险。

2、问题复现

(1)准备测试环境

mysql> use tidbtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t(id int not null primary key,name varchar(50) not null default '',age int not null default 0,unique key uk_name(name),key idx_age(age));
Query OK, 0 rows affected (0.07 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL DEFAULT '',
  `age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

mysql> 

mysql> insert into t values(1,'a',10),(2,'A',11);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.00 sec)

mysql> 

测试表【t】id是主键字段,name是唯一索引字段,因为是大小写敏感,所以'a' 和 'A'是可以写进去的,唯一索引也是为了验证大小写敏感这一性质。

(2)修改排序集

mysql> alter table t modify `name` varchar(50) not null COLLATE utf8mb4_general_ci DEFAULT '';
Query OK, 0 rows affected (0.07 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> select * from t where name = 'a';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> select * from t where name = 'A';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | A    |  11 |
+----+------+-----+
1 row in set (0.01 sec)

mysql> 

可以看到,修改排序集操作成功了,但是从实际测试看没有生效。

3、测试

经过查阅官方文档后发现是因为tidb从4.0开始支持collation规则但是默认是关闭的,对应的参数是new_collation_enabled。该参数在6.0版本开始默认开启。下面将该参数调整为True试试。

该参数对应的配置文件参数是 new_collations_enabled_on_first_bootstrap,要求是新集群才生效,即旧集群启用这个参数是无法启用collation规则,需要直接更改tidb系统的参数才生效。

(1)验证

mysql> select * from mysql.tidb where VARIABLE_NAME = 'new_collation_enabled';
+-----------------------+----------------+----------------------------------------------------+
| VARIABLE_NAME         | VARIABLE_VALUE | COMMENT                                            |
+-----------------------+----------------+----------------------------------------------------+
| new_collation_enabled | False          | If the new collations are enabled. Do not edit it. |
+-----------------------+----------------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> update mysql.tidb set VARIABLE_VALUE = 'True' where VARIABLE_NAME = 'new_collation_enabled' limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mysql.tidb where VARIABLE_NAME = 'new_collation_enabled';
+-----------------------+----------------+----------------------------------------------------+
| VARIABLE_NAME         | VARIABLE_VALUE | COMMENT                                            |
+-----------------------+----------------+----------------------------------------------------+
| new_collation_enabled | True           | If the new collations are enabled. Do not edit it. |
+-----------------------+----------------+----------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from t where name = 'a';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> select * from t where name = 'A';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | A    |  11 |
+----+------+-----+
1 row in set (0.01 sec)

mysql> 

可以看到调整为True后还是不生效,原因是这个参数需要重启tidb组件才能生效。

tiup cluster reload cluster_name -R tidb

注意这里用的是reload。需要注意,这种情况下在线上一定不能reload或者重启,要不然容易引发故障,如下测试,查询会失效。

重启以后再次查看发现一个诡异的事情,之前的查询条件不管是使用小写还是大写现在都没法查询到数据,但是数据真实的存在表里,于是乎就想着走强制索引试试。

mysql> select * from mysql.tidb where VARIABLE_NAME = 'new_collation_enabled';
+-----------------------+----------------+----------------------------------------------------+
| VARIABLE_NAME         | VARIABLE_VALUE | COMMENT                                            |
+-----------------------+----------------+----------------------------------------------------+
| new_collation_enabled | True           | If the new collations are enabled. Do not edit it. |
+-----------------------+----------------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> use tidbtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t where name = 'a';
Empty set (0.00 sec)

mysql> select * from t where name = 'A';
Empty set (0.00 sec)

mysql> select * from t;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.00 sec)

mysql>

如果name字段不带索引,这里的查询是可以查到数据的,而且是符合预期,即大小写不再敏感。

下面使用强制索引试试

mysql> select * from t where name = 'a';
Empty set (0.00 sec)

mysql> select * from t force index(idx_age) where name = 'a';
Empty set (0.00 sec)

mysql> desc select * from t force index(idx_age) where name = 'a';
+-------------+---------+------+------------------------------+---------------+
| id          | estRows | task | access object                | operator info |
+-------------+---------+------+------------------------------+---------------+
| Point_Get_1 | 1.00    | root | table:t, index:uk_name(name) |               |
+-------------+---------+------+------------------------------+---------------+
1 row in set (0.00 sec)

mysql> desc select * from t  where name = 'a';
+-------------+---------+------+------------------------------+---------------+
| id          | estRows | task | access object                | operator info |
+-------------+---------+------+------------------------------+---------------+
| Point_Get_1 | 1.00    | root | table:t, index:uk_name(name) |               |
+-------------+---------+------+------------------------------+---------------+
1 row in set (0.00 sec)

mysql>

可以看到强制索引并不能解决问题,通过执行计划发现还是走了uk_name这个索引。

起初以为跟这个表的数据量少有关系,后来追加了10000行测试数据发现结果还是一样。

后来尝试通过模糊查询发现只有将'%a'或者'%A'才能查到数据,且大小写已经不敏感。

mysql> select * from t where name like '%a';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.01 sec)

mysql> select * from t where name like '%a%';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.02 sec)

mysql> select * from t where name like '%A';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.02 sec)

mysql> select * from t where name like '%A%';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.02 sec)

mysql> select id,concat('-',name,'-') name, age from t where name like '%a%';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | -a-  |  10 |
|  2 | -A-  |  11 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> 

从测试结果猜测可能是uk_name这个索引的问题,忽略大小写且在唯一约束条件下,'a'和'A'能同时存在,这显然不合理,于是乎就想着重建这个索引。

mysql> alter table t drop index uk_name;
Query OK, 0 rows affected (0.26 sec)

mysql> select * from t where name = 'A';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.01 sec)

mysql> select * from t where name = 'a';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.02 sec)

mysql> desc select * from t where name = 'A';
+-------------------------+---------+-----------+---------------+--------------------------------+
| id                      | estRows | task      | access object | operator info                  |
+-------------------------+---------+-----------+---------------+--------------------------------+
| TableReader_7           | 1.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 1.00    | cop[tikv] |               | eq(tidbtest.t.name, "A")       |
|   └─TableFullScan_5     | 2.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+---------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> 

可以看到删除uk_name这个索引后,查询就正常了。

mysql> alter table t add unique index uk_name(name);
ERROR 1062 (23000): Duplicate entry 'a' for key 'uk_name'
mysql> alter table t add index idx_name(name);
Query OK, 0 rows affected (0.26 sec)

mysql> select * from t where name = 'a';
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | A    |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from t where name = 'A';
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | A    |
+----+------+
2 rows in set (0.01 sec)

mysql> 

因'a','A'是重复数据,所以没法加唯一索引,加一个普通二级索引。

4、解决方案

通过测试发现,这类需求比较棘手,那能不能解决呢?答案当然是肯定的,只是成本问题,下面提供两个方案,仅供参考。

(1)直接在原集群进行操作

这个方案适合小表的场景,主要考虑点是操作过程中对目标字段需要重建索引(如果有索引的话),所以要能容忍全表扫描带来的性能问题。

mysql> use tidbtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t where name = 'A';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | A    |  11 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> select * from t where name = 'a';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> alter table t modify `name` varchar(50) not null COLLATE utf8mb4_general_ci DEFAULT '';
ERROR 8200 (HY000): Unsupported modifying collation of column 'name' from 'utf8mb4_bin' to 'utf8mb4_general_ci' when index is defined on it.
mysql> 
/*目标字段没有索引直接就能修改成功*/

如果目标字段是索引字段,则无法直接修改排序集,需要删除索引后再修改。

mysql> alter table t drop index uk_name;
Query OK, 0 rows affected (0.25 sec)

mysql> alter table t modify `name` varchar(50) not null COLLATE utf8mb4_general_ci DEFAULT '';
Query OK, 0 rows affected (0.07 sec)

mysql> select * from t where name = 'a';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.02 sec)

mysql> alter table t add index idx_name(name); 
Query OK, 0 rows affected (0.85 sec)
/*如果目标字段没有索引就忽略即可*/
/*如果目标字段是唯一索引则需要考虑重复值问题,有可能加不上*/

mysql> select * from t where name = 'a';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  10 |
|  2 | A    |  11 |
+----+------+-----+
2 rows in set (0.00 sec)

mysql> 

可以看到,大小写已经不敏感了。

(2)集群迁移(物理备份)

这个方案适合目标表是大表的场景。具体操作略,下面简单提供一个思路:

  • 部署好一套新集群
  • 利用br备份原集群
  • 利用br将备份恢复到新集群
  • 使用ticdc进行增量同步
  • 待数据同步后,对新集群的目标表配置大小写不敏感
  • 协同业务进行验证
  • 切换流量,将业务流量切到新集群

另外,待数据同步后,是在新集群变更大小写敏感参数,因为新集群这时候未接受业务流量,所以重启,重建索引等可以放心操作,具体操作流程请参考第一个方案。

(3)集群迁移(逻辑备份)

这个方案适合目标表是大表的场景,这也是官方建议的方案。具体操作略,下面简单提供一个思路:

  • 部署好一套新集群(初始化的时候直接启用new_collations_enabled_on_first_bootstrap参数)
  • 利用逻辑备份工具备份旧集群
  • 利用逻辑备份工具恢复数据到新集群
  • 使用ticdc进行增量同步
  • 待数据同步后,协同业务进行验证
  • 切换流量,将业务流量切到新集群

这种架构可能存在一个问题,上游是大小写敏感,但是下游大小写不敏感,对于唯一索引字段可能会导致同步失败。

5、总结

官方建议有需要忽略大小写需求的话,在初始化集群的时候就启用该参数,已经初始化的集群,无法通过更改该配置项打开或关闭新的 collation 框架,但是我实际测试过程中发现直接变更【mysql.tidb】表的new_collation_enabled参数是能关闭或者开启的,而且也能符合预期。

(1)如果目标字段是非索引字段,直接变更字段排序集以及系统参数即可,待合适时间重新加载tidb组件即可生效。

(2)如果目标表是小表,全表扫描的查询成本与使用目标字段的索引的查询成本相差不大,也可直接参考第一点进行操作。

(3)如果目标表是大表,则需要慎重一些,需要考虑通过集群迁移的方式进行变更此类需求。

(4)如果集群已经启用 collation 框架,则不能对索引字段进行变更排序集操作,否则会报错。

new_collation_enabled=True

mysql> alter table t2 modify `name` varchar(50) not null COLLATE utf8mb4_general_ci DEFAULT '';
ERROR 8200 (HY000): Unsupported modifying collation of column 'name' from 'utf8mb4_bin' to 'utf8mb4_general_ci' when index is defined on it.
mysql> 

综上所述,tidb集群想要变更大小写敏感问题需要考虑的风险点较多,需要用户根据自身环境进行充分评估测试,本文涉及的测试及方案仅供参考,非线上环境的操作建议。

如果存在上下游关系的架构中,一定要评估好风险,避免因调整了tidb的大小写敏感问题导致上下游同步异常。

6、写在最后

本文对tidb集群变更大小写敏感问题做了一下分析及总结,总体来说此类需求是存在一定的风险的,各公司的业务场景也不一样,需求也不同,还可能碰上其他未知的问题,本文所有内容仅供参考。

0
2
2
0

版权声明:本文为 TiDB 社区用户原创文章,遵循 CC BY-NC-SA 4.0 版权协议,转载请附上原文出处链接和本声明。

评论
暂无评论