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

[26 Mar 2018 17:15] Norman Knupp
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.
[28 Mar 2018 1:17] Miguel Solorzano
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)