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

            Activity

              People

              Assignee:
              anel Anel Husakovic
              Reporter:
              fduenas Francisco Dueñas
              Votes:
              4 Vote for this issue
              Watchers:
              18 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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