定时任务运行期间经常出现 information schema is changed during the excution of the statement

[heming (何明)]

Bug 反馈
清晰准确地描述您发现的问题，提供任何可能复现问题的步骤有助于研发同学及时处理问题
【 Bug 的影响】
tidb 5.2.2
定时任务运行期间经常出现 information schema is changed during the excution of the statement
导致业务中断 。
【可能的问题复现步骤】

【看到的非预期行为】

【期望看到的行为】

【相关组件及具体版本】

【其他背景信息或者截图】
如集群拓扑，系统和内核版本，应用 app 信息等；如果问题跟 SQL 有关，请提供 SQL 语句和相关表的 Schema 信息；如果节点日志存在关键报错，请提供相关节点的日志内容或文件；如果一些业务敏感信息不便提供，请留下联系方式，我们与您私下沟通。

[spc_monkey (carry@pingcap.com)]

这个咱们执行 DDL 的频率如何，应该有一个参数可以调整的，一会我发一下

[heming (何明)]

不确定 ，有比较多的按天 truncate 分区 的操作 。 基本上每个ddl 都会 导致 tidb_table_id 变化 。

[spc_monkey (carry@pingcap.com)]

整个集群一天的 DDL 数量是多少啊

[heming (何明)]

昨天凌晨3点 到现在 500多个DDL

[spc_monkey (carry@pingcap.com)]

https://docs.pingcap.com/zh/tidb/stable/sql-faq/#触发-information-schema-is-changed-错误的原因？

[spc_monkey (carry@pingcap.com)]

@heming 上面是官网提到的该报错的几种可能原因，咱们的原因大概率是 1和 4

[heming (何明)]

触发 Information schema is changed 错误的原因？

TiDB 在执行 SQL 语句时，会使用当时的 schema 来处理该 SQL 语句，而且 TiDB 支持在线异步变更 DDL。那么，在执行 DML 的时候可能有 DDL 语句也在执行，而你需要确保每个 SQL 语句在同一个 schema 上执行。所以当执行 DML 时，遇到正在执行中的 DDL 操作就可能会报 Information schema is changed 的错误。为了避免太多的 DML 语句报错，已做了一些优化。

现在会报此错的可能原因如下（只有第一个报错原因与表有关）：

• 执行的 DML 语句中涉及的表和集群中正在执行的 DDL 的表有相同的，那么这个 DML 语句就会报此错。
• 这个 DML 执行时间很久，而这段时间内执行了很多 DDL 语句，导致中间 schema 版本变更次数超过 1024 （此为默认值，可以通过 tidb_max_delta_schema_count 变量修改）。
• 接受 DML 请求的 TiDB 长时间不能加载到 schema information （TiDB 与 PD 或 TiKV 之间的网络连接故障等会导致此问题），而这段时间内执行了很多 DDL 语句，导致中间 schema 版本变更次数超过 100。
• TiDB 重启后执行第一个 DDL 操作前，执行 DML 操作，并且在执行过程中遇到了第 1 个 DDL 操作（即在执行第 1 个 DDL 操作前，启动该 DML 对应的事务，且在该 DDL 变更第一个 schema 版本后，提交该 DML 对应的事务），那么这个 DML 会报此错。

注意：

• 目前 TiDB 未缓存所有的 schema 版本信息。
• 对于每个 DDL 操作， schema 版本变更的数量与对应 schema state 变更的次数一致。
• 不同的 DDL 操作版本变更次数不一样。例如， create table 操作会有 1 次 schema 版本变更； add column 操作有 4 次 schema 版本变更。

[heming (何明)]

定时任务一小时执行一次 ，只有每天凌晨5-6点之间会报错 ，其他时间执行却正常 。

[db: yixintui_operate]select * from information_schema.DDL_JOBS where table_name = ‘agent_material_report_cost’ limit 50;
±-------±-----------------±---------------------------±------------------------------±-------------±----------±---------±----------±--------------------±--------------------±-------±----------------------------------------------------------------------------------------------------------+
| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | START_TIME | END_TIME | STATE | QUERY |
±-------±-----------------±---------------------------±------------------------------±-------------±----------±---------±----------±--------------------±--------------------±-------±----------------------------------------------------------------------------------------------------------+
| 38231 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 12:01:20 | 2021-12-16 12:01:21 | synced | |
| 38229 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 12:01:06 | 2021-12-16 12:01:06 | synced | alter table Agent_material_report_cost truncate partition p20211216 |
| 38219 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 11:29:20 | 2021-12-16 11:29:20 | synced | |
| 38216 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 11:29:05 | 2021-12-16 11:29:05 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38208 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 11:11:20 | 2021-12-16 11:11:20 | synced | |
| 38206 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 11:11:01 | 2021-12-16 11:11:01 | synced | alter table Agent_material_report_cost truncate partition p20211216 |
| 38186 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 09:30:30 | 2021-12-16 09:30:31 | synced | |
| 38184 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 09:30:13 | 2021-12-16 09:30:14 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38177 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 09:11:20 | 2021-12-16 09:11:21 | synced | |
| 38175 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 09:11:01 | 2021-12-16 09:11:01 | synced | alter table Agent_material_report_cost truncate partition p20211216 |
| 38170 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 08:13:40 | 2021-12-16 08:13:42 | synced | |
| 38168 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 08:13:22 | 2021-12-16 08:13:22 | synced | alter table Agent_material_report_cost truncate partition p20211213 |
| 38157 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 07:27:20 | 2021-12-16 07:27:22 | synced | |
| 38155 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 07:27:02 | 2021-12-16 07:27:02 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38154 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 07:17:50 | 2021-12-16 07:17:52 | synced | |
| 38152 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 07:17:35 | 2021-12-16 07:17:35 | synced | alter table Agent_material_report_cost truncate partition p20211214 |
| 38145 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 07:11:20 | 2021-12-16 07:11:23 | synced | |
| 38143 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 07:11:01 | 2021-12-16 07:11:01 | synced | alter table Agent_material_report_cost truncate partition p20211216 |
| 38128 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 06:37:00 | 2021-12-16 06:37:01 | synced | |
| 38126 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 06:36:47 | 2021-12-16 06:36:47 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38125 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 06:28:30 | 2021-12-16 06:28:30 | synced | |
| 38123 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 06:28:10 | 2021-12-16 06:28:11 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38122 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 06:06:40 | 2021-12-16 06:06:42 | synced | |
| 38120 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 06:06:24 | 2021-12-16 06:06:24 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38119 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 05:57:00 | 2021-12-16 05:57:00 | synced | |
| 38117 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 05:56:46 | 2021-12-16 05:56:46 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38116 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 05:31:20 | 2021-12-16 05:31:23 | synced | |
| 38114 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 05:31:01 | 2021-12-16 05:31:01 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38113 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 05:21:40 | 2021-12-16 05:21:43 | synced | |
| 38111 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 05:21:27 | 2021-12-16 05:21:27 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38104 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 05:11:20 | 2021-12-16 05:11:22 | synced | |
| 38102 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 05:11:01 | 2021-12-16 05:11:01 | synced | alter table Agent_material_report_cost truncate partition p20211216 |
| 38087 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 04:21:30 | 2021-12-16 04:21:32 | synced | |
| 38085 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 04:21:17 | 2021-12-16 04:21:17 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38078 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 04:11:40 | 2021-12-16 04:11:40 | synced | |
| 38076 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 04:11:24 | 2021-12-16 04:11:24 | synced | alter table Agent_material_report_cost truncate partition p20211216 |
| 38058 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 03:19:50 | 2021-12-16 03:19:51 | synced | |
| 38056 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 03:19:30 | 2021-12-16 03:19:31 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 38049 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 03:11:40 | 2021-12-16 03:11:41 | synced | |
| 38047 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 03:11:25 | 2021-12-16 03:11:26 | synced | alter table Agent_material_report_cost truncate partition p20211216 |
| 38014 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 03:00:18 | 2021-12-16 03:02:24 | synced | |
| 37994 | yixintui_operate | agent_material_report_cost | add partition | public | 1382 | 19660 | 0 | 2021-12-16 03:00:07 | 2021-12-16 03:00:12 | synced | alter table Agent_material_report_cost add partition(partition p20211231 VALUES LESS THAN (‘2022-01-01’)) |
| 37964 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 02:23:48 | 2021-12-16 02:23:50 | synced | |
| 37962 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 02:18:38 | 2021-12-16 02:18:38 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 37955 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 02:11:38 | 2021-12-16 02:11:39 | synced | |
| 37953 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 02:11:26 | 2021-12-16 02:11:26 | synced | alter table Agent_material_report_cost truncate partition p20211216 |
| 37938 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 01:17:58 | 2021-12-16 01:17:59 | synced | |
| 37936 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 01:17:46 | 2021-12-16 01:17:46 | synced | alter table Agent_material_report_cost truncate partition p20211215 |
| 37929 | yixintui_operate | agent_material_report_cost | update tiflash replica status | public | 1382 | 19660 | 0 | 2021-12-16 01:11:18 | 2021-12-16 01:11:19 | synced | |
| 37927 | yixintui_operate | agent_material_report_cost | truncate partition | queueing | 1382 | 19660 | 0 | 2021-12-16 01:11:00 | 2021-12-16 01:11:00 | synced | alter table Agent_material_report_cost truncate partition p20211216 |

[spc_monkey (carry@pingcap.com)]

这个建议看 tidbn-server 日志，找到报错的 DML，看看执行的时间（因为报错显示的是 执行期间 ，表的 schema 信息发生了改变）