解决MYSQL Table has no partition for value from column_list的问题
一、问题描述
在新的一年,突然系统运行报错,看描述是数据库分区的问题
HHH000315: Exception executing batch [java.sql.BatchUpdateException: Table has no partition for value from column_list]
二、问题排除与解决
随便百度下,明显是新的一年,数据库表分区未创建
- 首先查询下数据库有哪些表配置了partition,执行下列sql 由此能看到有8个表配置了partition。
select distinct table_name from (
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
p.PARTITION_NAME,
p.PARTITION_METHOD
FROM
information_schema.TABLES t
JOIN
information_schema.PARTITIONS p
ON
t.TABLE_SCHEMA = p.TABLE_SCHEMA
AND t.TABLE_NAME = p.TABLE_NAME
WHERE
t.TABLE_TYPE = 'BASE TABLE'
) a where a.TABLE_SCHEMA = 'skmesprod' and a.PARTITION_NAME is not null
2. 随后每个表手动添加新的时间分区
ALTER TABLE machinehist ADD PARTITION (
PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB
)
添加完成后,系统恢复。