统计局宣传工作总结 网站建设,怎么做网站的广告,深圳企业模板网站建设,怎么做qq空间支付网站作者#xff1a; sustyle 原文来源#xff1a; https://tidb.net/blog/2a72bc13 1 背景 近期#xff0c;我们线上的tidb集群就遇到一个变更忽略大小写的需求#xff0c;本来以为一个改表工单就解决了#xff0c;但是业务反馈工单完成后#xff0c;大小写仍旧敏感 sustyle 原文来源 https://tidb.net/blog/2a72bc13 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 -ADatabase 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)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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: 0mysql 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)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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: 0mysql 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 -ADatabase 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 -ADatabase 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_enabledTrue 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集群变更大小写敏感问题做了一下分析及总结总体来说此类需求是存在一定的风险的各公司的业务场景也不一样需求也不同还可能碰上其他未知的问题本文所有内容仅供参考。
