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

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

    XMLWordPrintable

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28332 Alter on temporary table causes ER_TABLE_EXISTS_ERROR note

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28334 SHOW TABLE STATUS shows all temporary tables ignoring database and conditions

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28335 TABLE_TYPE for temporary sequences is the same as for permanent ones

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28343 sys.create_synonym_db fails with ER_VIEW_SELECT_TMPTABLE when schema contains temporary tables

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28351 Assertion `this->file->children_attached' failed in ha_myisammrg::info

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28453 SHOW commands are inconsistent for temporary tables

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31618 Server crashes in process_i_s_table_temporary_tables/get_all_tables

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31837 Server crashes in process_i_s_table_temporary_tables/get_all_tables

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

          Task - A task that needs to be done. MDEV-28112 prepare 10.9.0 preview releases

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10356 rpl.rpl_parallel_temptable failure due to incorrect commit optimization of temptables

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11785 Remove INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO (was: Server crashes in i_s_innodb_temp_table_info_fill_table)

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-15623 Show temporary tables for all sessions in information schema

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-17805 Do not add temporary tables to dict_sys->table_hash

          • Major - Major loss of function.
          • Stalled

          Task - A task that needs to be done. MDEV-23643 Column information of a TEMPORARY table as a server side CURSOR

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25064 rpl.rpl_parallel_temptable failed in bb, ASAN heap-use-after-free in std::__atomic_base<long>::store

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28325 I_S.CHECK_CONSTRAINTS, COLUMNS etc. do not show temporary tables

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28333 CREATE_TIME is not populated for temporary InnoDB tables

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28455 CREATE TEMPORARY TABLES privilege is insufficient for SHOW COLUMNS

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. CONJ-1108 Database metadata listing TEMPORARY tables/sequences

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31773 sys.table_exists results for temporary sequence differ

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            People

              anel Anel Husakovic
              fduenas Francisco Dueñas
              Votes:
              6 Vote for this issue
              Watchers:
              21 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.