MySQL information.schema.tables shows create_time later than update_time?

jules

I've checked https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html , but couldn't find anything that could explain this.

mysql> SELECT * FROM information_schema.`TABLES` WHERE table_schema='myschema' AND TABLE_NAME ='mytable' \G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: myschema
     TABLE_NAME: mytable
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 262354
 AVG_ROW_LENGTH: 114
    DATA_LENGTH: 30015488
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 4194304
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2022-05-17 12:00:32
    UPDATE_TIME: 2022-05-17 06:00:37
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0,04 sec)

As you can see, it looks like the table was created after it was updated. Any idea what can cause this?

EDIT: Here's the schema:

show create table myschema.mytable \G;
| mytable | CREATE TABLE mytable (

app_id char(27) NOT NULL,

ref_id int unsigned NOT NULL,

value decimal(10,5) unsigned NOT NULL,

created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (app_id,ref_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

Oba22

You are right, it shouldn't. Unfortunately, I strongly feel there is a bug.

I created a test in the Windows version of MySQL 8.0.23

TEST CODE

SHOW GLOBAL VARIABLES LIKE '%version%';
DROP DATABASE IF EXISTS dbasa_duru;
CREATE DATABASE dbasa_duru;
USE dbasa_duru
CREATE TABLE t1
(
  `app_id` char(27) NOT NULL,
  `ref_id` int unsigned NOT NULL,
  `value` decimal(10,5) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`app_id`,`ref_id`)
) ENGINE=InnoDB;
DO SLEEP(5);
CREATE TABLE t1_new LIKE t1;
SELECT table_name,create_time,update_time FROM information_schema.`TABLES`
WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');
DO SLEEP(3);
INSERT INTO t1_new (app_id,ref_id,value) VALUES
('APP01',5,10.5),('APP02',6,12.6),('APP03',5,14.7),
('APP04',5,10.5),('APP05',6,12.6),('APP06',5,14.7),
('APP07',5,10.5),('APP08',6,12.6),('APP09',5,14.7);
SELECT table_name,create_time,update_time FROM information_schema.`TABLES`
WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');
DO SLEEP(3);
RENAME TABLE t1 TO t1_old, t1_new TO t1;
DO SLEEP(3);
SELECT table_name,create_time,update_time FROM information_schema.`TABLES`
WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

Please note how I put DO SLEEP(3); to make sure we see distinct timestamps between operations.

I ran this multiple times, the UPDATE_TIME seems to be stuck on the timestamp 2022-05-18 09:28:56, the time I began running this test.

Here is the session where I ran the test multiple times

mysql> SHOW GLOBAL VARIABLES LIKE '%version%';
+-------------------------+-------------------------------+
| Variable_name           | Value                         |
+-------------------------+-------------------------------+
| admin_tls_version       | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| innodb_version          | 8.0.23                        |
| protocol_version        | 10                            |
| slave_type_conversions  |                               |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                 | 8.0.23                        |
| version_comment         | MySQL Community Server - GPL  |
| version_compile_machine | x86_64                        |
| version_compile_os      | Win64                         |
| version_compile_zlib    | 1.2.11                        |
+-------------------------+-------------------------------+
10 rows in set (0.00 sec)
mysql> DROP DATABASE IF EXISTS dbasa_duru;

Query OK, 2 rows affected (0.07 sec)
mysql> CREATE DATABASE dbasa_duru;

Query OK, 1 row affected (0.00 sec)
mysql> USE dbasa_duru

Database changed

mysql> CREATE TABLE t1

-> (

->   app_id char(27) NOT NULL,

->   ref_id int unsigned NOT NULL,

->   value decimal(10,5) unsigned NOT NULL,

->   created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

->   updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

->   PRIMARY KEY (app_id,ref_id)

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> DO SLEEP(5);

Query OK, 0 rows affected (5.01 sec)
mysql> CREATE TABLE t1_new LIKE t1;

Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:38:46 | NULL                |

| t1_new     | 2022-05-18 09:38:51 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.00 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.00 sec)
mysql> INSERT INTO t1_new (app_id,ref_id,value) VALUES

-> ('APP01',5,10.5),('APP02',6,12.6),('APP03',5,14.7),

-> ('APP04',5,10.5),('APP05',6,12.6),('APP06',5,14.7),

-> ('APP07',5,10.5),('APP08',6,12.6),('APP09',5,14.7);

Query OK, 9 rows affected (0.05 sec)

Records: 9  Duplicates: 0  Warnings: 0
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:38:46 | NULL                |

| t1_new     | 2022-05-18 09:38:51 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.00 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.01 sec)
mysql> RENAME TABLE t1 TO t1_old, t1_new TO t1;

Query OK, 0 rows affected (0.10 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.01 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+-------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME |

+------------+---------------------+-------------+

| t1         | 2022-05-18 09:38:51 | NULL        |

| t1_old     | 2022-05-18 09:38:46 | NULL        |

+------------+---------------------+-------------+

2 rows in set (0.05 sec)
mysql>

mysql> SHOW GLOBAL VARIABLES LIKE '%version%';

+-------------------------+-------------------------------+

| Variable_name           | Value                         |

+-------------------------+-------------------------------+

| admin_tls_version       | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |

| innodb_version          | 8.0.23                        |

| protocol_version        | 10                            |

| slave_type_conversions  |                               |

| tls_version             | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |

| version                 | 8.0.23                        |

| version_comment         | MySQL Community Server - GPL  |

| version_compile_machine | x86_64                        |

| version_compile_os      | Win64                         |

| version_compile_zlib    | 1.2.11                        |

+-------------------------+-------------------------------+

10 rows in set (0.01 sec)
mysql> DROP DATABASE IF EXISTS dbasa_duru;

Query OK, 2 rows affected (0.08 sec)
mysql> CREATE DATABASE dbasa_duru;

Query OK, 1 row affected (0.00 sec)
mysql> USE dbasa_duru

Database changed

mysql> CREATE TABLE t1

-> (

->   app_id char(27) NOT NULL,

->   ref_id int unsigned NOT NULL,

->   value decimal(10,5) unsigned NOT NULL,

->   created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

->   updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

->   PRIMARY KEY (app_id,ref_id)

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> DO SLEEP(5);

Query OK, 0 rows affected (5.01 sec)
mysql> CREATE TABLE t1_new LIKE t1;

Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:43:30 | NULL                |

| t1_new     | 2022-05-18 09:43:35 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.00 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.00 sec)
mysql> INSERT INTO t1_new (app_id,ref_id,value) VALUES

-> ('APP01',5,10.5),('APP02',6,12.6),('APP03',5,14.7),

-> ('APP04',5,10.5),('APP05',6,12.6),('APP06',5,14.7),

-> ('APP07',5,10.5),('APP08',6,12.6),('APP09',5,14.7);

Query OK, 9 rows affected (0.05 sec)

Records: 9  Duplicates: 0  Warnings: 0
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:43:30 | NULL                |

| t1_new     | 2022-05-18 09:43:35 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.00 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.01 sec)
mysql> RENAME TABLE t1 TO t1_old, t1_new TO t1;

Query OK, 0 rows affected (0.08 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.00 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+-------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME |

+------------+---------------------+-------------+

| t1         | 2022-05-18 09:43:35 | NULL        |

| t1_old     | 2022-05-18 09:43:30 | NULL        |

+------------+---------------------+-------------+

2 rows in set (0.03 sec)
mysql> SHOW GLOBAL VARIABLES LIKE '%version%';

+-------------------------+-------------------------------+

| Variable_name           | Value                         |

+-------------------------+-------------------------------+

| admin_tls_version       | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |

| innodb_version          | 8.0.23                        |

| protocol_version        | 10                            |

| slave_type_conversions  |                               |

| tls_version             | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |

| version                 | 8.0.23                        |

| version_comment         | MySQL Community Server - GPL  |

| version_compile_machine | x86_64                        |

| version_compile_os      | Win64                         |

| version_compile_zlib    | 1.2.11                        |

+-------------------------+-------------------------------+

10 rows in set (0.01 sec)
mysql> DROP DATABASE IF EXISTS dbasa_duru;

Query OK, 2 rows affected (0.07 sec)
mysql> CREATE DATABASE dbasa_duru;

Query OK, 1 row affected (0.00 sec)
mysql> USE dbasa_duru

Database changed

mysql> CREATE TABLE t1

-> (

->   app_id char(27) NOT NULL,

->   ref_id int unsigned NOT NULL,

->   value decimal(10,5) unsigned NOT NULL,

->   created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

->   updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

->   PRIMARY KEY (app_id,ref_id)

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> DO SLEEP(5);

Query OK, 0 rows affected (5.00 sec)
mysql> CREATE TABLE t1_new LIKE t1;

Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:45:22 | NULL                |

| t1_new     | 2022-05-18 09:45:27 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.00 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.02 sec)
mysql> INSERT INTO t1_new (app_id,ref_id,value) VALUES

-> ('APP01',5,10.5),('APP02',6,12.6),('APP03',5,14.7),

-> ('APP04',5,10.5),('APP05',6,12.6),('APP06',5,14.7),

-> ('APP07',5,10.5),('APP08',6,12.6),('APP09',5,14.7);

Query OK, 9 rows affected (0.01 sec)

Records: 9  Duplicates: 0  Warnings: 0
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:45:22 | NULL                |

| t1_new     | 2022-05-18 09:45:27 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.00 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.00 sec)
mysql> RENAME TABLE t1 TO t1_old, t1_new TO t1;

Query OK, 0 rows affected (0.04 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.00 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+-------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME |

+------------+---------------------+-------------+

| t1         | 2022-05-18 09:45:27 | NULL        |

| t1_old     | 2022-05-18 09:45:22 | NULL        |

+------------+---------------------+-------------+

2 rows in set (0.00 sec)
mysql>

mysql>

mysql> SHOW GLOBAL VARIABLES LIKE '%version%';

+-------------------------+-------------------------------+

| Variable_name           | Value                         |

+-------------------------+-------------------------------+

| admin_tls_version       | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |

| innodb_version          | 8.0.23                        |

| protocol_version        | 10                            |

| slave_type_conversions  |                               |

| tls_version             | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |

| version                 | 8.0.23                        |

| version_comment         | MySQL Community Server - GPL  |

| version_compile_machine | x86_64                        |

| version_compile_os      | Win64                         |

| version_compile_zlib    | 1.2.11                        |

+-------------------------+-------------------------------+

10 rows in set (0.00 sec)
mysql> DROP DATABASE IF EXISTS dbasa_duru;

Query OK, 2 rows affected (0.07 sec)
mysql> CREATE DATABASE dbasa_duru;

Query OK, 1 row affected (0.00 sec)
mysql> USE dbasa_duru

Database changed

mysql> CREATE TABLE t1

-> (

->   app_id char(27) NOT NULL,

->   ref_id int unsigned NOT NULL,

->   value decimal(10,5) unsigned NOT NULL,

->   created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

->   updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

->   PRIMARY KEY (app_id,ref_id)

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> DO SLEEP(5);

Query OK, 0 rows affected (5.01 sec)
mysql> CREATE TABLE t1_new LIKE t1;

Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:46:51 | NULL                |

| t1_new     | 2022-05-18 09:46:56 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.00 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.01 sec)
mysql> INSERT INTO t1_new (app_id,ref_id,value) VALUES

-> ('APP01',5,10.5),('APP02',6,12.6),('APP03',5,14.7),

-> ('APP04',5,10.5),('APP05',6,12.6),('APP06',5,14.7),

-> ('APP07',5,10.5),('APP08',6,12.6),('APP09',5,14.7);

Query OK, 9 rows affected (0.01 sec)

Records: 9  Duplicates: 0  Warnings: 0
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:46:51 | NULL                |

| t1_new     | 2022-05-18 09:46:56 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.00 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.01 sec)
mysql> RENAME TABLE t1 TO t1_old, t1_new TO t1;

Query OK, 0 rows affected (0.04 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.00 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+-------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME |

+------------+---------------------+-------------+

| t1         | 2022-05-18 09:46:56 | NULL        |

| t1_old     | 2022-05-18 09:46:51 | NULL        |

+------------+---------------------+-------------+

2 rows in set (0.05 sec)
mysql> SHOW GLOBAL VARIABLES LIKE '%version%';

+-------------------------+-------------------------------+

| Variable_name           | Value                         |

+-------------------------+-------------------------------+

| admin_tls_version       | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |

| innodb_version          | 8.0.23                        |

| protocol_version        | 10                            |

| slave_type_conversions  |                               |

| tls_version             | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |

| version                 | 8.0.23                        |

| version_comment         | MySQL Community Server - GPL  |

| version_compile_machine | x86_64                        |

| version_compile_os      | Win64                         |

| version_compile_zlib    | 1.2.11                        |

+-------------------------+-------------------------------+

10 rows in set (0.01 sec)
mysql> DROP DATABASE IF EXISTS dbasa_duru;

Query OK, 2 rows affected (0.07 sec)
mysql> CREATE DATABASE dbasa_duru;

Query OK, 1 row affected (0.00 sec)
mysql> USE dbasa_duru

Database changed

mysql> CREATE TABLE t1

-> (

->   app_id char(27) NOT NULL,

->   ref_id int unsigned NOT NULL,

->   value decimal(10,5) unsigned NOT NULL,

->   created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

->   updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

->   PRIMARY KEY (app_id,ref_id)

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> DO SLEEP(5);

Query OK, 0 rows affected (5.00 sec)
mysql> CREATE TABLE t1_new LIKE t1;

Query OK, 0 rows affected, 1 warning (0.12 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:47:10 | NULL                |

| t1_new     | 2022-05-18 09:47:15 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.01 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.01 sec)
mysql> INSERT INTO t1_new (app_id,ref_id,value) VALUES

-> ('APP01',5,10.5),('APP02',6,12.6),('APP03',5,14.7),

-> ('APP04',5,10.5),('APP05',6,12.6),('APP06',5,14.7),

-> ('APP07',5,10.5),('APP08',6,12.6),('APP09',5,14.7);

Query OK, 9 rows affected (0.05 sec)

Records: 9  Duplicates: 0  Warnings: 0
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+---------------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |

+------------+---------------------+---------------------+

| t1         | 2022-05-18 09:47:10 | NULL                |

| t1_new     | 2022-05-18 09:47:15 | 2022-05-18 09:28:56 |

+------------+---------------------+---------------------+

2 rows in set (0.00 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.01 sec)
mysql> RENAME TABLE t1 TO t1_old, t1_new TO t1;

Query OK, 0 rows affected (0.08 sec)
mysql> DO SLEEP(3);

Query OK, 0 rows affected (3.01 sec)
mysql> SELECT table_name,create_time,update_time FROM information_schema.TABLES

-> WHERE table_schema='dbasa_duru' AND TABLE_NAME IN ('t1','t1_old','t1_new');

+------------+---------------------+-------------+

| TABLE_NAME | CREATE_TIME         | UPDATE_TIME |

+------------+---------------------+-------------+

| t1         | 2022-05-18 09:47:15 | NULL        |

| t1_old     | 2022-05-18 09:47:10 | NULL        |

+------------+---------------------+-------------+

2 rows in set (0.01 sec)
mysql>

While the resulting behavior of my test is slightly different from yours, it is just as bad because the timestamp 2022-05-18 09:28:56 keeps reappearing long after I did the initial test.

There has to be some bug regarding the information_schema. If I got this error in Windows, this bug most also exist in other OS versions of MySQL 8.0.23.

You should file a bug report. Chances are, Oracle might say it is a feature and you should code around it. It the very least, information_schema.tables is a little unstable.

MySQL information.schema.tables shows create_time later than update_time?

TEST CODE

Suggested Topics

Real-time website with Node. JS
Software Programming • javascript mysql html5 express node. js • • shizuka

Oracle DB : Export only table data from from all tables of given schemas
SQL, Database Testing • oracle export expdp • • Kadir

Find the most remote of the mean value
Software Programming • mysql • • Avante

error with insert and mysql
Software Programming • mysql c sharp sql insert • • Laycee

MYSQL - How to make a table with a column that will sum columns from parent tables?
SQL, Database Testing • mysql mysql workbenc languageh • • Saumya

Record last login through mysql and display who is online
Software Programming • php mysql • • Kadyn

How to optimize the request
Software Programming • mysql index request • • authera

How colums are read in database?
SQL, Database Testing • mysql oracle database design • • gionna

Mysql table doesn't exist error, references wrong table name
SQL, Database Testing • mysql • • Avante

Is there a database table design to avoid storing table names in a table?
SQL, Database Testing • mysql database design normalization • • shizuka

How do PHP learn the name(s) of the OBD tables?
Software Programming • php mysql sql • • jules

Trying to run query and getting error: Error 1064 (42000)
SQL, Database Testing • mysql subquery errors • • jeanid

increasing mysql table open cache?
SQL, Database Testing • mysql innodb mysql 5.5 • • Alberto

Difference between two recordsets
SQL, Database Testing • php mysql • • nishika

Is this query too slow regarding the job it is doing?
SQL, Database Testing • mysql optimization innodb • • emerson

MySQL information.schema.tables shows create_time later than update_time?

TEST CODE

Suggested Topics

Real-time website with Node. JS Software Programming • javascript mysql html5 express node. js • • shizuka

Oracle DB : Export only table data from from all tables of given schemas SQL, Database Testing • oracle export expdp • • Kadir

Find the most remote of the mean value Software Programming • mysql • • Avante

error with insert and mysql Software Programming • mysql c sharp sql insert • • Laycee

MYSQL - How to make a table with a column that will sum columns from parent tables? SQL, Database Testing • mysql mysql workbenc languageh • • Saumya

Record last login through mysql and display who is online Software Programming • php mysql • • Kadyn

How to optimize the request Software Programming • mysql index request • • authera

How colums are read in database? SQL, Database Testing • mysql oracle database design • • gionna

Mysql table doesn't exist error, references wrong table name SQL, Database Testing • mysql • • Avante

Is there a database table design to avoid storing table names in a table? SQL, Database Testing • mysql database design normalization • • shizuka

How do PHP learn the name(s) of the OBD tables? Software Programming • php mysql sql • • jules

Trying to run query and getting error: Error 1064 (42000) SQL, Database Testing • mysql subquery errors • • jeanid

increasing mysql table open cache? SQL, Database Testing • mysql innodb mysql 5.5 • • Alberto

Difference between two recordsets SQL, Database Testing • php mysql • • nishika

Is this query too slow regarding the job it is doing? SQL, Database Testing • mysql optimization innodb • • emerson

Real-time website with Node. JS
Software Programming • javascript mysql html5 express node. js • • shizuka

Oracle DB : Export only table data from from all tables of given schemas
SQL, Database Testing • oracle export expdp • • Kadir

Find the most remote of the mean value
Software Programming • mysql • • Avante

error with insert and mysql
Software Programming • mysql c sharp sql insert • • Laycee

MYSQL - How to make a table with a column that will sum columns from parent tables?
SQL, Database Testing • mysql mysql workbenc languageh • • Saumya

Record last login through mysql and display who is online
Software Programming • php mysql • • Kadyn

How to optimize the request
Software Programming • mysql index request • • authera

How colums are read in database?
SQL, Database Testing • mysql oracle database design • • gionna

Mysql table doesn't exist error, references wrong table name
SQL, Database Testing • mysql • • Avante

Is there a database table design to avoid storing table names in a table?
SQL, Database Testing • mysql database design normalization • • shizuka

How do PHP learn the name(s) of the OBD tables?
Software Programming • php mysql sql • • jules

Trying to run query and getting error: Error 1064 (42000)
SQL, Database Testing • mysql subquery errors • • jeanid

increasing mysql table open cache?
SQL, Database Testing • mysql innodb mysql 5.5 • • Alberto

Difference between two recordsets
SQL, Database Testing • php mysql • • nishika

Is this query too slow regarding the job it is doing?
SQL, Database Testing • mysql optimization innodb • • emerson