admin check doesn't fail when index not correct

[fubinzh]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. TiDB new_collations_enabled_on_first_bootstrap = true
2. Prepare lightning test data

   [root@centos76_vm CR]# cat test.t3.000000000.csv
   "id","a","b"
   1,"aaa","bbb"
   2,"bbb","cdsfds"
   [root@centos76_vm CR]# cat test.t3-schema.sql
   /*!40101 SET NAMES binary*/;
   CREATE TABLE `t3` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `a` varchar(20) DEFAULT NULL,
   `b` varchar(20) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `idx_a` (`a`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=60003;

3. Use lightning to import the data to TiDB, using a user which doesn't have access to mysql.tidb (in this case, lightning import should fail, but now we can still import data to bug: https://github.com/pingcap/tidb/issues/31088.)

   
   mysql> SHOW GRANTS FOR 'testuser';
   +-----------------------------------------------------------------------------------------------+
   | Grants for testuser@%                                                                         |
   +-----------------------------------------------------------------------------------------------+
   | GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON lightning_metadata.* TO 'testuser'@'%' |
   | GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON test.* TO 'testuser'@'%'               |
   +-----------------------------------------------------------------------------------------------+

[root@centos76_vm v5.4.0-nightly-20211229]# ./tidb-lightning -tidb-user testuser --tidb-host=172.16.6.217 --tidb-port=4000 --backend=local --sorted-kv-dir=/home/sorted-kvs -d 'local:///home/tools_test_data/CR' Verbose debug logs will be written to /tmp/lightning.log.2021-12-29T17.59.46+0800

+---+----------------------------------------------+-------------+--------+ | # | CHECK ITEM | TYPE | PASSED | +---+----------------------------------------------+-------------+--------+ | 1 | Source csv files size is proper | performance | true | +---+----------------------------------------------+-------------+--------+ | 2 | checkpoints are valid | critical | true | +---+----------------------------------------------+-------------+--------+ | 3 | table schemas are valid | critical | true | +---+----------------------------------------------+-------------+--------+ | 4 | Cluster is available | critical | true | +---+----------------------------------------------+-------------+--------+ | 5 | Lightning has the correct storage permission | critical | true | +---+----------------------------------------------+-------------+--------+ tidb lightning exit [root@centos76_vm v5.4.0-nightly-20211229]# grep WARN /tmp/lightning.log.2021-12-29T17.59.46+0800 [2021/12/29 17:59:46.318 +08:00] [WARN] [util.go:129] ["obtain new collation enabled failed with no retry"] [query="SELECT variable_value FROM mysql.tidb WHERE variable_name = 'new_collation_enabled'"] [retryCnt=0] [error="Error 1142: SELECT command denied to user 'testuser'@'%' for table 'tidb'"]

4. Check the data consistency

### 2. What did you expect to see? (Required)
The index for the imported table is not consistent with the data, admin check table/index should fail

### 3. What did you see instead (Required)
Admin check table/index doesn't report error.

mysql> show create table t3; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t3 | CREATE TABLE t3 ( id int(11) NOT NULL AUTO_INCREMENT, a varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, b varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (id) /T![clustered_index] CLUSTERED /, KEY idx_a (a) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=60004 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

mysql> select * from t3; +----+------+--------+ | id | a | b | +----+------+--------+ | 1 | aaa | bbb | | 2 | bbb | cdsfds | +----+------+--------+ 2 rows in set (0.00 sec)

mysql> select a from t3 where a = 'aaa'; Empty set (0.00 sec)

mysql> select a from t3 where a = 'bbb'; Empty set (0.00 sec)

mysql> admin check table t3; Query OK, 0 rows affected (0.01 sec)

mysql> admin check index t3 idx_a; Query OK, 0 rows affected (0.01 sec)

### 4. What is your TiDB version? (Required)
mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.4.0-alpha-510-gabb658291
Edition: Community
Git Commit Hash: abb65829101d2f19b69b4e5a4dd0471dbed05915
Git Branch: master
UTC Build Time: 2021-12-27 07:08:01
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[wjhuang2016]

In this case, the index is still consistent with data, so it won't report errors.

[fubinzh]

In this case, the index is still consistent with data, so it won't report errors.

@wjhuang2016 , Why I can't get data using the below queries?

mysql> select a from t3 where a = 'aaa';
Empty set (0.00 sec)

mysql> select a from t3 where a = 'bbb';
Empty set (0.00 sec)

[wjhuang2016]

Even if the index uses the wrong encoding config(new collation enabled), the data can be decoded correctly. But if we construct a lookup key, then it doesn't meet the index. The problem is that, admin check table doesn't check the index's encoding.

[yudongusa]

@bb7133 assign this to you for now.

[bb7133]

I think it can be an 'enhancement' for ADMIN CHECK TABLE: the current logic is kind of by-design.