Re: [Maria-developers] bb1737b0482: MDEV-12459: The information_schema tables for getting temporary tables info is missing, at least for innodb there is no INNODB_TEMP_TABLE_INFO

Hi, Anel! On Nov 01, Anel Husakovic wrote:

revision-id: bb1737b0482 (mariadb-10.6.1-73-gbb1737b0482) parent(s): 1bc82aaf0a7 author: Anel Husakovic committer: Anel Husakovic timestamp: 2021-09-06 08:58:46 +0200 message:

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

diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 6b351726320..5444de8c1ca 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -69,6 +69,8 @@

#include "lex_symbol.h" #define KEYWORD_SIZE 64 +#define IS_USER_TEMP_TABLE(A) ((A->tmp_table == TRANSACTIONAL_TMP_TABLE) || \ + (A->tmp_table == NON_TRANSACTIONAL_TMP_TABLE))

extern SYMBOL symbols[]; extern size_t symbols_length; @@ -151,6 +153,8 @@ static int show_create_sequence(THD *thd, TABLE_LIST *table_list, static const LEX_CSTRING *view_algorithm(TABLE_LIST *table);

bool get_lookup_field_values(THD *, COND *, TABLE_LIST *, LOOKUP_FIELD_VALUES *); +void process_i_s_table_temporary_tables(THD *thd, TABLE * table, LEX_CSTRING *db_name, + LEX_CSTRING *table_name, TABLE *tmp_tbl);

/** Try to lock a mutex, but give up after a short while to not cause deadlocks @@ -5161,6 +5166,10 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) uint table_open_method= tables->table_open_method; bool can_deadlock; MEM_ROOT tmp_mem_root; + Dynamic_array system_tables(PSI_INSTRUMENT_MEM); + All_tmp_tables_list *temp_tables= NULL; + // Scan for temporary tables + TMP_TABLE_SHARE *share_temp; DBUG_ENTER("get_all_tables");

bzero(&tmp_mem_root, sizeof(tmp_mem_root)); @@ -5223,6 +5232,12 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) init_alloc_root(PSI_INSTRUMENT_ME, &tmp_mem_root, SHOW_ALLOC_BLOCK_SIZE, SHOW_ALLOC_BLOCK_SIZE, MY_THREAD_SPECIFIC);

+ system_tables.push(INFORMATION_SCHEMA_NAME); + system_tables.push(PERFORMANCE_SCHEMA_DB_NAME); + system_tables.push(MYSQL_SCHEMA_NAME); + system_tables.push(SYS_SCHEMA_NAME); + system_tables.push(MTR_SCHEMA_NAME); + for (size_t i=0; i < db_names.elements(); i++) { LEX_CSTRING *db_name= db_names.at(i); @@ -5234,6 +5249,36 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) sctx->master_access & (DB_ACLS | SHOW_DB_ACL) || acl_get(sctx->host, sctx->ip, sctx->priv_user, db_name->str, 0)) #endif + + // Only if there is IS.tables allow temporary tables to be shown + if (schema_table_idx == SCH_TABLES && !temp_tables) + { + for (size_t k=0; k < system_tables.elements(); k++) + { + if (db_name != &system_tables.at(k)) + { + temp_tables= open_tables_state_backup.temporary_tables; + if (temp_tables) + { + All_tmp_tables_list::Iterator it(*open_tables_state_backup.temporary_tables); + while ((share_temp= it++)) + { + All_share_tables_list::Iterator it2(share_temp->all_tmp_tables); + while (TABLE *tbl= it2++) + { + if (IS_USER_TEMP_TABLE(share_temp)) + { + // Now we have the data and we should process_table() manually + process_i_s_table_temporary_tables(thd, table, db_name, + &share_temp->table_name, tbl); + } + } + } + } + break; + } + } + }

hmmmm. What do you do here? I understand, basically, nothing here. Could you explain, line by line?

{ Dynamic_array table_names(PSI_INSTRUMENT_MEM); int res= make_table_name_list(thd, &table_names, lex, @@ -5495,6 +5540,8 @@ static int get_schema_tables_record(THD *thd, TABLE_LIST *tables, table->field[3]->store(STRING_WITH_LEN("SYSTEM VIEW"), cs); else if (share->table_type == TABLE_TYPE_SEQUENCE) table->field[3]->store(STRING_WITH_LEN("SEQUENCE"), cs); + else if (IS_USER_TEMP_TABLE(share)) + table->field[3]->store(STRING_WITH_LEN("TEMPORARY"), cs);

what about INTERNAL_TMP_TABLE and SYSTEM_TMP_TABLE?

else { DBUG_ASSERT(share->tmp_table == NO_TMP_TABLE); @@ -9032,7 +9096,6 @@ ST_FIELD_INFO tables_fields_info[]= NOT_NULL, "Comment", OPEN_FRM_ONLY), Column("MAX_INDEX_LENGTH",ULonglong(), NULLABLE, "Max_index_length", OPEN_FULL_TABLE), - Column("TEMPORARY", Varchar(1), NULLABLE, "Temporary", OPEN_FRM_ONLY), CEnd() };

Let's remove it in a separate commit. After you implement "TEMPORARY" for table type. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org