Where is the default case sensitivity of index key names controlled? The defaults are different in versions 6.1 and 6.5

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 索引key默认名称大小写是哪里控制的?6.1和6.5的默认不一样

| username: Soysauce520

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed that led to the issue
[Encountered Issue:]
May I ask, is this controlled by a parameter? The binlog synchronization source database statement does not include the index name.

6.1 When creating a table without specifying an index name, the index name defaults to lowercase.

create table t1 (Id int, key (ID));

show create table result:

CREATE TABLE t1 (

Id int(11) DEFAULT NULL,

KEY id (Id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

6.5 It matches the case of the column name specified after the key.

create table t5 (Id int, key (ID));

show create table result:

CREATE TABLE t5 (

Id int(11) DEFAULT NULL,

KEY ID (Id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Resulting issue: Inconsistent index case, syncdiff comparison reports table structure inconsistency.

| username: TiDBer_小阿飞 | Original post link

The schema of upstream and downstream database tables may differ, for example, the downstream table may only have a subset of the upstream indexes. Inappropriate index selection can lead to increased processing time for one of the databases. When performing table structure validation, only retain indexes that exist in both upstream and downstream (if such indexes do not exist, retain all indexes). On the other hand, some indexes contain columns that are not unique, which may result in a large number of rows having the same index value, leading to uneven chunk division. Sync-diff-inspector prioritizes selecting primary key or unique indexes when choosing indexes, followed by indexes with the lowest duplication rate.

| username: Soysauce520 | Original post link

Understood. Please provide the Chinese text you would like translated into English.

| username: tidb菜鸟一只 | Original post link

I tested it, and it is indeed the case. But isn’t TiDB case-insensitive by default? How come sync-diff-inspector is case-sensitive when comparing?

| username: TiDBer_小阿飞 | Original post link

The official recommendation is to enable this parameter when initializing the cluster if there is a need to ignore case sensitivity. For already initialized clusters, it is not possible to enable or disable the new collation framework by changing this configuration item. However, in my actual tests, I found that directly changing the new_collation_enabled parameter in the [mysql.tidb] table can enable or disable it, and it also meets expectations.
Link: https://www.jianshu.com/p/15eb2c1836e6

| username: Soysauce520 | Original post link

Without adding it, the default is lowercase. I don’t know why the index suddenly changed. At first, I thought it was a binlog synchronization issue. Later, I checked the DDL history and found no problems. I tested the statements and found that the results were different between the two versions.

| username: Soysauce520 | Original post link

Cannot be ignored; ignoring it would cause production to crash. It’s the index names that are different, not the data.

| username: zxgaa | Original post link

This shouldn’t have much of an impact, right? We are also planning to upgrade to 6.5 soon.

| username: Soysauce520 | Original post link

It doesn’t affect usage, just the visual experience.

| username: Fly-bird | Original post link

sync-diff-inspector is case-sensitive.

| username: Soysauce520 | Original post link

Case-sensitive

| username: heiwandou | Original post link

Case-sensitive

| username: Daniel-W | Original post link

The case sensitivity of index names is not controlled by parameters. Table names and database names are case-sensitive (lower_case_table_names), but index names are not.

| username: Soysauce520 | Original post link

There should be some changes made in the code, but I couldn’t find where. Never mind, it doesn’t have a big impact.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.