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: tidb-lightning import wrong data if user doesn't has right permission #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: abb6582
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.