dashboard 流量图中的表不存在

【 TiDB 使用环境】生产环境
【 TiDB 版本】v7.1.3
【遇到的问题:问题现象及影响】
dashborad 流量图中显示在狂读,但是监控显示的表 table_8920 在库中不存在,请问是什么情况?

这个已经是老的表了,表经历过 truncate 或 drop,现在的元数据已经找不到了对应的表了,可以看下当时的 8920 对应的是那个 table_id,通过 set tidb_snapshot='2024-07-08 19:58:12' ,然后再查当时的 information_schema.tables 表 table_id=8920 的表

基本可以确定没有建过这张表

那您先按我提供的方法查下确定下表名,然后使用表名,在 dashboard 的日志搜索功能中搜索下试试

这个图中显示的是按 region 显示的,table_8920 也是 start_key 去解出来的,所以当表比较小的时候,一个 region 可能涉及多个表,真正流量大的表可能不是这个 8920 表。

当前一直在查,但是information_schema.tables 表中没有id为8920的表

查之前这个执行了吗

没设置,因为当前还一直有查询,而不是当前已经被删除,所以设置快照点是不是没有什么意义

当前时间早超过10分钟,估计也查不到了。我也想知道,10分钟前删除的表,知道表ID,怎么反查表名?

为啥没意义,您现在不就是想知道 id 为 8920 的表是哪张表吗?truncate 是会改变表的 id 的(当然其他操作也可能)

MySQL [test]> select * from information_schema.tables where table_name='tt2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | TIDB_TABLE_ID | TIDB_ROW_ID_SHARDING_INFO | TIDB_PK_TYPE | TIDB_PLACEMENT_POLICY_NAME |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| def           | test         | tt2        | BASE TABLE | InnoDB |      10 | Compact    |          0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2024-07-05 10:50:51 | NULL        | NULL       | utf8mb4_bin     |     NULL |                |               |          1312 | NOT_SHARDED               | NONCLUSTERED | NULL                       |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
1 row in set (20.28 sec)

MySQL [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-07-09 13:35:56 |
+---------------------+
1 row in set (0.00 sec)

MySQL [test]> truncate table tt2;
Query OK, 0 rows affected (0.54 sec)

MySQL [test]> select * from information_schema.tables where table_name='tt2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | TIDB_TABLE_ID | TIDB_ROW_ID_SHARDING_INFO | TIDB_PK_TYPE | TIDB_PLACEMENT_POLICY_NAME |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| def           | test         | tt2        | BASE TABLE | InnoDB |      10 | Compact    |          0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2024-07-05 10:50:51 | NULL        | NULL       | utf8mb4_bin     |     NULL |                |               |          1314 | NOT_SHARDED               | NONCLUSTERED | NULL                       |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
1 row in set (0.01 sec)

MySQL [test]> show variables like '%snap%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| tidb_enable_analyze_snapshot | OFF   |
| tidb_snapshot                |       |
+------------------------------+-------+
2 rows in set (0.00 sec)

MySQL [test]> set tidb_snapshot='2024-07-09 13:35:56';
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> select * from information_schema.tables where table_name='tt2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | TIDB_TABLE_ID | TIDB_ROW_ID_SHARDING_INFO | TIDB_PK_TYPE | TIDB_PLACEMENT_POLICY_NAME |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
| def           | test         | tt2        | BASE TABLE | InnoDB |      10 | Compact    |          0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2024-07-05 10:50:51 | NULL        | NULL       | utf8mb4_bin     |     NULL |                |               |          1312 | NOT_SHARDED               | NONCLUSTERED | NULL                       |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+---------------+---------------------------+--------------+----------------------------+
1 row in set (0.00 sec)

MySQL [test]> set tidb_snapshot='';
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> admin show ddl jobs where table_name='tt2';
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE       | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME         | START_TIME          | END_TIME            | STATE  |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
|   1315 | test    | tt2        | truncate table | public       |         2 |     1312 |         0 | 2024-07-09 13:36:10 | 2024-07-09 13:36:10 | 2024-07-09 13:36:10 | synced |
|   1313 | test    | tt2        | truncate table | public       |         2 |     1310 |         0 | 2024-07-09 13:34:42 | 2024-07-09 13:34:43 | 2024-07-09 13:34:43 | synced |
|   1311 | test    | tt2        | truncate table | public       |         2 |     1308 |         0 | 2024-07-09 13:34:41 | 2024-07-09 13:34:41 | 2024-07-09 13:34:41 | synced |
|   1309 | test    | tt2        | truncate table | public       |         2 |     1306 |         0 | 2024-07-09 13:34:38 | 2024-07-09 13:34:39 | 2024-07-09 13:34:39 | synced |
|   1307 | test    | tt2        | create table   | public       |         2 |     1306 |         0 | 2024-07-05 10:50:51 | 2024-07-05 10:50:51 | 2024-07-05 10:50:51 | synced |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
5 rows in set (0.01 sec)

admin show ddl jobs 可以看到 table id ,tidb 日志里也会记录 table id,这两个存都都比较久

1 个赞

您的意思我明白,但前提是我们要查的表发生了drop或者truncate等造成table_id变化的操作,如果在GC有效时间内,我闪回可以查到删除之前的数据信息。

我现在的场景是这张表一直持续有流量,而且GC也能正常推进,假设一张表被truncate或者drop了,你过了GC的时间,也不可能还有人能查到他吧(如果有人查,GC是不会推进的)

你看看是这个情况不

请教一下,这种情况要如何确认?

拿 dashboard 上边的那个 start_key ,用下面的 SQL 查一下

select * from INFORMATION_SCHEMA.tikv_region_status where lower(start_key)='6e00000000000000f8';```

可能是表没有创建。

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。