MySQL Bugs: #69990: CREATE_TIME and UPDATE_TIME are wrong for partitioned tables

Bug #69990	CREATE_TIME and UPDATE_TIME are wrong for partitioned tables
Submitted:	12 Aug 2013 7:22	Modified:	8 May 2015 15:32
Reporter:	Justin Swanhart	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S1 (Critical)
Version:	5.6.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> select distinct table_name,update_time,create_time from information_schema.tables where table_schema='test';
+------------+-------------+---------------------+
| table_name | update_time | create_time         |
+------------+-------------+---------------------+
| car_read   | NULL        | NULL                |
| cust_car   | NULL        | 2013-08-12 00:10:30 |
+------------+-------------+---------------------+
2 rows in set (0.00 sec)

mysql> analyze table test.car_read;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.car_read | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.34 sec)

mysql> select distinct table_name,update_time,create_time from information_schema.tables where table_schema='test';
+------------+-------------+---------------------+
| table_name | update_time | create_time         |
+------------+-------------+---------------------+
| car_read   | NULL        | NULL                |
| cust_car   | NULL        | 2013-08-12 00:10:30 |
+------------+-------------+---------------------+
2 rows in set (0.01 sec)

mysql> analyze table test.cust_car;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.cust_car | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> select distinct table_name,update_time,create_time from information_schema.tables where table_schema='test';
+------------+-------------+---------------------+
| table_name | update_time | create_time         |
+------------+-------------+---------------------+
| car_read   | NULL        | NULL                |
| cust_car   | NULL        | 2013-08-12 00:10:30 |
+------------+-------------+---------------------+
2 rows in set (0.00 sec)

Prevents tools from knowing if they should refresh their statistics because they do not know if the table structure of the table has changed, or that the contents have changed.

How to repeat:
use test;

CREATE TABLE `car_read` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `car_id` int(11) NOT NULL,
  `reader_id` int(11) NOT NULL,
  `odometer_reading` bigint(20) NOT NULL,
  `longitude` float DEFAULT NULL,
  `latitude` float DEFAULT NULL,
  `read_datetime` datetime NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_car_read_datetime` (`car_id`,`read_datetime`),
  KEY `idx_reader_id` (`reader_id`)
) ENGINE=InnoDB AUTO_INCREMENT=255616 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY HASH (id)
PARTITIONS 16 */;

CREATE TABLE `cust_car` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_id` int(11) NOT NULL,
  `car_id` int(11) NOT NULL,
  `assigned_datetime` datetime DEFAULT NULL,
  `unassigned_datetime` datetime NOT NULL,
  `issued_odometer` bigint(20) NOT NULL,
  `merged` tinyint(1) DEFAULT '0',
  `create_manually` tinyint(1) DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25555 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

analyze table cust_car;

analyze table car_read;

select distinct table_name,update_time,create_time from information_schema.tables where table_schema='test';

Suggested fix:
Fix the information_schema so that it shows proper information for all tables, including partitioned tables, regardless of storage engine.

Hello Justin,

Thank you for the bug report and the test case. 
Verified as described.

Thanks,
Umesh

Also, INFORMATION_SCHEMA PARTITIONS Table too doesn't show CREATE_TIME and UPDATE_TIME for partitioned tables.

// 5.6.13

mysql> select distinct table_name,CREATE_TIME, UPDATE_TIME, CHECK_TIME  from information_schema.partitions where table_schema='test'
    -> ;
+------------+---------------------+-------------+------------+
| table_name | CREATE_TIME         | UPDATE_TIME | CHECK_TIME |
+------------+---------------------+-------------+------------+
| animals    | 2013-08-12 17:06:07 | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| cust_car   | 2013-08-12 17:20:18 | NULL        | NULL       |
+------------+---------------------+-------------+------------+
3 rows in set (0.01 sec)

mysql> select table_name,CREATE_TIME, UPDATE_TIME, CHECK_TIME  from information_schema.partitions where table_schema='test';
+------------+---------------------+-------------+------------+
| table_name | CREATE_TIME         | UPDATE_TIME | CHECK_TIME |
+------------+---------------------+-------------+------------+
| animals    | 2013-08-12 17:06:07 | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| car_read   | NULL                | NULL        | NULL       |
| cust_car   | 2013-08-12 17:20:18 | NULL        | NULL       |
+------------+---------------------+-------------+------------+
18 rows in set (0.02 sec)

Documented fix in the 5.5.44 and 5.6.25 changelogs as follows:

        The CREATE_TIME column of the INFORMATION_SCHEMA.TABLES table
        now shows the correct table creation time for partitioned InnoDB
        tables. The CREATE_TIME column of the
        INFORMATION_SCHEMA.PARTITIONS table now shows the correct
        partition creation time for a partition of partitioned InnoDB
        tables.

Documented fix in the 5.7.8 changelog as above, plus the following:

        The UPDATE_TIME column of the INFORMATION_SCHEMA.TABLES table
        now shows when a partitioned InnoDB table was last updated by an
        INSERT, DELETE, or UPDATE. The UPDATE_TIME column of the
        INFORMATION_SCHEMA.PARTITIONS table now shows when a partition
        of a partitioned InnoDB table was last updated.

Also updated the descriptions of the affected INFORMATION_SCHEMA table columns.

Closed.

commit 232d8bbdb165260ba8eca51565067a93659bea6f
Author: aditya <aditya.a@oracle.com>
Date:   Mon Apr 6 12:27:12 2015 +0530

    Bug #17299181  CREATE_TIME AND UPDATE_TIME ARE WRONG FOR PARTITIONED TABLES
    
    PROBLEM
    
    Create time is calculated as last status change time of .frm file.
    The first problem was that innodb was passing file name as
    "table_name#po#p0.frm" to the stat() call which calculates the create time.
    Since there is no frm file with this name create_time will be stored as NULL.
    The second problem is ha_partition::info() updates stats for create time
    when HA_STATUS_CONST flag was set ,where as innodb calculates this statistic
    when HA_STATUS_TIME is set,which causes create_time to be set as NULL.
    
    Fix
    Pass proper .frm name to stat() call and calculate create time when
    HA_STATUS_CONST flag is set.

commit 195062883527917fc50c6fba5aaed201b8fe4446
Author: aditya <aditya.a@oracle.com>
Date:   Tue Apr 7 09:56:28 2015 +0530

    Bug #17299181    CREATE_TIME AND UPDATE_TIME ARE WRONG FOR PARTITIONED TABLES
    
    Posty push fix for test case

Crete and modified times are necessary also for views, but in microseconds because running a script can create more than 6 views in the same second.

Thanks.