[MDEV-15867] Slave SQL: Error 'Table 't' is specified twice (ER_UPDATE_TABLE_USED) upon CREATE TABLE .. SELECT Created: 2018-04-13  Updated: 2018-07-18  Resolved: 2018-07-18

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Replication
Affects Version/s: 10.2, 10.3
Fix Version/s: 10.2.17

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sachin Setiya (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Attachments: Text File a.txt    
Issue Links:
Duplicate
duplicates MDEV-16192 Table 't' is specified twice, both as... Closed
Problem/Incident
is caused by MDEV-5535 cannot reopen temporary table Closed

 Description    

--source include/master-slave.inc
 
CREATE TEMPORARY TABLE t (i INT);
 
CREATE TABLE t AS SELECT * FROM t;
 
 
 
--sync_slave_with_master
 
 
 
# Cleanup
 
--connection master
 
DROP TEMPORARY TABLE t;
 
DROP TABLE t;
 
--source include/rpl_end.inc

10.2 36c0116720

 
2018-04-13 20:16:21 139886080214784 [ERROR] Slave SQL: Error 'Table 't' is specified twice, both as a target for 'CREATE' and as a separate source for data' on query. Default database: 'test'. Query: 'CREATE TABLE t AS SELECT * FROM t', Gtid 0-1-2, Internal MariaDB error code: 1093
 
2018-04-13 20:16:21 139886080214784 [Warning] Slave: Table 't' is specified twice, both as a target for 'CREATE' and as a separate source for data Error_code: 1093
 
2018-04-13 20:16:21 139886080214784 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 470

Reproducible on 10.2, 10.3. Not reproducible on 10.1.

 Comments   
Comment by Sachin Setiya (Inactive) [ 2018-05-16 ]

a.txt

With row format this test passes which is strange.

Comment by Sachin Setiya (Inactive) [ 2018-05-16 ]

Ohh got it , in the case of row format the slave does not receive temp table

Comment by Sachin Setiya (Inactive) [ 2018-05-16 ]

commit b2ae32aafdd2

 55 diff --git a/sql/sql_base.cc b/sql/sql_base.cc
 
 56 index e14fb1b4e5f..e8a741931ed 100644
 
 57 --- a/sql/sql_base.cc
 
 58 +++ b/sql/sql_base.cc
 
 59 @@ -910,8 +910,7 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table,
 
 60  {
 
 61    for (; table; table= table->*link )
 
 62    {
 
 63 -    if ((table->table == 0 || table->table->s->tmp_table == NO_TMP_TABLE) &&
 
 64 -        strcmp(table->db, db_name) == 0 &&
 
 65 +    if (strcmp(table->db, db_name) == 0 &&

Comment by Sachin Setiya (Inactive) [ 2018-05-16 ]

Initial Analysis:-

1st->It only fails when format is mixed/stmt because in row format tmp table is not transfered to slave.

2nd-> The above patch is the introduces different behavior between 10.1 and 10.2

Now there are two ways
1. Restore the patch
But this creates a problems
This stmt will never stop running 

insert into t2 select * from t2; # t2 is temp table

2. We can change condition in mysql_execute_command , If we find that the duplicate table itself is temp table then do not through error.
One might ask a question Then why it works on master but not on slave
Answer is 

if (create_info.or_replace() && !create_info.tmp_table())
 
     {

in master create_info.or_replace is false()

{for this test case, which makes me thinking that master is also buggy}

In Slave however it is turned true by this line

    if (thd->slave_thread &&
 
           slave_ddl_exec_mode_options == SLAVE_EXEC_MODE_IDEMPOTENT &&
 
            !lex->create_info.if_not_exists())
 
    {
 
      create_info.add(DDL_options_st::OPT_OR_REPLACE);
 
      create_info.add(DDL_options_st::OPT_OR_REPLACE_SLAVE_GENERATED);
 
    }

Comment by Sachin Setiya (Inactive) [ 2018-05-16 ]

Initial Patch

diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
 
index 52aa6bf9381..d550aad09c9 100644
 
--- a/sql/sql_parse.cc
 
+++ b/sql/sql_parse.cc
 
@@ -3913,7 +3913,8 @@ mysql_execute_command(THD *thd)
 
         TABLE_LIST *duplicate;
 
         if ((duplicate= unique_table(thd, lex->query_tables,
 
                                      lex->query_tables->next_global,
 
-                                     0)))
 
+                                     0)) && duplicate->table->s->tmp_table
 
+                                     == NO_TMP_TABLE)
 
         {
 
           update_non_unique_table_error(lex->query_tables, "CREATE",
 
                                         duplicate);

Comment by Sachin Setiya (Inactive) [ 2018-05-16 ]

http://lists.askmonty.org/pipermail/commits/2018-May/012551.html

Comment by Andrei Elkin [ 2018-07-17 ]

Reviewed to approve provided couple of small improvement is done.

Generated at Thu Feb 08 08:24:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.