Description:
information_schema.partitions table contains has wrong CREATE_TIME and UPDATE_TIME for partitioned tables.
How to repeat:
CREATE TABLE t1 (
id INT,
year_col INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999)
);
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, CREATE_TIME, UPDATE_TIME from information_schema.partitions where table_schema='test';
TABLE_SCHEMA TABLE_NAME PARTITION_NAME CREATE_TIME UPDATE_TIME
test t1 p0 2017-11-07 00:47:57 NULL
test t1 p1 2017-11-07 00:47:57 NULL
test t1 p2 2017-11-07 00:47:57 NULL
ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, CREATE_TIME, UPDATE_TIME from information_schema.partitions where table_schema='test';
TABLE_SCHEMA TABLE_NAME PARTITION_NAME CREATE_TIME UPDATE_TIME
test t1 p0 2017-11-07 00:48:08 NULL
test t1 p1 2017-11-07 00:48:08 NULL
test t1 p2 2017-11-07 00:48:08 NULL
test t1 p3 2017-11-07 00:48:08 NULL
insert into t1 values(1, 2000);
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, CREATE_TIME, UPDATE_TIME from information_schema.partitions where table_schema='test';
TABLE_SCHEMA TABLE_NAME PARTITION_NAME CREATE_TIME UPDATE_TIME
test t1 p0 2017-11-07 00:48:08 NULL
test t1 p1 2017-11-07 00:48:08 NULL
test t1 p2 2017-11-07 00:48:08 NULL
test t1 p3 2017-11-07 00:48:08 2017-11-07 00:48:08
delete from t1;
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, CREATE_TIME, UPDATE_TIME from information_schema.partitions where table_schema='test';
TABLE_SCHEMA TABLE_NAME PARTITION_NAME CREATE_TIME UPDATE_TIME
test t1 p0 2017-11-07 00:48:08 NULL
test t1 p1 2017-11-07 00:48:08 NULL
test t1 p2 2017-11-07 00:48:08 NULL
test t1 p3 2017-11-07 00:48:08 2017-11-07 00:48:08
You see what CREATE_TIME updated for all partitions when I added only one.
This can be result of bad fix for https://bugs.mysql.com/bug.php?id=69990
In version 8.0 this gets worse: UPDATE_TIME column is also updated for only partitions:
=====mysql-8.0=====
=====bug=====
CREATE TABLE t1 (
id INT,
year_col INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999)
);
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, CREATE_TIME, UPDATE_TIME from information_schema.partitions where table_schema='test';
TABLE_SCHEMA TABLE_NAME PARTITION_NAME CREATE_TIME UPDATE_TIME
test t1 p0 2017-11-07 00:38:49 NULL
test t1 p1 2017-11-07 00:38:49 NULL
test t1 p2 2017-11-07 00:38:49 NULL
ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, CREATE_TIME, UPDATE_TIME from information_schema.partitions where table_schema='test';
TABLE_SCHEMA TABLE_NAME PARTITION_NAME CREATE_TIME UPDATE_TIME
test t1 p0 2017-11-07 00:38:59 NULL
test t1 p1 2017-11-07 00:38:59 NULL
test t1 p2 2017-11-07 00:38:59 NULL
test t1 p3 2017-11-07 00:38:59 NULL
insert into t1 values(1, 2000);
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, CREATE_TIME, UPDATE_TIME from information_schema.partitions where table_schema='test';
TABLE_SCHEMA TABLE_NAME PARTITION_NAME CREATE_TIME UPDATE_TIME
test t1 p0 2017-11-07 00:38:59 2017-11-07 00:38:59
test t1 p1 2017-11-07 00:38:59 2017-11-07 00:38:59
test t1 p2 2017-11-07 00:38:59 2017-11-07 00:38:59
test t1 p3 2017-11-07 00:38:59 2017-11-07 00:38:59
delete from t1;
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, CREATE_TIME, UPDATE_TIME from information_schema.partitions where table_schema='test';
TABLE_SCHEMA TABLE_NAME PARTITION_NAME CREATE_TIME UPDATE_TIME
test t1 p0 2017-11-07 00:38:59 2017-11-07 00:38:59
test t1 p1 2017-11-07 00:38:59 2017-11-07 00:38:59
test t1 p2 2017-11-07 00:38:59 2017-11-07 00:38:59
test t1 p3 2017-11-07 00:38:59 2017-11-07 00:38:59
drop table t1;
Suggested fix:
Update CREATE_TIME and UPDATE_TIME per partition
