Bug #88377 Wrong CREATE_TIME and UPDATE_TIME for partitioned table
Submitted: 6 Nov 2017 21:54 Modified: 11 Mar 13:33
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5, 5.6, 5.7, 8.0, 5.7.20 OS:Any
Assigned to: CPU Architecture:Any

[6 Nov 2017 21:54] Sveta Smirnova
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
[7 Nov 2017 5:32] Umesh Shastry
Hello Sveta,

Thank you for the report and test case.
Verified as described with 5.7.20, 8.0.4 builds.

Thanks,
Umesh
[11 Mar 13:33] Sveta Smirnova
This should be fixed in 5.7.23 according to "Partitioning: For a partitioned table, partition update time could be incorrect after rebuilding the table or restarting the server. (Bug #27073100)" At least I do not see this bug anymore in the latest versions.