Problem with Index After br/Lightning

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

Original topic: br/Lightning之后索引出问题了

| username: TiDBer_yangxi

【TiDB Usage Environment】Production Environment / Testing / Poc
【TiDB Version】
【Reproduction Path】What operations were performed that led to the issue
【Encountered Issue: Issue Phenomenon and Impact】
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

From 7.1.1, br to 7.5.1, the data has not changed.
SELECT * from rel_user_organization where org_id =‘1ce8c8c12ac24a57bb07734df5ec5120’; No data found
SELECT * from rel_user_organization where trim(org_id) =‘1ce8c8c12ac24a57bb07734df5ec5120’; Data found
select length(org_id) from rel_user_organization; The result is 32
Very strange

mysql> select distinct length(org_id) from rel_user_organization;
±---------------+
| length(org_id) |
±---------------+
| 32 |
±---------------+
1 row in set (0.00 sec)

The database creation statements are all create database xxx DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

| username: tidb狂热爱好者 | Original post link

BR cannot perform physical data operations across versions.

| username: TiDBer_yangxi | Original post link

Can I use BR version 7.1.1 for everything? I see the data has been written and can be queried, but this string is very strange.

| username: tidb狂热爱好者 | Original post link

You can use SQL to restore it with Lightning.

| username: TiDBer_QYr0vohO | Original post link

Ensure that the upstream and downstream TiDB versions are consistent.

| username: h5n1 | Original post link

The image is not visible. Please provide the text you need translated.

| username: DBAER | Original post link

The versions must be consistent for physical backup and recovery.

| username: TiDBer_yangxi | Original post link

I just destroyed version 7.5.1 and rebuilt version 7.1.1, but the same issue persists. Now both clusters are consistent. I tested it and found that there is a problem with string queries, but not with numbers.

| username: WalterWj | Original post link

Are the new collation configurations of the two clusters the same? :thinking:

| username: TiDBer_yangxi | Original post link

The same, both are true. This thing is checked during BR and must be consistent.

| username: TiDBer_yangxi | Original post link

I found that using “create table ** like *;” and then inserting data allows you to query it.

| username: TiDBer_yangxi | Original post link

I upgraded both clusters to 7.1.1 and used Lightning to synchronize the data, but problems still occurred.

It feels like some parameter is affecting string comparison. Both clusters have new_collation_enabled set to False.

mysql> SELECT VARIABLE_VALUE FROM mysql.tidb WHERE VARIABLE_NAME='new_collation_enabled';
+----------------+
| VARIABLE_VALUE |
+----------------+
| False          |
+----------------+
| username: TiDB_C罗 | Original post link

Calculate the MD5 value.

| username: TiDBer_yangxi | Original post link

Consistent

| username: TiDB_C罗 | Original post link

Try importing it to MySQL.

| username: erwadba | Original post link

Run admin check table <table_name> to see if there are any errors.
Also, check if the execution plans for the two SQL queries are consistent.

| username: zhanggame1 | Original post link

Use the hex function to check if the hexadecimal strings are the same. If they are not the same, then there is an issue with the stored values. If they are the same, then there is an issue with the character set and sorting.

| username: zhaokede | Original post link

Are the upstream and downstream strings exactly the same? Is the character set still utf8mb4?

| username: TiDBer_yangxi | Original post link

Check is fine, the execution plans of the two clusters are the same, but the execution plans of the two SQLs are different. One uses the index as range, and the other as eq.

| username: TiDBer_yangxi | Original post link

Coming from Lightning, the character set must be the same, and the table creation statements are all the same.