Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29200

CREATE_TIME is newer than UPDATE_TIME in information_schema.TABLES

    XMLWordPrintable

Details

    Description

      This is the example of CREATE_TIME is newer than UPDATE_TIME in information_schema.TABLES.

      MariaDB [information_schema]> select TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_TIME,UPDATE_TIME from information_schema.TABLES where UPDATE_TIME < CREATE_TIME ;
      +--------------+------------+--------+---------------------+---------------------+
      | TABLE_SCHEMA | TABLE_NAME | ENGINE | CREATE_TIME | UPDATE_TIME |
      +--------------+------------+--------+---------------------+---------------------+
      | sysbench | sbtest23 | InnoDB | 2022-07-27 09:41:52 | 2022-07-27 09:39:22 |
      +--------------+------------+--------+---------------------+---------------------+
      1 row in set (0.006 sec)
      ----------------------------------------------
      

      The above example shows that UPDATE_TIME is older than CREATE_TIME because I've run `alter table sbtest2 rename to sbtest23;` Restarting server/altering table would force UPDATE_TIME to set NULL, but change CREATE_TIME as current.
      However, one of our customer said they did not run any type of alter/restarting server, but information_schema.TABLES shows CREATE_TIME is newer than UPDATE_TIME. I found the bug in mysql as well - https://dba.stackexchange.com/questions/312251/mysql-information-schema-tables-shows-create-time-later-than-update-time, but I am sure our implementation of information_schema.TABLES is different than mysql v8.x as we have many missing globals than mysql v8.

      So, the question is what other cases can cause this other than rename table?

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              allen.lee@mariadb.com Allen Lee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.