[MDEV-28333] CREATE_TIME is not populated for temporary InnoDB tables Created: 2022-04-17  Updated: 2022-04-27  Resolved: 2022-04-27

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Information Schema, Storage Engine - InnoDB
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Anel Husakovic
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-12459 The information_schema tables for get... Closed

 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



 Comments   
Comment by Anel Husakovic [ 2022-04-18 ]

I guess this is on InnoDB side marko ?

Comment by Marko Mäkelä [ 2022-04-22 ]

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?

Comment by Elena Stepanova [ 2022-04-22 ]

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)

Generated at Thu Feb 08 09:59:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.