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

CREATE_TIME is not populated for temporary InnoDB tables

Details

    Description

      --source include/have_innodb.inc
      		 
       
      		 
      drop table if exists tbase_innodb, ttmp_innodb;
      		 
       
      		 
      create table tbase_innodb (a int) engine=InnoDB;
      		 
      create temporary table ttmp_innodb (a int) engine=InnoDB;
      		 
       
      		 
      select table_name, table_type, create_time, update_time from information_schema.tables where table_schema = 'test' order by table_name;
      		 
       
      		 
      --sleep 2
      		 
       
      		 
      insert into tbase_innodb values (1);
      		 
      insert into ttmp_innodb values (1);
      		 
       
      		 
      select table_name, table_type, create_time, update_time from information_schema.tables where table_schema = 'test' order by table_name;
      		 
       
      		 
      # Cleanup
      		 
      drop table if exists tbase_innodb, ttmp_innodb;

      For the base table, CREATE_TIME is set. For the temporary table it is not.
      UPDATE_TIME is set for both.
      Aria and MyISAM temporary tables get CREATE_TIME.

      preview-10.9-MDEV-20119-misc c906db30

      		 
      create temporary table ttmp_innodb (a int) engine=InnoDB;
      		 
      select table_name, table_type, create_time, update_time from information_schema.tables where table_schema = 'test' order by table_name;
      		 
      table_name	table_type	create_time	update_time
      		 
      tbase_innodb	BASE TABLE	2022-04-17 21:55:00	NULL
      		 
      ttmp_innodb	TEMPORARY	NULL	NULL
      		 
      insert into tbase_innodb values (1);
      		 
      insert into ttmp_innodb values (1);
      		 
      select table_name, table_type, create_time, update_time from information_schema.tables where table_schema = 'test' order by table_name;
      		 
      table_name	table_type	create_time	update_time
      		 
      tbase_innodb	BASE TABLE	2022-04-17 21:55:00	2022-04-17 21:55:02
      		 
      ttmp_innodb	TEMPORARY	NULL	2022-04-17 21:55:02

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-12459 The information_schema tables for getting temporary tables info is missing, at least for innodb there is no INNODB_TEMP_TABLE_INFO

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            anel Anel Husakovic added a comment -

            I guess this is on InnoDB side marko ?

            anel Anel Husakovic added a comment - I guess this is on InnoDB side marko ?
            marko Marko Mäkelä added a comment -

            InnoDB does not store the information. For .ibd files it is retrieved from the file system. Does it work for tables created while SET GLOBAL innodb_file_per_table=0 is in effect?

            marko Marko Mäkelä added a comment - InnoDB does not store the information. For .ibd files it is retrieved from the file system. Does it work for tables created while SET GLOBAL innodb_file_per_table=0 is in effect?
            elenst Elena Stepanova added a comment -

            No, it doesn't work with innodb_file_per_table=0 either.

            preview-10.9-MDEV-20119-misc e62a2a061

            		 
            MariaDB [test]> set global innodb_file_per_table=0;
            		 
            Query OK, 0 rows affected (0.000 sec)
            		 
             
            		 
            MariaDB [test]> create temporary table ttmp_innodb (a int) engine=InnoDB;
            		 
            Query OK, 0 rows affected (0.001 sec)
            		 
             
            		 
            MariaDB [test]> select table_name, table_type, create_time, update_time from information_schema.tables where table_schema = 'test' order by table_name;
            		 
            +-------------+------------+-------------+-------------+
            		 
            | table_name  | table_type | create_time | update_time |
            		 
            +-------------+------------+-------------+-------------+
            		 
            | ttmp_innodb | TEMPORARY  | NULL        | NULL        |
            		 
            +-------------+------------+-------------+-------------+
            		 
            1 row in set (0.001 sec)

            elenst Elena Stepanova added a comment - No, it doesn't work with innodb_file_per_table=0 either. preview-10.9-MDEV-20119-misc e62a2a061 MariaDB [test]> set global innodb_file_per_table=0; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> create temporary table ttmp_innodb (a int ) engine=InnoDB; Query OK, 0 rows affected (0.001 sec)   MariaDB [test]> select table_name, table_type, create_time, update_time from information_schema.tables where table_schema = 'test' order by table_name; + -------------+------------+-------------+-------------+ | table_name | table_type | create_time | update_time | + -------------+------------+-------------+-------------+ | ttmp_innodb | TEMPORARY | NULL | NULL | + -------------+------------+-------------+-------------+ 1 row in set (0.001 sec)

            People

              anel Anel Husakovic
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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