This seems to duplicate MDEV-29191.

allen.lee@mariadb.com, note that any operation that may rebuild the table may also update the create_time. Apart from ALTER TABLE and CREATE INDEX, these includes TRUNCATE and OPTIMIZE. Were any such statements executed?

Before this bug can be confirmed, the SQL statements for reproducing it must be provided, along with the exact version with which it was tested.

I can imagine that in some case of a table rebuild operation, update_time could be inherited from the old copy of the table. If the last operation on the table was such a rebuild (ALTER, OPTIMIZE, TRUNCATE), the create_time could be newer. Someone needs to provide the exact steps for reproducing this.

The update_time is not persisted anywhere by InnoDB. It only resides in the InnoDB data dictionary cache. It can disappear even from there if LRU eviction of the table definition takes place. On reload, I think it should be NULL.

I developed and debugged a test case of this on 10.6:

--source include/have_innodb.inc

create table t1(a int primary key)engine=innodb;

select create_time,update_time from information_schema.tables where table_name='t1';

insert into t1 set a=1;

select create_time,update_time from information_schema.tables where table_name='t1';

sleep 1;

alter table t1 rename to t2;

select create_time,update_time from information_schema.tables where table_name='t2';

I was under the impression that the create_time would be read from the .ibd file, but actually it is being read from the .frm file, which I suppose was rewritten by the ALTER TABLE t1 RENAME TO t2. Here is the relevant part from the ./mtr --rr trace:

#2  0x0000561c6e48d6b3 in os_file_get_status (

    path=0x7fd86e3f6cb0 "./test/t2.frm", stat_info=0x7fd86e3f6eb0, 

    check_rw_perm=false, read_only=false)

    at /mariadb/10.5/storage/innobase/os/os0file.cc:3494

#3  0x0000561c6e37b75f in ha_innobase::info_low (this=0x7fd86007d930, 

    flag=282, is_analyze=false)

If InnoDB read a creation timestamp from the .ibd file, it would not work for tables that are created in the system tablespace (innodb_file_per_table=0) or for temporary tables. Furthermore, after any operation that rebuilds the InnoDB tablespace (such as TRUNCATE or OPTIMIZE), a too new creation time
would be reported.

sanja or serg, does the .frm file store the original table creation file? If not, could it please be extended to store it? Ideally, storage engines would not have report any creation time to the SQL layer. They have a much better idea of update_time. After all, the SQL layer controls all DDL.