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



  • 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 |



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2