MySQL Bugs: #90219: CREATE_TIME gets updated when table is altered

Bug #90219	CREATE_TIME gets updated when table is altered
Submitted:	26 Mar 2018 17:15	Modified:	28 Mar 2018 1:17
Reporter:	Norman Knupp	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.6/5.7/8.0	OS:	Linux (GNU/Linux)
Assigned to:		CPU Architecture:	x86
Tags:	myisam

Description:
The CREATE_TIME value appears to be updated any time the table meta data is altered (e.g. table comment).  Out of 49 tables in my database, all but 5 have the same values for both CREATE_TIME and UPDATE_TIME.  This renders the CREATE_TIME value almost useless.

How to repeat:
Using phpMyAdmin change the value of Table comments under Operations for a given table and see if the CREATE_TIME get set to NOW().

Suggested fix:
Do not update CREATE_TIME after table is initially created.  The UPDATE_TIME value is already updated when changes occur.

Thank you for the bug report. I think that's expected behavior with alter table, just I was not able to find in the Manual the text that confirm that, lets development to decide that (documentation bug).

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23 Source distribution 2018-MAR-25

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > create table t (id int);
Query OK, 0 rows affected (0,73 sec)

mysql 5.7 > select distinct table_name,update_time,create_time from information_schema.tables where table_schema='test';
+------------+-------------+---------------------+
| table_name | update_time | create_time         |
+------------+-------------+---------------------+
| t          | NULL        | 2018-03-27 21:53:09 |
+------------+-------------+---------------------+
1 row in set (0,00 sec)

mysql 5.7 > exit
Bye
miguel@tikal:~/dbs $ ls -la --time-style=full-iso 5.7/data/test/
total 120
drwxr-x--- 2 miguel miguel  4096 2018-03-27 21:53:09.722182867 -0300 ./
drwxr-x--- 6 miguel miguel  4096 2018-03-27 21:50:24.245520731 -0300 ../
-rw-r----- 1 miguel miguel    65 2018-03-27 21:50:24.245520731 -0300 db.opt
-rw-r----- 1 miguel miguel  8556 2018-03-27 21:53:09.588849539 -0300 t.frm
-rw-r----- 1 miguel miguel 98304 2018-03-27 21:53:11.472182818 -0300 t.ibd

Your MySQL connection id is 5
Server version: 5.7.23 Source distribution 2018-MAR-25

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql 5.7 > alter table t add column g int;
Query OK, 0 rows affected (0,60 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.7 > select distinct table_name,update_time,create_time from information_schema.tables where table_schema='test';
+------------+-------------+---------------------+
| table_name | update_time | create_time         |
+------------+-------------+---------------------+
| t          | NULL        | 2018-03-27 22:02:13 |
+------------+-------------+---------------------+
1 row in set (0,01 sec)

This definitely threw me for a loop.  I noticed all our tables had a create_time of our last upgrade for MySQL so I was all concerned.  Seems there should be a create_time and an update_time.

This issue also exists in versions 8.0.21-commercial and 8.0.36.

Can it be repaired as soon as possible.thanks.

Just stumbling over this here. 

I have a requirement to find the latest created table, which seems to line up with the naming of CREATE_TIME, but in reality we observed it being updated on every ALTER TABLE which is 100% unexpected.

We tried sorting by UPDATE_TIME, but that's NULL after a server restart.

I'd love to see this fixed, or at the vary least, noted in the documentation.