[MDEV-8940] CREATE_TIME column of INFORMATION_SCHEMA.TABLES does not always update for innodb tables Created: 2015-10-14  Updated: 2021-04-28  Resolved: 2021-04-28

Status: Closed
Project: MariaDB Server
Component/s: Platform Windows, Storage Engine - InnoDB
Affects Version/s: 10.0.21, 10.2, 10.3, 10.4, 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Tuco Assignee: Vladislav Vaintroub
Resolution: Won't Do Votes: 0
Labels: None
Environment:

Windows Server 2008 R2

Attachments: File my.ini    

 Description   

The CREATE_TIME column of INFORMATION_SCHEMA.TABLES does not always update for innodb tables when the table is dropped and recreated.

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 18
 
Server version: 10.0.21-MariaDB mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> create database xyz;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [(none)]> use xyz
 
Database changed
 
MariaDB [xyz]> create table ct_test(id int);
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [xyz]> select create_time from information_schema.tables
 
    -> where table_schema='xyz' and table_name='ct_test'\G
 
*************************** 1. row ***************************
 
create_time: 2015-10-13 17:36:57
 
1 row in set (0.00 sec)
 
 
 
MariaDB [xyz]> drop table ct_test;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [xyz]> select now()\g
 
+---------------------+
 
| now()               |
 
+---------------------+
 
| 2015-10-13 17:38:14 |
 
+---------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [xyz]> create table ct_test(id int);
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [xyz]> select create_time from information_schema.tables
 
    -> where table_schema='xyz' and table_name='ct_test'\G
 
*************************** 1. row ***************************
 
create_time: 2015-10-13 17:38:32
 
1 row in set (0.00 sec)
 
 
 
MariaDB [xyz]> drop table ct_test;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [xyz]> select now()\G
 
*************************** 1. row ***************************
 
now(): 2015-10-13 17:39:24
 
1 row in set (0.00 sec)
 
 
 
MariaDB [xyz]> create table ct_test(id int);
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [xyz]> select create_time from information_schema.tables
 
    -> where table_schema='xyz' and table_name='ct_test'\G
 
*************************** 1. row ***************************
 
create_time: 2015-10-13 17:38:32
 
1 row in set (0.00 sec)
 
 
 
MariaDB [xyz]>

In this example the first drop and recreate worked, but the second did not.
Sometimes doing a 'optimize table' will cause an update, but not always.

This might be related to this bug https://bugs.mysql.com/bug.php?id=69689 which is a duplicate of this bug https://bugs.mysql.com/bug.php?id=14374
It might also be related this this bug https://bugs.mysql.com/bug.php?id=69990 but partitioned tables are not involved here.

 Comments   
Comment by Elena Stepanova [ 2015-10-19 ]

Could you please attach your ini file(s), or paste the output of SHOW VARIABLES?
Thanks.

Comment by Tuco [ 2015-10-20 ]

my.ini attached. The server is a standard install nothing has been changed.

Comment by Daniel Black [ 2017-03-07 ]

This fixed in the latest 10.2 and 10.1 as of today (on linux at least). I haven't tested 10.0.

+create table ct_test(id int);
 
+select create_time from information_schema.tables
 
+where table_schema='test' and table_name='ct_test';
 
+create_time
 
+2017-03-07 15:03:36
 
+drop table ct_test;
 
+select now();
 
+now()
 
+2017-03-07 15:03:36
 
+select sleep(2);
 
+sleep(2)
 
+0
 
+create table ct_test(id int);
 
+select create_time from information_schema.tables
 
+where table_schema='test' and table_name='ct_test';
 
+create_time
 
+2017-03-07 15:03:38
 
+drop table ct_test;

Comment by Elena Stepanova [ 2021-04-28 ]

It appears to be a Windows-specific effect; still reproducible on current 10.x branches.
The important factor is not the time between two create statements, but the time between drop and create. For example, on my machine (Windows 10 x84) it is reliably reproducible with less than 15 seconds between drop and create; if the interval is greater, the create_time is updated as it's supposed to.

So, in the MTR style, with this test case I can reproduce it if I run it with
perl ./mtr --sleep=14 (MTR overrides sleep value in the test case with the one from the command-line option) and can't reproduce with perl ./mtr --sleep=15

--source include/have_innodb.inc
 
 
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
drop table t;
 
--sleep 1
 
select now();
 
select create_time from information_schema.tables where table_name = 't';
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
drop table t;

10.5 559efad4

 
c:\_home\10.5\mysql-test>perl mysql-test-run.pl main.t --sleep=14
 
...
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
create_time
 
2021-04-29 00:00:36
 
drop table t;
 
select now();
 
now()
 
2021-04-29 00:00:50
 
select create_time from information_schema.tables where table_name = 't';
 
create_time
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
create_time
 
2021-04-29 00:00:36
 
drop table t;


c:\_home\10.5\mysql-test>perl mysql-test-run.pl main.t --sleep=15
 
...
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
create_time
 
2021-04-29 00:02:20
 
drop table t;
 
select now();
 
now()
 
2021-04-29 00:02:35
 
select create_time from information_schema.tables where table_name = 't';
 
create_time
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
create_time
 
2021-04-29 00:02:35
 
drop table t;

But if --sleep N is moved above drop table t, it is reproducible regardless the option value, even if it more than a minute.

--source include/have_innodb.inc
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
--sleep 1
 
drop table t;
 
select now();
 
select create_time from information_schema.tables where table_name = 't';
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
drop table t;


c:\_home\10.5\mysql-test>perl mysql-test-run.pl main.t --sleep=80
 
...
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
create_time
 
2021-04-29 00:19:14
 
drop table t;
 
select now();
 
now()
 
2021-04-29 00:20:34
 
select create_time from information_schema.tables where table_name = 't';
 
create_time
 
create table t (a int) engine=InnoDB;
 
select create_time from information_schema.tables where table_name = 't';
 
create_time
 
2021-04-29 00:19:14
 
drop table t;

Comment by Vladislav Vaintroub [ 2021-04-28 ]

I'm not sure what we can do here.
Anecdotal evidence of this problem was reported problem https://stackoverflow.com/a/24974744/547065 . It looks like file metadata is cached by some APIs, for some time.
Innodb just reports what CRT library stat() says, and this is the function to retrieve creation time.

I'd chalk this up , as Windows problem. I also do not think anyone should rely too much on these times.

Comment by Vladislav Vaintroub [ 2021-04-28 ]

Closing since "not our bug".

Comment by Vladislav Vaintroub [ 2021-04-28 ]

This is , mostly likely , and effect of something called tunnelling
There is an article of 1998
https://www.itprotoday.com/compute-engines/jsi-tip-0702-windows-nt-holds-deleted-file-meta-info-cache
and here is another one , a little bit fresher.
https://www.forensixchange.com/posts/19_04_13_windows-file-system-tunneling/

The 15 seconds, as found experimentally by elenst , correlates well with the MaximumTunnelEntryAgeInSeconds
Both post describe how to disable this tunneling thing.

Generated at Thu Feb 08 07:30:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.