[MDEV-12459] The information_schema tables for getting temporary tables info is missing, at least for innodb there is no INNODB_TEMP_TABLE_INFO - Jira

Francisco Dueñas created issue - 2017-04-06 11:25

Francisco Dueñas made changes - 2017-04-06 11:40

Field	Original Value	New Value
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 doesnt exist	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 doesnt exist BTW way that implementation is very lmited, there no way you can query against temporary tables fields using a SELECT statement.
Summary	Th information_schema table for getting temporary tables info is missing, at lets innodb is missing INNODB_TEMP_TABLE_INFO	The information_schema tables for getting temporary tables info is missing, at least for innodb there is no INNODB_TEMP_TABLE_INFO

Francisco Dueñas made changes - 2017-04-06 11:41

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 doesnt exist

BTW way that implementation is very lmited, there no way you can query against temporary tables fields using a SELECT statement.

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.

Elena Stepanova made changes - 2017-04-20 15:38

Link

This issue relates to ~~MDEV-11785~~ [ ~~MDEV-11785~~ ]

Elena Stepanova made changes - 2017-04-20 15:39

Fix Version/s		10.2 [ 14601 ]
Assignee		Marko Mäkelä [ marko ]

Rasmus Johansson (Inactive) made changes - 2017-05-09 12:09

Fix Version/s		10.3 [ 22126 ]
Fix Version/s	10.2 [ 14601 ]

Marko Mäkelä made changes - 2017-05-10 11:54

Component/s		Data Definition - Temporary [ 10123 ]
Component/s	Storage Engine - InnoDB [ 10129 ]

Marko Mäkelä made changes - 2017-05-10 11:54

Assignee

Marko Mäkelä [ marko ]

Elena Stepanova made changes - 2017-05-12 16:44

Affects Version/s	10.2.4 [ 22116 ]
Affects Version/s	10.2.5 [ 22117 ]
Environment	Windows 10 64 bit
Issue Type	Bug [ 1 ]	Task [ 3 ]

Sergei Golubchik made changes - 2018-02-14 16:06

Labels

beginner-friendly

Sergei Golubchik made changes - 2018-02-14 16:06

Fix Version/s

10.3 [ 22126 ]

Sergei Golubchik made changes - 2018-04-09 09:20

Assignee

Anel Husakovic [ anel ]

Chris Calender (Inactive) made changes - 2019-04-06 14:52

Support case ID

27461

Anel Husakovic made changes - 2019-05-02 07:40

Status

Open [ 1 ]

Confirmed [ 10101 ]

Anel Husakovic made changes - 2019-05-02 07:40

Assignee	Anel Husakovic [ anel ]	Vicentiu Ciorbaru [ cvicentiu ]
Status	Confirmed [ 10101 ]	In Review [ 10002 ]

Anel Husakovic made changes - 2019-05-16 14:28

Fix Version/s

10.3 [ 22126 ]

Julien Fritsch made changes - 2020-01-20 13:32

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2020-01-21 20:05

Priority

Critical [ 2 ]

Major [ 3 ]

Ralf Gebhardt made changes - 2020-05-07 17:40

Fix Version/s

10.3 [ 22126 ]

Ralf Gebhardt made changes - 2020-05-20 12:43

Fix Version/s

10.6 [ 24028 ]

Sergei Petrunia made changes - 2020-07-08 10:23

Rank

Ranked lower

Sergei Golubchik made changes - 2020-08-14 09:26

Assignee

Vicențiu Ciorbaru [ cvicentiu ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2020-08-16 19:55

Rank

Ranked higher

Sergei Golubchik made changes - 2020-09-15 10:37

Link

This issue relates to MDEV-23643 [ MDEV-23643 ]

Sergei Golubchik made changes - 2020-09-15 10:38

Link

This issue relates to MDEV-15623 [ MDEV-15623 ]

Sergei Golubchik made changes - 2020-09-21 18:38

Comment

[ will be done in MDEV-15623 ]

Sergei Golubchik made changes - 2020-09-21 18:39

Link

This issue duplicates MDEV-15623 [ MDEV-15623 ]

Sergei Golubchik made changes - 2020-09-21 18:56

Link

This issue duplicates MDEV-15623 [ MDEV-15623 ]

Sergei Golubchik made changes - 2020-09-21 18:57

Assignee	Sergei Golubchik [ serg ]	Anel Husakovic [ anel ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Anel Husakovic made changes - 2020-11-17 13:11

Labels

beginner-friendly

beginner-friendly foundation

Anel Husakovic made changes - 2021-03-03 12:57

Assignee	Anel Husakovic [ anel ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2021-03-31 17:10

Assignee	Sergei Golubchik [ serg ]	Anel Husakovic [ anel ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2021-05-01 13:30

Priority

Major [ 3 ]

Critical [ 2 ]

Julien Fritsch made changes - 2021-05-10 09:35

Fix Version/s

10.6 [ 24028 ]

Anel Husakovic made changes - 2021-09-06 08:36

Assignee	Anel Husakovic [ anel ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2021-10-29 13:43

Fix Version/s

10.8 [ 26121 ]

Sergei Golubchik made changes - 2021-11-01 12:57

Assignee	Sergei Golubchik [ serg ]	Anel Husakovic [ anel ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2021-12-06 21:22

Workflow

MariaDB v3 [ 80262 ]

MariaDB v4 [ 131671 ]

Anel Husakovic made changes - 2022-01-28 08:09

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.

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. list them in {{I_S.COLUMNS}} and other {{I_S}} tables ([#3]) if they don't shadow non-temp tables ([#4])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table ([#4]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

Anel Husakovic made changes - 2022-01-28 08:18

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. list them in {{I_S.COLUMNS}} and other {{I_S}} tables ([#3]) if they don't shadow non-temp tables ([#4])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table ([#4]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

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. list them in {{I_S.COLUMNS}} and other {{I_S}} tables ([#3]) if they don't shadow non-temp tables ([#4])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table ([#4]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

Ralf Gebhardt made changes - 2022-01-28 09:25

Fix Version/s		10.9 [ 26905 ]
Fix Version/s	10.8 [ 26121 ]

Anel Husakovic made changes - 2022-02-01 08:11

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Michael Widenius made changes - 2022-02-08 13:02

Comment

[ Why do "list session (1) temporary tables in I_S.TABLES with column TABLE_TYPE="TEMPORARY"(2)"

Information_schema.tables has already a column 'temporary'. There is no reason to add more information about temporary tables to it.
If the TEMPORARY is a requirement by the standard, then it should be ok. If not, don't do, it as there may already be tools who uses the temporary column.
However, we cannot just remove the 'temporary' column as there may be tools or programs depending on it.

]

Michael Widenius made changes - 2022-02-08 13:02

Comment

[ Please describe the reason why we want to change the current format?
Yes, GLOBAL TEMPORARY and LOCAL TEMPORARY are standard, but as we have neither, what is wrong with just using the current temporary column?
We should not remove the 'Temporary' column as there may be users, applications or tools that depends on this. Remember that it is as important to be backward compatible at is is to following the standard. Best if we can do both.
]

Michael Widenius made changes - 2022-02-08 13:02

Comment

[ Please describe the reason why we want to change the current format?
Yes, GLOBAL TEMPORARY and LOCAL TEMPORARY are standard, but as we have neither, what is wrong with just using the current temporary column?
We should not remove the 'Temporary' column as there may be users, applications or tools that depends on this. Remember that it is as important to be backward compatible at is is to following the standard. Best if we can do both.
]

Marko Mäkelä made changes - 2022-02-08 16:34

Link

This issue relates to ~~MDEV-10356~~ [ ~~MDEV-10356~~ ]

Marko Mäkelä made changes - 2022-02-08 16:34

Link

This issue relates to MDEV-17805 [ MDEV-17805 ]

Marko Mäkelä made changes - 2022-02-08 16:34

Link

This issue relates to ~~MDEV-25064~~ [ ~~MDEV-25064~~ ]

Sergei Golubchik made changes - 2022-03-17 13:14

Link

This issue is part of ~~MDEV-28112~~ [ ~~MDEV-28112~~ ]

Anel Husakovic made changes - 2022-03-17 13:38

Status

In Progress [ 3 ]

In Testing [ 10301 ]

Anel Husakovic made changes - 2022-04-13 11:56

Status

In Testing [ 10301 ]

Stalled [ 10000 ]

Anel Husakovic made changes - 2022-04-13 11:56

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Anel Husakovic made changes - 2022-04-13 11:57

Assignee

Anel Husakovic [ anel ]

Elena Stepanova [ elenst ]

Julien Fritsch made changes - 2022-04-14 17:34

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. list them in {{I_S.COLUMNS}} and other {{I_S}} tables ([#3]) if they don't shadow non-temp tables ([#4])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table ([#4]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

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. list them in {{I_S.COLUMNS}} and other {{I_S}} tables (\[#3\]) if they don't shadow non\-temp tables (\[#4\])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table (\[#4\]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

Sergei Golubchik made changes - 2022-04-14 17:48

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. list them in {{I_S.COLUMNS}} and other {{I_S}} tables (\[#3\]) if they don't shadow non\-temp tables (\[#4\])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table (\[#4\]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

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. list them in {{I_S.COLUMNS}} and other {{I_S}} tables (\[#3\]) if they don't shadow non\-temp tables (\[#4\])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table (\[#4\]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

Elena Stepanova made changes - 2022-04-15 15:25

Link

This issue relates to ~~MDEV-28325~~ [ ~~MDEV-28325~~ ]

Anel Husakovic made changes - 2022-04-17 11:27

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. list them in {{I_S.COLUMNS}} and other {{I_S}} tables (\[#3\]) if they don't shadow non\-temp tables (\[#4\])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table (\[#4\]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

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\])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table (\[#4\]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

Elena Stepanova made changes - 2022-04-17 18:34

Link

This issue causes ~~MDEV-28332~~ [ ~~MDEV-28332~~ ]

Elena Stepanova made changes - 2022-04-17 18:59

Link

This issue relates to ~~MDEV-28333~~ [ ~~MDEV-28333~~ ]

Elena Stepanova made changes - 2022-04-17 20:47

Link

This issue causes ~~MDEV-28334~~ [ ~~MDEV-28334~~ ]

Elena Stepanova made changes - 2022-04-17 20:59

Link

This issue causes ~~MDEV-28335~~ [ ~~MDEV-28335~~ ]

Anel Husakovic made changes - 2022-04-18 08:39

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\])
3. issue a warning on {{CREATE}} if a new temp table shadows non-temp table (\[#4\]), not vice-versa.
4. remove column {{"Temporary"}} in a separate commit

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

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\])
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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

Elena Stepanova made changes - 2022-04-18 16:31

Link

This issue causes ~~MDEV-28343~~ [ ~~MDEV-28343~~ ]

Elena Stepanova made changes - 2022-04-19 19:34

Link

This issue causes ~~MDEV-28351~~ [ ~~MDEV-28351~~ ]

Elena Stepanova made changes - 2022-05-02 12:01

Link

This issue causes ~~MDEV-28453~~ [ ~~MDEV-28453~~ ]

Elena Stepanova made changes - 2022-05-02 15:35

Link

This issue causes ~~MDEV-28455~~ [ ~~MDEV-28455~~ ]

Elena Stepanova made changes - 2022-05-06 12:38

Assignee	Elena Stepanova [ elenst ]	Anel Husakovic [ anel ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Elena Stepanova made changes - 2022-05-11 09:25

Link

This issue relates to ~~MDEV-28455~~ [ ~~MDEV-28455~~ ]

Elena Stepanova made changes - 2022-05-11 09:25

Link

This issue causes ~~MDEV-28455~~ [ ~~MDEV-28455~~ ]

Ralf Gebhardt made changes - 2022-05-11 14:09

Fix Version/s		10.10 [ 27530 ]
Fix Version/s	10.9 [ 26905 ]

Sergei Golubchik made changes - 2022-06-15 13:36

Fix Version/s		10.11 [ 27614 ]
Fix Version/s	10.10 [ 27530 ]

Anel Husakovic made changes - 2022-08-09 06:57

Status

Stalled [ 10000 ]

In Progress [ 3 ]

AirFocus made changes - 2022-08-09 16:11

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\])
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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

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\])
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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

Julien Fritsch made changes - 2022-08-10 08:12

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\])
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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

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\])
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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

Ralf Gebhardt made changes - 2022-09-20 13:21

Fix Version/s		10.12 [ 28320 ]
Fix Version/s	10.11 [ 27614 ]

Anel Husakovic made changes - 2022-09-29 11:00

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\])
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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=167688&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-167688].
{{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|https://jira.mariadb.org/browse/MDEV-12459?focusedCommentId=183124&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-183124]

Ralf Gebhardt made changes - 2022-12-27 14:14

Labels

beginner-friendly foundation

Preview_removed_10.9 beginner-friendly foundation

Anel Husakovic made changes - 2023-01-31 09:15

Assignee	Anel Husakovic [ anel ]	Vicențiu Ciorbaru [ cvicentiu ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Vicențiu Ciorbaru made changes - 2023-02-15 11:58

Fix Version/s		11.1 [ 28549 ]
Fix Version/s	11.0 [ 28320 ]

Ralf Gebhardt made changes - 2023-03-21 09:30

Labels

Preview_removed_10.9 beginner-friendly foundation

Preview_10.9 Preview_11.1 Preview_removed_10.9 beginner-friendly foundation

Ralf Gebhardt made changes - 2023-03-21 09:31

Labels

Preview_10.9 Preview_11.1 Preview_removed_10.9 beginner-friendly foundation

Preview_10.9 Preview_11.1 beginner-friendly foundation

Ralf Gebhardt made changes - 2023-03-21 09:43

Labels

Preview_10.9 Preview_11.1 beginner-friendly foundation

Preview_10.9 beginner-friendly foundation

Ralf Gebhardt made changes - 2023-04-11 10:00

Fix Version/s		11.2 [ 28603 ]
Fix Version/s	11.1 [ 28549 ]

Vicențiu Ciorbaru made changes - 2023-04-18 04:12

Assignee	Vicențiu Ciorbaru [ cvicentiu ]	Anel Husakovic [ anel ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Anel Husakovic made changes - 2023-06-12 12:45

Assignee	Anel Husakovic [ anel ]	Vicențiu Ciorbaru [ cvicentiu ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2023-06-19 16:59

Status

In Review [ 10002 ]

In Testing [ 10301 ]

Sergei Golubchik made changes - 2023-06-19 17:00

Assignee

Vicențiu Ciorbaru [ cvicentiu ]

Ramesh Sivaraman [ JIRAUSER48189 ]

Alice Sherepa made changes - 2023-06-20 07:07

Assignee

Ramesh Sivaraman [ JIRAUSER48189 ]

Alice Sherepa [ alice ]

Ralf Gebhardt made changes - 2023-06-26 10:55

Labels

Preview_10.9 beginner-friendly foundation

Preview_10.9 Preview_11.2 beginner-friendly foundation

Alice Sherepa made changes - 2023-07-04 11:32

Link

This issue causes ~~MDEV-31618~~ [ ~~MDEV-31618~~ ]

Alice Sherepa made changes - 2023-07-25 16:02

Link

This issue relates to ~~MDEV-31773~~ [ ~~MDEV-31773~~ ]

Sergei Golubchik made changes - 2023-08-10 23:11

Link

This issue includes ~~MDEV-31837~~ [ ~~MDEV-31837~~ ]

Sergei Golubchik made changes - 2023-08-10 23:11

Link

This issue causes ~~MDEV-31837~~ [ ~~MDEV-31837~~ ]

Sergei Golubchik made changes - 2023-08-10 23:11

Link

This issue includes ~~MDEV-31837~~ [ ~~MDEV-31837~~ ]

Alice Sherepa made changes - 2023-08-11 16:14

Status

In Testing [ 10301 ]

Stalled [ 10000 ]

Alice Sherepa made changes - 2023-08-11 16:15

Assignee

Alice Sherepa [ alice ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2023-08-12 13:45

Fix Version/s		11.2.1 [ 29034 ]
Fix Version/s	11.2 [ 28603 ]
Assignee	Sergei Golubchik [ serg ]	Anel Husakovic [ anel ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2023-08-12 13:49

Component/s

Information Schema [ 14413 ]

Diego Dupin made changes - 2023-09-07 15:31

Link

This issue relates to ~~CONJ-1108~~ [ ~~CONJ-1108~~ ]

Rob Schwyzer (Inactive) made changes - 2024-04-04 20:02

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36726 ]

Rob Schwyzer (Inactive) made changes - 2024-04-05 18:34

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36726 ]

Jira Automation (IT) made changes - 2024-07-04 08:35

Zendesk Related Tickets

190605

MariaDB Server

The information_schema tables for getting temporary tables info is missing, at least for innodb there is no INNODB_TEMP_TABLE_INFO

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration