WL#7784: Store temporary table metadata in memory
Affects: Server-8.0 — Status: Complete
Temporary table metadata is now stored in FRM files. There are two main reasons for that: 1. To know the list of tables which should be deleted on the server restart and pass that knowldege to SE. 2. Originally valid fully initialized TABLE_SHARE object could be constructed from FRM-file only. These reasons become obsolete in 5.7 with the New DD: - InnoDB (the main SE) stores temporary tables in a dedicated tablespace, which is discarded on startup. So, there is no need to pass a list of individual table names. Other SEs can implement the same logic themself. - There are no FRM-files. Storing temporary table metadata in persistent DD is wrong by design and it also creates more problems than it solves. This WL is to avoid storing temporary table metadata in persistent DD. NOTE: MySQL temporary table implementation differs from The SQL Standard in a sense that MySQL temporary tables are not shown in the INFORMATION_SCHEMA. That's why this WL is possible.
NF1: No user visible changes.
Types of temporary tables ========================= There are several types of temporary tables in server: 1) Implicit temporary tables created by optimizer for query execution. These tables do not have .FRM and represented by in-memory TABLE/ TABLE_SHARE structure already. They don't need to be represented in on-disk data-dictionary or have in-memory dd::Table objects. In the scope of this WL they are relevant only because on start-up we need to remove orphan tables of this kind, which remain after previous server run has aborted due to crash (as it is done now). It is fairly easy to do so as these tables created in tempdir with #sql prefix or in temporary tablespace. 2) Explicit temporary tables. These are tables created by user with CREATE TEMPORARY TABLES statement. Currently these tables have .FRMs. The goal of this WL is to change implementation and don't represent these tables in on-disk DD. Instead they should be represented by in-memory dd::Table object which will be associated/owned by temporary table's TABLE_SHARE and won't be present in in-memory DD. Similarly to 1) on start-up we need to remove orphan tables of this kind which were left from previous server runs. Similarly to 1) these tables have #sql prefix and reside in tempdir or temporary tablespace. 3) Implicit temporary tables created by ALTER TABLE implementation. There are two subclasses for them: a) Implicit temporary tables representing new versions of user-created temporary tables. This case is similar to case 2) and should be handled in the same fashion. b) Implicit temporary tables representing new versions of user-created non-temporary tables. Such tables now have .FRM file and reside in datadir or general/system tablespaces. With new-DD information about these tables will end up in on-disk and in in-memory DD when new table version replaces old table version. In theory there is no need to store information about such tables in DD before this moment. But to limit the scope of this task we won't change the fact that information about such temporary tables (i.e. about new version of table) is stored on-disk DD even before they replace old version of table. For the same reason we won't keep in-memory dd::Table for such tables bound to TABLE_SHARE. Orphan tables of this kind should not be automatically removed on server start-up, as in some scenarios they might be the only chance to recover data if server crashes in the middle of DDL. Once crash-safe DDL is implemented this problem willgo away. Cleaning up orphaned temporary tables on server start-up ======================================================== Handlerton interface should be extended with an operation, which instructs SE to discard all temporary tables of 1), 2) and 3.a) types it has. That operation should be called at server startup. Supported SEs should be updated: - InnoDB -- should discard temporary table tablespace; - MyISAM, CSV, Archive -- should do the same logic as now is done for FRM-files, i.e. look for files with #sql prefix in tmpdir directory and remove them. This logic should be generalized so that different SEs can reuse the same code. Note that for MyISAM tables with DATA/INDEX DIRECTORY option such files will be symlinks to other directories. In this case we need to remove both symlink and file it points to. For security reasons we should not do this if symlink points to a file within the data directory (it is impossible to create tables with such symlinks without manual intervention anyway). - Blackhole, Heap, Example -- no changes ID and namespace issue ====================== - Since temporary tables are not referenced from other DD objects it is OK to have -1 IDs for all tmp tables. - Temporary tables shadow normal tables. This sorted out on the layer above DD. So tmp tables doesn't have to be present in general in-memory/on-disk DD in any form.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.