【 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,这两个存都都比较久
您的意思我明白,但前提是我们要查的表发生了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';```
可能是表没有创建。