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

Unexpected ER_ERROR_ON_RENAME upon CREATE OR REPLACE

Details

    Description

      Note: Reproducible on bb-10.11-midenok which has a fix for MDEV-29701

      --source include/have_innodb.inc
      		 
       
      		 
      INSTALL SONAME 'ha_blackhole';
      		 
       
      		 
      CREATE TABLE t1 (a INT) ENGINE=BLACKHOLE;
      		 
      CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=InnoDB;
      		 
      CREATE OR REPLACE TABLE t1 (a INT, FOREIGN KEY(a) REFERENCES t2(pk)) ENGINE=InnoDB;
      		 
       
      		 
      SET FOREIGN_KEY_CHECKS = OFF;
      		 
      ALTER TABLE t1 MODIFY a BIGINT;
      		 
      SET FOREIGN_KEY_CHECKS = ON;
      		 
       
      		 
      CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB;
      		 
      CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB;
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE IF EXISTS t2, t1;

      bb-10.11-midenok 8e82c973e

      		 
      SET FOREIGN_KEY_CHECKS = ON;
      		 
      CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB;
      		 
      bug2.acor5a 'innodb'                     [ fail ]
      		 
              Test ended at 2022-10-12 22:33:47
      		 
       
      		 
      CURRENT_TEST: bug2.acor5a
      		 
      mysqltest: At line 13: query 'CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB' failed: ER_ERROR_ON_RENAME (1025): Error on rename of './test/t1' to './test/#sql-backup-2ff49c-4-t1' (errno: 150 "Foreign key constraint is incorrectly formed")

      If we allow the test to run further, the next CREATE OR REPLACE causes the assertion failure:

      mariadbd: /data/src/bb-10.11-midenok-gcov/storage/innobase/row/row0mysql.cc:2676: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, rename_fk): Assertion `err != DB_DUPLICATE_KEY' failed.
      		 
      221012 22:34:59 [ERROR] mysqld got signal 6 ;
      		 
       
      		 
      #7  0x00007f2397629662 in __GI___assert_fail (assertion=0x5565125f0195 "err != DB_DUPLICATE_KEY", file=0x5565125ee7b8 "/data/src/bb-10.11-midenok-gcov/storage/innobase/row/row0mysql.cc", line=2676, function=0x5565125f0060 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, rename_fk)") at assert.c:101
      		 
      #8  0x0000556511b3c2b0 in row_rename_table_for_mysql (old_name=0x7f238039f950 "test/#sql-create-2ffb09-4-t1", new_name=0x7f238039f750 "test/t1", trx=0x7f2391c6b680, fk=RENAME_FK) at /data/src/bb-10.11-midenok-gcov/storage/innobase/row/row0mysql.cc:2676
      		 
      #9  0x00005565117f2e6c in innobase_rename_table (trx=0x7f2391c6b680, from=0x7f23803a04e0 "./test/#sql-create-2ffb09-4-t1", to=0x7f23803a06e0 "./test/t1", fk=RENAME_FK) at /data/src/bb-10.11-midenok-gcov/storage/innobase/handler/ha_innodb.cc:13779
      		 
      #10 0x00005565117f6600 in ha_innobase::rename_table (this=0x7f23400177c8, from=0x7f23803a04e0 "./test/#sql-create-2ffb09-4-t1", to=0x7f23803a06e0 "./test/t1") at /data/src/bb-10.11-midenok-gcov/storage/innobase/handler/ha_innodb.cc:14179
      		 
      #11 0x00005565110efb13 in handler::ha_rename_table (this=0x7f23400177c8, from=0x7f23803a04e0 "./test/#sql-create-2ffb09-4-t1", to=0x7f23803a06e0 "./test/t1") at /data/src/bb-10.11-midenok-gcov/sql/handler.cc:5346
      		 
      #12 0x0000556510bcf947 in mysql_rename_table (base=0x556516b34258, old_db=0x7f23803a19b8, old_name=0x7f23803a11f0, new_db=0x7f23803a11c0, new_name=0x7f23803a1200, id=0x7f23803a1210, flags=1) at /data/src/bb-10.11-midenok-gcov/sql/sql_table.cc:5566
      		 
      #13 0x0000556510a60af8 in rename_table_and_triggers (thd=0x7f2340000db8, param=0x7f23803a11f0, ddl_log_state=0x0, ren_table=0x7f23803a19b8, new_db=0x7f23803a11c0, skip_error=false, force_if_exists=0x7f23803a11b6) at /data/src/bb-10.11-midenok-gcov/sql/sql_rename.cc:384
      		 
      #14 0x0000556510bc9546 in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f23803a1780, thd=0x7f2340000db8, orig_table=0x7f23400156d0) at /data/src/bb-10.11-midenok-gcov/sql/sql_table.cc:4490
      		 
      #15 0x0000556510bcdb7f in mysql_create_table (thd=0x7f2340000db8, create_table=0x7f23400156d0, create_info=0x7f23803a1780, alter_info=0x7f23803a1690) at /data/src/bb-10.11-midenok-gcov/sql/sql_table.cc:5285
      		 
      #16 0x0000556510bfa1f8 in Sql_cmd_create_table_like::execute (this=0x7f2340015670, thd=0x7f2340000db8) at /data/src/bb-10.11-midenok-gcov/sql/sql_table.cc:12858
      		 
      #17 0x00005565109db934 in mysql_execute_command (thd=0x7f2340000db8, is_called_from_prepared_stmt=false) at /data/src/bb-10.11-midenok-gcov/sql/sql_parse.cc:5997
      		 
      #18 0x00005565109e72cf in mysql_parse (thd=0x7f2340000db8, rawbuf=0x7f23400155b0 "CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB", length=48, parser_state=0x7f23803a2390) at /data/src/bb-10.11-midenok-gcov/sql/sql_parse.cc:8023
      		 
      #19 0x00005565109be54a in dispatch_command (command=COM_QUERY, thd=0x7f2340000db8, packet=0x7f234000bb39 "CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB", packet_length=48, blocking=true) at /data/src/bb-10.11-midenok-gcov/sql/sql_parse.cc:1894
      		 
      #20 0x00005565109bb705 in do_command (thd=0x7f2340000db8, blocking=true) at /data/src/bb-10.11-midenok-gcov/sql/sql_parse.cc:1407
      		 
      #21 0x0000556510d2e1fa in do_handle_one_connection (connect=0x5565171cb388, put_in_cache=true) at /data/src/bb-10.11-midenok-gcov/sql/sql_connect.cc:1416
      		 
      #22 0x0000556510d2dbf0 in handle_one_connection (arg=0x5565171e0e08) at /data/src/bb-10.11-midenok-gcov/sql/sql_connect.cc:1318
      		 
      #23 0x0000556511640399 in pfs_spawn_thread (arg=0x5565171caef8) at /data/src/bb-10.11-midenok-gcov/storage/perfschema/pfs.cc:2201
      		 
      #24 0x00007f2397b05ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
      		 
      #25 0x00007f23976f4aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

      Before MDEV-25292 both CREATE OR REPLACE statements succeed.

      Attachments

        Issue Links

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25292 Atomic CREATE OR REPLACE TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29701 Inconsistencies and eventual failure upon CREATE OR REPLACE with foreign keys

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29783 ER_NO_SUCH_TABLE_IN_ENGINE after failed CREATE OR REPLACE with FK violation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            midenok Aleksey Midenkov added a comment -

            Please review bb-10.11-midenok

            midenok Aleksey Midenkov added a comment - Please review bb-10.11-midenok
            marko Marko Mäkelä added a comment -

            The branch appears to have been force-pushed since my review of MDEV-29701, but none of my review comments have been addressed in the rebased commit.

            Please add a test to cover also the cases where the foreign key constraints are being preserved or removed as part of the CREATE OR REPLACE TABLE t1:

            create table t1 (a int) engine=innodb;
            		 
            create table t2 (pk int primary key) engine=innodb;
            		 
            create or replace table t1 (a int, foreign key(a) references t2(pk)) engine=innodb;
            		 
            set foreign_key_checks = off;
            		 
            --echo # Introduce a type mismatch for the foreign key constraint
            		 
            alter table t1 modify a bigint;
            		 
            set foreign_key_checks = on;
            		 
            create or replace table t1 (a int) engine=innodb;
            		 
            --error ER_ERROR_ON_RENAME
            		 
            create or replace table t1 (a bigint references t2(pk)) engine=innodb;
            		 
            set foreign_key_checks = off;
            		 
            --echo # Add a foreign key constraint with a type mismatch
            		 
            create or replace table t1 (a bigint references t2(pk)) engine=innodb;
            		 
            show create table t1;
            		 
            --echo # Retain the foreign key constraint with a type mismatch
            		 
            create or replace table t1 (a bigint references t2(pk)) engine=innodb;
            		 
            set foreign_key_checks = on;
            		 
            --error ER_ERROR_ON_RENAME
            		 
            create or replace table t1 (b bigint references t2(pk)) engine=innodb;
            		 
            --echo # Both these must reflect "a bigint references t2(pk)" and not "b"
            		 
            show create table t1;
            		 
            select … from information_schema.innodb_sys_columns …;
            		 
            create or replace table t1 (b int references t2(pk)) engine=innodb;
            		 
            show create table t1;
            		 
            drop tables t1, t2;

            Even better would be to add some DML (with foreign_key_checks=ON) between each step to demonstrate that the FOREIGN KEY constraint is being enforced.

            marko Marko Mäkelä added a comment - The branch appears to have been force-pushed since my review of MDEV-29701 , but none of my review comments have been addressed in the rebased commit . Please add a test to cover also the cases where the foreign key constraints are being preserved or removed as part of the CREATE OR REPLACE TABLE t1 : create table t1 (a int ) engine=innodb; create table t2 (pk int primary key ) engine=innodb; create or replace table t1 (a int , foreign key (a) references t2(pk)) engine=innodb; set foreign_key_checks = off ; --echo # Introduce a type mismatch for the foreign key constraint alter table t1 modify a bigint ; set foreign_key_checks = on ; create or replace table t1 (a int ) engine=innodb; --error ER_ERROR_ON_RENAME create or replace table t1 (a bigint references t2(pk)) engine=innodb; set foreign_key_checks = off ; --echo # Add a foreign key constraint with a type mismatch create or replace table t1 (a bigint references t2(pk)) engine=innodb; show create table t1; --echo # Retain the foreign key constraint with a type mismatch create or replace table t1 (a bigint references t2(pk)) engine=innodb; set foreign_key_checks = on ; --error ER_ERROR_ON_RENAME create or replace table t1 (b bigint references t2(pk)) engine=innodb; --echo # Both these must reflect "a bigint references t2(pk)" and not "b" show create table t1; select … from information_schema.innodb_sys_columns …; create or replace table t1 (b int references t2(pk)) engine=innodb; show create table t1; drop tables t1, t2; Even better would be to add some DML (with foreign_key_checks=ON ) between each step to demonstrate that the FOREIGN KEY constraint is being enforced.
            midenok Aleksey Midenkov added a comment -

            marko Your comments were addressed in replies.

            Did you check your test in 10.9? It doesn't work (no, it's not about wrong error code in --error):

            mysqltest: At line 15: query 'create or replace table t1 (a bigint references t2(pk)) engine=innodb' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")

            midenok Aleksey Midenkov added a comment - marko Your comments were addressed in replies. Did you check your test in 10.9? It doesn't work (no, it's not about wrong error code in --error): mysqltest: At line 15: query 'create or replace table t1 (a bigint references t2(pk)) engine=innodb' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
            marko Marko Mäkelä added a comment -

            I thought that the intention of my (admittedly untested) test case scenario would be obvious.

            marko Marko Mäkelä added a comment - I thought that the intention of my (admittedly untested) test case scenario would be obvious.
            midenok Aleksey Midenkov added a comment -

            If your intention doesn't work, there is nothing to do.

            midenok Aleksey Midenkov added a comment - If your intention doesn't work, there is nothing to do.

            People

              midenok Aleksey Midenkov
              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.