Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
Currently, before Mysql 5.7, there was no way to get information from a temporary table (session table) using the information_schema. Now with Mysql 5.7 there is a new table named 'INNODB_TEMP_TABLE_INFO'.
According to documentation: https://mariadb.com/kb/en/mariadb/information-schema-temp_tables_info-table/
There was a table names 'TEMP_TABLES_INFO' which was introduced in 10.2.2 but removed in 10.2.4. I think maybe you're thinking to implement another solution which can cover all the needs.
The example shows how to use 'INNODB_TEMP_TABLE_INFO' but it doesn't exist
BTW that mysql's implementation is very limited, there no way you can query against temporary tables fields using a SELECT statement.
Summary from comments
This is what will be implemented:
1. list session ([#1]) temporary tables in I_S.TABLES with column TABLE_TYPE="TEMPORARY"([#2])
2. by design they will not be listed in I_S.COLUMNS and other I_S tables ([#3]). This MDEV will not implement changes related to the temporary table and I_S.COLUMNS .
3. issue a warning on CREATE if a new temp table shadows non-temp table ([#4]), not vice-versa.
References:
[#1] - No need for connection_id column to list all threads (MDEV-15623 should do that).
[#2] - Standard prescribes "GLOBAL TEMPORARY" or "LOCAL TEMPORARY" but our tables are neither, so let's just use "TEMPORARY".
[#3] - I_S.COLUMNS, I_S.STATISTICS, I_S.TABLE_CONSTRAINTS, I_S.KEY_COLUMN_USAGE, I_S.PARTITIONS, I_S.FILES ( ? ), I_S.CHECK_CONSTRAINTS. Comment.
MariaDB should be also able to show their fields in INFORMATION_SCHEMA.COLUMNS, and any other metadata associated to a normal table. But at least, implementing INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS will help a lot.
[#4] - Based on comment
Attachments
Issue Links
- causes
-
MDEV-28332 Alter on temporary table causes ER_TABLE_EXISTS_ERROR note
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- is part of
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Stalled
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
I think different tables should be used in I_S for temporary tables, as far as I know, there is no way to know globally how many temporary tables there are, which session created them and how much space they use (on disk or memory for engine=memory). This makes it very difficult to debug memory usage in environments with heavy use of temporary tables with engine=memory.
Also, actually a single connection can fill the entire memory of the server since temporary tables can be limited in space but not in number per session.
Please correct me if I'm wrong...
As agreed with Serg, moving this to 11.2 with the final specification:
This task will cover only the following behaviour for I_S.tables:
create table t1 (a int);
|
create sequence s1;
|
create temporary table t1 (b int);
|
create temporary sequence s1;
|
select table_schema, table_name, table_type, temporary
|
from information_schema.tables
|
where table_schema = 'test';
|
table_schema table_name table_type temporary
|
test s1 TEMPORARY SEQUENCE Y
|
test t1 TEMPORARY Y
|
test t1 BASE TABLE N
|
test s1 SEQUENCE N
|
The added test main.information_schema_temp_table is massively failing. I spotted a failure of a MemorySanitizer build:
CURRENT_TEST: main.information_schema_temp_table
|
mysqltest: At line 220: query 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test'' failed: <Unknown> (2013): Lost connection to server during query
|
…
|
==275990==WARNING: MemorySanitizer: use-of-uninitialized-value
|
#0 0x55a317232d06 in ha_myisammrg::info(unsigned int) /home/buildbot/amd64-debian-11-msan/build/storage/myisammrg/ha_myisammrg.cc:1289:7
|
#1 0x55a3147e651f in get_schema_tables_record(THD*, TABLE_LIST*, TABLE*, bool, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*) /home/buildbot/amd64-debian-11-msan/build/sql/sql_show.cc:5793:11
|
#2 0x55a3147da58a in process_i_s_table_temporary_tables(THD*, TABLE*, TABLE*) /home/buildbot/amd64-debian-11-msan/build/sql/sql_show.cc:5930:3
|
#3 0x55a3147da58a in get_all_tables(THD*, TABLE_LIST*, Item*) /home/buildbot/amd64-debian-11-msan/build/sql/sql_show.cc:5347:9
|
#4 0x55a31480bfdc in get_schema_tables_result(JOIN*, enum_schema_table_state) /home/buildbot/amd64-debian-11-msan/build/sql/sql_show.cc:9146:11
|
#5 0x55a31469faec in JOIN::exec_inner() /home/buildbot/amd64-debian-11-msan/build/sql/sql_select.cc:4890:7
|
#6 0x55a31469d411 in JOIN::exec() /home/buildbot/amd64-debian-11-msan/build/sql/sql_select.cc:4710:8
|
#7 0x55a314610da7 in mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /home/buildbot/amd64-debian-11-msan/build/sql/sql_select.cc:5239:21
|
#8 0x55a31460f94d in handle_select(THD*, LEX*, select_result*, unsigned long long) /home/buildbot/amd64-debian-11-msan/build/sql/sql_select.cc:627:10
|
#9 0x55a3144fa8cf in execute_sqlcom_select(THD*, TABLE_LIST*) /home/buildbot/amd64-debian-11-msan/build/sql/sql_parse.cc:6030:12
|
#10 0x55a3144de850 in mysql_execute_command(THD*, bool) /home/buildbot/amd64-debian-11-msan/build/sql/sql_parse.cc:3944:12
|
#11 0x55a3144c76e9 in mysql_parse(THD*, char*, unsigned int, Parser_state*) /home/buildbot/amd64-debian-11-msan/build/sql/sql_parse.cc:7774:18
|
#12 0x55a3144bde30 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /home/buildbot/amd64-debian-11-msan/build/sql/sql_parse.cc:1892:7
|
#13 0x55a3144c8e12 in do_command(THD*, bool) /home/buildbot/amd64-debian-11-msan/build/sql/sql_parse.cc:1405:17
|
#14 0x55a314b18736 in do_handle_one_connection(CONNECT*, bool) /home/buildbot/amd64-debian-11-msan/build/sql/sql_connect.cc:1416:11
|
#15 0x55a314b17d39 in handle_one_connection /home/buildbot/amd64-debian-11-msan/build/sql/sql_connect.cc:1318:5
|
#16 0x55a315de6dae in pfs_spawn_thread /home/buildbot/amd64-debian-11-msan/build/storage/perfschema/pfs.cc:2201:3
|
#17 0x7f3293f35ea6 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x7ea6) (BuildId: 255e355c207aba91a59ae1f808e3b4da443abf0c)
|
#18 0x7f329393da2e in __clone (/lib/x86_64-linux-gnu/libc.so.6+0xfba2e) (BuildId: e15ec78d51a522023f9cfc58dc284f379d81860b)
|
Uninitialized value was created by an allocation of 'mrg_info' in the stack frame of function '_ZN12ha_myisammrg4infoEj'
|
#0 0x55a3172324d0 in ha_myisammrg::info(unsigned int) /home/buildbot/amd64-debian-11-msan/build/storage/myisammrg/ha_myisammrg.cc:1274
|
As far as I understand, myrg_status() will not initialize the errkey field if flag=HA_STATUS_POS.
I think that this must be fixed before the feature can be merged to any main branch.
Tested on preview-11.2-preview a238b9a87616794a24a9. OK to push into 11.2
As Elena Stepanova said, wisely, an implementation that would not allow seeing the columns, is useless. I vote to delay it until it works.