[MDEV-28332] Alter on temporary table causes ER_TABLE_EXISTS_ERROR note - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: N/A
Fix Version/s: 11.2.1
Component/s: Data Definition - Temporary
Labels:
None

Description

~~MDEV-12459~~ introduced a note upon creation of a temporary table which shadows an existing base table; it looks quite natural.
However, this note is also produced upon ALTER of such a table. That is not natural, even though may be understandable from the technical point of view.

create table t (a int);

create temporary table t (b int);

alter table t add c int;

# Cleanup

drop temporary table t;

drop table t;

preview-10.9-MDEV-20119-misc c906db30
alter table t add c int;
Warnings:
Note 1050 Table 't' already exists

Attachments

Issue Links

is caused by

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

Closed

Activity

Ascending order - Click to sort in descending order

Michael Widenius added a comment - 2022-04-20 10:31 - edited

diff --git a/sql/sql_show.cc b/sql/sql_show.cc

index 11d1a739eb2..561aaa3d46a 100644

--- a/sql/sql_show.cc

+++ b/sql/sql_show.cc

@@ -5803,9 +5803,9 @@ static int get_schema_tables_record(THD *thd, TABLE_LIST *tables,

/**

  @brief           Fill IS.table with temporary tables

+ @param[in]       thd                  thread handle

  @param[in]       table                I_S table (TABLE)

- @param[in]       db_name              db name of temporary table

- @param[in]       table_name           table name of temporary table

+ @param[in]       table_name           temporary table

  @return          Operation status

    @retval        0   - success

    @retval        1   - failure

The above doesn't match the function parameters:

void process_i_s_table_temporary_tables(THD *thd, TABLE * table, TABLE *tmp_tbl)

Please update.

diff --git a/sql/sql_table.cc b/sql/sql_table.cc

index f52733c2640..9315ef06e90 100644

--- a/sql/sql_table.cc

+++ b/sql/sql_table.cc

@@ -4369,6 +4369,9 @@ int create_table_impl(THD *thd,

 #else

       warning_given=1;

 #endif /* NO_EMBEDDED_ACCESS_CHECKS */

+      if (create_table_mode == C_ALTER_TABLE_FRM_ONLY || create_table_mode == C_ALTER_TABLE)

+        warning_given= 0;

       if (warning_given)

           push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,

                               ER_TABLE_EXISTS_ERROR,

In the code you are first setting warning_given and then reseting it.

Better to not set warning_given at all.

An alternative patch is:

diff --git a/sql/sql_table.cc b/sql/sql_table.cc

index 9315ef06e90..79c8a946487 100644

--- a/sql/sql_table.cc

+++ b/sql/sql_table.cc

@@ -4357,26 +4357,28 @@ int create_table_impl(THD *thd,

     ddl_log_state_create= 0;

     ddl_log_state_rm= 0;

-    if (ha_table_exists(thd, &orig_db, &orig_table_name, NULL, NULL, NULL))

+    if (create_table_mode != C_ALTER_TABLE_FRM_ONLY &&

+        create_table_mode != C_ALTER_TABLE)

+      if (ha_table_exists(thd, &orig_db, &orig_table_name, NULL, NULL, NULL))

+      {

 #ifndef NO_EMBEDDED_ACCESS_CHECKS

-      TABLE_LIST table_acl_check;

-      bzero((char*) &table_acl_check, sizeof(table_acl_check));

-      table_acl_check.db= orig_db;

-      table_acl_check.table_name= orig_table_name;

-      if (!check_table_access(thd, TABLE_ACLS, &table_acl_check, true, 1, true))

-        warning_given= 1;

+        TABLE_LIST table_acl_check;

+        bzero((char*) &table_acl_check, sizeof(table_acl_check));

+        table_acl_check.db= orig_db;

+        table_acl_check.table_name= orig_table_name;

+        if (!check_table_access(thd, TABLE_ACLS, &table_acl_check, true, 1, true))

+          warning_given= 1;

 #else

-      warning_given=1;

+        warning_given=1;

 #endif /* NO_EMBEDDED_ACCESS_CHECKS */

-      if (create_table_mode == C_ALTER_TABLE_FRM_ONLY || create_table_mode == C_ALTER_TABLE)

-        warning_given= 0;

-      if (warning_given)

+        if (warning_given)

           push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,

                               ER_TABLE_EXISTS_ERROR,

                               ER_THD(thd, ER_TABLE_EXISTS_ERROR),

                               orig_table_name.str);

+      }

Michael Widenius added a comment - 2022-04-20 10:31 - edited diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 11d1a739eb2..561aaa3d46a 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -5803,9 +5803,9 @@ static int get_schema_tables_record(THD *thd, TABLE_LIST *tables, /** @brief Fill IS.table with temporary tables + @param[in] thd thread handle @param[in] table I_S table (TABLE) - @param[in] db_name db name of temporary table - @param[in] table_name table name of temporary table + @param[in] table_name temporary table @return Operation status @retval 0 - success @retval 1 - failure The above doesn't match the function parameters: void process_i_s_table_temporary_tables(THD *thd, TABLE * table, TABLE *tmp_tbl) Please update. diff --git a/sql/sql_table.cc b/sql/sql_table.cc index f52733c2640..9315ef06e90 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4369,6 +4369,9 @@ int create_table_impl(THD *thd, #else warning_given=1; #endif /* NO_EMBEDDED_ACCESS_CHECKS */ + if (create_table_mode == C_ALTER_TABLE_FRM_ONLY || create_table_mode == C_ALTER_TABLE) + warning_given= 0; + if (warning_given) push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, ER_TABLE_EXISTS_ERROR, In the code you are first setting warning_given and then reseting it. Better to not set warning_given at all. An alternative patch is: diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 9315ef06e90..79c8a946487 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4357,26 +4357,28 @@ int create_table_impl(THD *thd, ddl_log_state_create= 0; ddl_log_state_rm= 0; - if (ha_table_exists(thd, &orig_db, &orig_table_name, NULL, NULL, NULL)) + if (create_table_mode != C_ALTER_TABLE_FRM_ONLY && + create_table_mode != C_ALTER_TABLE) { + if (ha_table_exists(thd, &orig_db, &orig_table_name, NULL, NULL, NULL)) + { #ifndef NO_EMBEDDED_ACCESS_CHECKS - TABLE_LIST table_acl_check; - bzero((char*) &table_acl_check, sizeof(table_acl_check)); - table_acl_check.db= orig_db; - table_acl_check.table_name= orig_table_name; - if (!check_table_access(thd, TABLE_ACLS, &table_acl_check, true, 1, true)) - warning_given= 1; + TABLE_LIST table_acl_check; + bzero((char*) &table_acl_check, sizeof(table_acl_check)); + table_acl_check.db= orig_db; + table_acl_check.table_name= orig_table_name; + if (!check_table_access(thd, TABLE_ACLS, &table_acl_check, true, 1, true)) + warning_given= 1; #else - warning_given=1; + warning_given=1; #endif /* NO_EMBEDDED_ACCESS_CHECKS */ - if (create_table_mode == C_ALTER_TABLE_FRM_ONLY || create_table_mode == C_ALTER_TABLE) - warning_given= 0; - if (warning_given) + if (warning_given) push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, ER_TABLE_EXISTS_ERROR, ER_THD(thd, ER_TABLE_EXISTS_ERROR), orig_table_name.str); + } } }

Michael Widenius added a comment - 2022-04-20 10:32

Review

Michael Widenius added a comment - 2022-04-20 10:32 Review

Anel Husakovic added a comment - 2022-04-27 13:47

For last check review https://github.com/MariaDB/server/commit/f49425389a5246b80f26c6d32d86979f4bc8fd62

Anel Husakovic added a comment - 2022-04-27 13:47 For last check review https://github.com/MariaDB/server/commit/f49425389a5246b80f26c6d32d86979f4bc8fd62

MariaDB Server

Alter on temporary table causes ER_TABLE_EXISTS_ERROR note

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration