Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15867

Slave SQL: Error 'Table 't' is specified twice (ER_UPDATE_TABLE_USED) upon CREATE TABLE .. SELECT

Details

    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.

      Attachments

        Issue Links

          Activity

            a.txt

            With row format this test passes which is strange.

            sachin.setiya.007 Sachin Setiya (Inactive) added a comment - a.txt With row format this test passes which is strange.

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

            sachin.setiya.007 Sachin Setiya (Inactive) added a comment - Ohh got it , in the case of row format the slave does not receive temp table
            sachin.setiya.007 Sachin Setiya (Inactive) added a comment - - edited

            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 &&
            
            

            sachin.setiya.007 Sachin Setiya (Inactive) added a comment - - edited 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 &&

            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);
                }
             
            

            sachin.setiya.007 Sachin Setiya (Inactive) added a comment - 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); }

            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);
            
            

            sachin.setiya.007 Sachin Setiya (Inactive) added a comment - 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);
            sachin.setiya.007 Sachin Setiya (Inactive) added a comment - http://lists.askmonty.org/pipermail/commits/2018-May/012551.html
            Elkin Andrei Elkin added a comment -

            Reviewed to approve provided couple of small improvement is done.

            Elkin Andrei Elkin added a comment - Reviewed to approve provided couple of small improvement is done.

            People

              sachin.setiya.007 Sachin Setiya (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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