[MDEV-29701] Inconsistencies and eventual failure upon CREATE OR REPLACE with foreign keys - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.11
Fix Version/s: N/A
Component/s: Data Definition - Create Table, Storage Engine - InnoDB
Labels:
- regression

Description

--source include/have_innodb.inc

CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = OFF;

CREATE TABLE t2 (

  pk INT PRIMARY KEY,

  a INT,

  FOREIGN KEY fk1 (a) REFERENCES t1 (f1),

  FOREIGN KEY fk2 (a) REFERENCES t3 (x)

) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = ON;

--error ER_ERROR_ON_RENAME

CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

# Cleanup

DROP TABLE IF EXISTS t3, t2, t1;

So, creation of t2 requires FOREIGN_KEY_CHECKS=OFF because it references non-existing t3.

Then t3 is created, but it doesn't contain a key on x column, so it's not valid from the t2 FK perspective. It is still happening under FOREIGN_KEY_CHECKS=OFF, so it's unclear whether it should succeed or not. The problem is, CREATE OR REPLACE succeeds, while the same CREATE fails. Naturally it shouldn't be so.

10.11 ba875e93
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = ON;

same test case with CREATE instead of CREATE OR REPLACE
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE TABLE t3 (x INT) ENGINE=InnoDB;
bug.dup2 'innodb' [ fail ]
Test ended at 2022-10-04 19:49:22

CURRENT_TEST: bug.dup2
mysqltest: At line 14: query 'CREATE TABLE t3 (x INT) ENGINE=InnoDB' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed")

Before MDEV-25292 both CREATE OR REPLACE and CREATE fail.

Back to the original test case with CREATE OR REPLACE t3; after it succeeded and we set FOREIGN_KEY_CHECKS back to ON, we CREATE OR REPLACE TABLE t2. The existing one still has a bad foreign key, but we create a new one without foreign keys, so one would expect it should succeed. However, it fails.

CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;

ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36f9c3-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")

If we instead do DROP TABLE and CREATE TABLE, it naturally succeeds.

Further, if we accept that CREATE OR REPLACE fails, it should not change the structure of t2 (atomic, right?). But it does. If we add SHOW CREATE before and after the failed statement, we see:

SHOW CREATE TABLE t2;

Table	Create Table

t2	CREATE TABLE `t2` (

  `pk` int(11) NOT NULL,

  `a` int(11) DEFAULT NULL,

  PRIMARY KEY (`pk`),

  KEY `fk2` (`a`),

  CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`f1`),

  CONSTRAINT `fk2` FOREIGN KEY (`a`) REFERENCES `t3` (`x`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;

ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36fae4-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")

SHOW CREATE TABLE t2;

Table	Create Table

t2	CREATE TABLE `t2` (

  `pk` int(11) NOT NULL,

  `a` int(11) DEFAULT NULL,

  PRIMARY KEY (`pk`),

  KEY `fk2` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

So, it keeps the old table but loses the foreign keys upon the failed CREATE OR REPLACE (both of them, even the good one).

And finally, after all this, the first CREATE OR REPLACE on t1 goes smoothly, but the second identical one fails the assertion:

10.11 ba875e93
2022-10-04 19:59:28 4 [Warning] InnoDB: In ALTER TABLE `test`.`t3` has or is referenced in foreign key constraints which are not compatible with the new table definition.
2022-10-04 19:59:28 4 [ERROR] InnoDB: In RENAME TABLE table `test`.`#sql-backup-36fc27-4-t2` is referenced in foreign key constraints which are not compatible with the new table definition.
mariadbd: /data/src/10.11/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.
221004 19:59:28 [ERROR] mysqld got signal 6 ;

#7 0x00007f698752c662 in __GI___assert_fail (assertion=0x55b049477e4d "err != DB_DUPLICATE_KEY", file=0x55b049476480 "/data/src/10.11/storage/innobase/row/row0mysql.cc", line=2676, function=0x55b049477d18 "dberr_t row_rename_table_for_mysql(const char, const char, trx_t*, rename_fk)") at assert.c:101
#8 0x000055b048cebd5d in row_rename_table_for_mysql (old_name=0x7f69802ad980 "test/t1", new_name=0x7f69802ad780 "test/#sql-backup-36fc27-4-t1", trx=0x7f6981b6e680, fk=RENAME_FK) at /data/src/10.11/storage/innobase/row/row0mysql.cc:2676
#9 0x000055b048b34843 in innobase_rename_table (trx=0x7f6981b6e680, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1", fk=RENAME_FK) at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:13792
#10 0x000055b048b36249 in ha_innobase::rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:14192
#11 0x000055b0487478b1 in handler::ha_rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/sql/handler.cc:5347
#12 0x000055b04848c707 in mysql_rename_table (base=0x55b04bf92168, old_db=0x7f69500156b8, old_name=0x7f69802af220, new_db=0x7f69802afa18, new_name=0x7f69802af230, id=0x7f69802af240, flags=2) at /data/src/10.11/sql/sql_table.cc:5536
#13 0x000055b0483c12de in rename_table_and_triggers (thd=0x7f6950000db8, param=0x7f69802af220, ddl_log_state=0x0, ren_table=0x7f69500156b8, new_db=0x7f69802afa18, skip_error=false, force_if_exists=0x7f69802af1ee) at /data/src/10.11/sql/sql_rename.cc:384
#14 0x000055b04848921d in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f69802af7b0, thd=0x7f6950000db8, orig_table=0x7f69500156b8) at /data/src/10.11/sql/sql_table.cc:4462
#15 0x000055b04848b80b in mysql_create_table (thd=0x7f6950000db8, create_table=0x7f69500156b8, create_info=0x7f69802af7b0, alter_info=0x7f69802af6c0) at /data/src/10.11/sql/sql_table.cc:5255
#16 0x000055b0484a2e87 in Sql_cmd_create_table_like::execute (this=0x7f6950015658, thd=0x7f6950000db8) at /data/src/10.11/sql/sql_table.cc:12816
#17 0x000055b04837c88e in mysql_execute_command (thd=0x7f6950000db8, is_called_from_prepared_stmt=false) at /data/src/10.11/sql/sql_parse.cc:5997
#18 0x000055b048382a00 in mysql_parse (thd=0x7f6950000db8, rawbuf=0x7f6950015580 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", length=61, parser_state=0x7f69802b03c0) at /data/src/10.11/sql/sql_parse.cc:8037
#19 0x000055b04836eebb in dispatch_command (command=COM_QUERY, thd=0x7f6950000db8, packet=0x7f695000bb09 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", packet_length=61, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1894
#20 0x000055b04836d894 in do_command (thd=0x7f6950000db8, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1407
#21 0x000055b048548694 in do_handle_one_connection (connect=0x55b04c628fa8, put_in_cache=true) at /data/src/10.11/sql/sql_connect.cc:1416
#22 0x000055b0485483ff in handle_one_connection (arg=0x55b04c63e798) at /data/src/10.11/sql/sql_connect.cc:1318
#23 0x000055b048a48102 in pfs_spawn_thread (arg=0x55b04c628b18) at /data/src/10.11/storage/perfschema/pfs.cc:2201
#24 0x00007f6987a08ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
#25 0x00007f69875f7aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Attachments

Issue Links

is caused by

MDEV-25292 Atomic CREATE OR REPLACE TABLE

In Testing

relates to

MDEV-29779 Unexpected ER_ERROR_ON_RENAME upon CREATE OR REPLACE

Closed

MDEV-29783 ER_NO_SUCH_TABLE_IN_ENGINE after failed CREATE OR REPLACE with FK violation

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2022-10-04 17:02

Elena Stepanova made changes - 2022-10-04 17:02

Field	Original Value	New Value
Link		This issue is caused by MDEV-25292 [ MDEV-25292 ]

Elena Stepanova made changes - 2022-10-04 17:07

Description

Since the test case involves turning off foreign key checks, it is not always obvious what the expected result should be for each statement. There are several checkpoints here where things may have gone wrong.
First, I will put here the test case as a whole, and then intermediate results which I suspect to be incorrect. The final assertion failure is obviously a remote aftermath and as such is the least of our problems, but I'll paste it too for completeness.

{code:sql}
--source include/have_innodb.inc

CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = OFF;

CREATE TABLE t2 (
  pk INT PRIMARY KEY,
  a INT,
  FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
  FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = ON;

--error ER_ERROR_ON_RENAME
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

# Cleanup
DROP TABLE IF EXISTS t3, t2, t1;
{code}

So, creation of {{t2}} requires {{FOREIGN_KEY_CHECKS=OFF}} because it references non-existing {{t3}}.

Then {{t3}} is created, but it doesn't contain a key on {{x}} column, so it's not valid from the t2 FK perspective. It is still happening under {{FOREIGN_KEY_CHECKS=OFF}}, so it's unclear whether it should succeed or not. The problem is, {{CREATE OR REPLACE}} succeeds, while the same {{CREATE}} fails. Naturally it shouldn't be so.

{code:sql|title=10.11 ba875e93, same test case with CREATE instead of CREATE OR REPLACE}
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = ON;
{code}
{code:sql}
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE TABLE t3 (x INT) ENGINE=InnoDB;
bug.dup2 'innodb' [ fail ]
        Test ended at 2022-10-04 19:49:22

CURRENT_TEST: bug.dup2
mysqltest: At line 14: query 'CREATE TABLE t3 (x INT) ENGINE=InnoDB' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed")
{code}

Before MDEV-25292 both {{CREATE OR REPLACE}} and {{CREATE}} fail.

Back to the original test case with {{CREATE OR REPLACE t3}}; after it succeeded and we set {{FOREIGN_KEY_CHECKS}} back to {{ON}}, we CREATE OR REPLACE TABLE t2. The existing one still has a bad foreign key, but we create a new one without foreign keys, so one would expect it should succeed. However, it fails.

{code:sql}
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;
ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36f9c3-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")
{code}

If we instead do {{DROP TABLE}} and {{CREATE TABLE}}, it naturally succeeds.

Further, if we accept that {{CREATE OR REPLACE}} fails, it should not change the structure of {{t2}} (atomic, right?). But it does. If we add {{SHOW CREATE}} before and after the failed statement, we see:

{code:sql}
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `fk2` (`a`),
  CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`f1`),
  CONSTRAINT `fk2` FOREIGN KEY (`a`) REFERENCES `t3` (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;
ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36fae4-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `fk2` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
{code}

So, it keeps the old table but loses the foreign keys upon the failed CREATE OR REPLACE (both of them, even the good one).

And finally, after all this, the first CREATE OR REPLACE on t1 goes smoothly, but the second identical one fails the assertion:

{noformat:title=10.11 ba875e93}
2022-10-04 19:59:28 4 [Warning] InnoDB: In ALTER TABLE `test`.`t3` has or is referenced in foreign key constraints which are not compatible with the new table definition.
2022-10-04 19:59:28 4 [ERROR] InnoDB: In RENAME TABLE table `test`.`#sql-backup-36fc27-4-t2` is referenced in foreign key constraints which are not compatible with the new table definition.
mariadbd: /data/src/10.11/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.
221004 19:59:28 [ERROR] mysqld got signal 6 ;

#7 0x00007f698752c662 in __GI___assert_fail (assertion=0x55b049477e4d "err != DB_DUPLICATE_KEY", file=0x55b049476480 "/data/src/10.11/storage/innobase/row/row0mysql.cc", line=2676, function=0x55b049477d18 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, rename_fk)") at assert.c:101
#8 0x000055b048cebd5d in row_rename_table_for_mysql (old_name=0x7f69802ad980 "test/t1", new_name=0x7f69802ad780 "test/#sql-backup-36fc27-4-t1", trx=0x7f6981b6e680, fk=RENAME_FK) at /data/src/10.11/storage/innobase/row/row0mysql.cc:2676
#9 0x000055b048b34843 in innobase_rename_table (trx=0x7f6981b6e680, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1", fk=RENAME_FK) at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:13792
#10 0x000055b048b36249 in ha_innobase::rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:14192
#11 0x000055b0487478b1 in handler::ha_rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/sql/handler.cc:5347
#12 0x000055b04848c707 in mysql_rename_table (base=0x55b04bf92168, old_db=0x7f69500156b8, old_name=0x7f69802af220, new_db=0x7f69802afa18, new_name=0x7f69802af230, id=0x7f69802af240, flags=2) at /data/src/10.11/sql/sql_table.cc:5536
#13 0x000055b0483c12de in rename_table_and_triggers (thd=0x7f6950000db8, param=0x7f69802af220, ddl_log_state=0x0, ren_table=0x7f69500156b8, new_db=0x7f69802afa18, skip_error=false, force_if_exists=0x7f69802af1ee) at /data/src/10.11/sql/sql_rename.cc:384
#14 0x000055b04848921d in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f69802af7b0, thd=0x7f6950000db8, orig_table=0x7f69500156b8) at /data/src/10.11/sql/sql_table.cc:4462
#15 0x000055b04848b80b in mysql_create_table (thd=0x7f6950000db8, create_table=0x7f69500156b8, create_info=0x7f69802af7b0, alter_info=0x7f69802af6c0) at /data/src/10.11/sql/sql_table.cc:5255
#16 0x000055b0484a2e87 in Sql_cmd_create_table_like::execute (this=0x7f6950015658, thd=0x7f6950000db8) at /data/src/10.11/sql/sql_table.cc:12816
#17 0x000055b04837c88e in mysql_execute_command (thd=0x7f6950000db8, is_called_from_prepared_stmt=false) at /data/src/10.11/sql/sql_parse.cc:5997
#18 0x000055b048382a00 in mysql_parse (thd=0x7f6950000db8, rawbuf=0x7f6950015580 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", length=61, parser_state=0x7f69802b03c0) at /data/src/10.11/sql/sql_parse.cc:8037
#19 0x000055b04836eebb in dispatch_command (command=COM_QUERY, thd=0x7f6950000db8, packet=0x7f695000bb09 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", packet_length=61, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1894
#20 0x000055b04836d894 in do_command (thd=0x7f6950000db8, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1407
#21 0x000055b048548694 in do_handle_one_connection (connect=0x55b04c628fa8, put_in_cache=true) at /data/src/10.11/sql/sql_connect.cc:1416
#22 0x000055b0485483ff in handle_one_connection (arg=0x55b04c63e798) at /data/src/10.11/sql/sql_connect.cc:1318
#23 0x000055b048a48102 in pfs_spawn_thread (arg=0x55b04c628b18) at /data/src/10.11/storage/perfschema/pfs.cc:2201
#24 0x00007f6987a08ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
#25 0x00007f69875f7aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
{noformat}

Since the test case involves turning off foreign key checks, it is not always obvious what the expected result should be for each statement. There are several checkpoints here where things may have gone wrong.
First, I will put here the test case as a whole, and then intermediate results which I suspect to be incorrect. The final assertion failure is obviously a remote aftermath and as such is the least of our problems, but I'll paste it too for completeness.

{code:sql}
--source include/have_innodb.inc

CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = OFF;

CREATE TABLE t2 (
  pk INT PRIMARY KEY,
  a INT,
  FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
  FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = ON;

--error ER_ERROR_ON_RENAME
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

# Cleanup
DROP TABLE IF EXISTS t3, t2, t1;
{code}

So, creation of {{t2}} requires {{FOREIGN_KEY_CHECKS=OFF}} because it references non-existing {{t3}}.

Then {{t3}} is created, but it doesn't contain a key on {{x}} column, so it's not valid from the t2 FK perspective. It is still happening under {{FOREIGN_KEY_CHECKS=OFF}}, so it's unclear whether it should succeed or not. The problem is, {{CREATE OR REPLACE}} succeeds, while the same {{CREATE}} fails. Naturally it shouldn't be so.

{code:sql|title=10.11 ba875e93}
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = ON;
{code}
{code:sql|same test case with CREATE instead of CREATE OR REPLACE}
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE TABLE t3 (x INT) ENGINE=InnoDB;
bug.dup2 'innodb' [ fail ]
        Test ended at 2022-10-04 19:49:22

CURRENT_TEST: bug.dup2
mysqltest: At line 14: query 'CREATE TABLE t3 (x INT) ENGINE=InnoDB' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed")
{code}

Before MDEV-25292 both {{CREATE OR REPLACE}} and {{CREATE}} fail.

Back to the original test case with {{CREATE OR REPLACE t3}}; after it succeeded and we set {{FOREIGN_KEY_CHECKS}} back to {{ON}}, we CREATE OR REPLACE TABLE t2. The existing one still has a bad foreign key, but we create a new one without foreign keys, so one would expect it should succeed. However, it fails.

{code:sql}
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;
ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36f9c3-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")
{code}

If we instead do {{DROP TABLE}} and {{CREATE TABLE}}, it naturally succeeds.

Further, if we accept that {{CREATE OR REPLACE}} fails, it should not change the structure of {{t2}} (atomic, right?). But it does. If we add {{SHOW CREATE}} before and after the failed statement, we see:

{code:sql}
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `fk2` (`a`),
  CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`f1`),
  CONSTRAINT `fk2` FOREIGN KEY (`a`) REFERENCES `t3` (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;
ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36fae4-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `fk2` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
{code}

So, it keeps the old table but loses the foreign keys upon the failed CREATE OR REPLACE (both of them, even the good one).

And finally, after all this, the first CREATE OR REPLACE on t1 goes smoothly, but the second identical one fails the assertion:

{noformat:title=10.11 ba875e93}
2022-10-04 19:59:28 4 [Warning] InnoDB: In ALTER TABLE `test`.`t3` has or is referenced in foreign key constraints which are not compatible with the new table definition.
2022-10-04 19:59:28 4 [ERROR] InnoDB: In RENAME TABLE table `test`.`#sql-backup-36fc27-4-t2` is referenced in foreign key constraints which are not compatible with the new table definition.
mariadbd: /data/src/10.11/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.
221004 19:59:28 [ERROR] mysqld got signal 6 ;

#7 0x00007f698752c662 in __GI___assert_fail (assertion=0x55b049477e4d "err != DB_DUPLICATE_KEY", file=0x55b049476480 "/data/src/10.11/storage/innobase/row/row0mysql.cc", line=2676, function=0x55b049477d18 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, rename_fk)") at assert.c:101
#8 0x000055b048cebd5d in row_rename_table_for_mysql (old_name=0x7f69802ad980 "test/t1", new_name=0x7f69802ad780 "test/#sql-backup-36fc27-4-t1", trx=0x7f6981b6e680, fk=RENAME_FK) at /data/src/10.11/storage/innobase/row/row0mysql.cc:2676
#9 0x000055b048b34843 in innobase_rename_table (trx=0x7f6981b6e680, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1", fk=RENAME_FK) at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:13792
#10 0x000055b048b36249 in ha_innobase::rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:14192
#11 0x000055b0487478b1 in handler::ha_rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/sql/handler.cc:5347
#12 0x000055b04848c707 in mysql_rename_table (base=0x55b04bf92168, old_db=0x7f69500156b8, old_name=0x7f69802af220, new_db=0x7f69802afa18, new_name=0x7f69802af230, id=0x7f69802af240, flags=2) at /data/src/10.11/sql/sql_table.cc:5536
#13 0x000055b0483c12de in rename_table_and_triggers (thd=0x7f6950000db8, param=0x7f69802af220, ddl_log_state=0x0, ren_table=0x7f69500156b8, new_db=0x7f69802afa18, skip_error=false, force_if_exists=0x7f69802af1ee) at /data/src/10.11/sql/sql_rename.cc:384
#14 0x000055b04848921d in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f69802af7b0, thd=0x7f6950000db8, orig_table=0x7f69500156b8) at /data/src/10.11/sql/sql_table.cc:4462
#15 0x000055b04848b80b in mysql_create_table (thd=0x7f6950000db8, create_table=0x7f69500156b8, create_info=0x7f69802af7b0, alter_info=0x7f69802af6c0) at /data/src/10.11/sql/sql_table.cc:5255
#16 0x000055b0484a2e87 in Sql_cmd_create_table_like::execute (this=0x7f6950015658, thd=0x7f6950000db8) at /data/src/10.11/sql/sql_table.cc:12816
#17 0x000055b04837c88e in mysql_execute_command (thd=0x7f6950000db8, is_called_from_prepared_stmt=false) at /data/src/10.11/sql/sql_parse.cc:5997
#18 0x000055b048382a00 in mysql_parse (thd=0x7f6950000db8, rawbuf=0x7f6950015580 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", length=61, parser_state=0x7f69802b03c0) at /data/src/10.11/sql/sql_parse.cc:8037
#19 0x000055b04836eebb in dispatch_command (command=COM_QUERY, thd=0x7f6950000db8, packet=0x7f695000bb09 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", packet_length=61, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1894
#20 0x000055b04836d894 in do_command (thd=0x7f6950000db8, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1407
#21 0x000055b048548694 in do_handle_one_connection (connect=0x55b04c628fa8, put_in_cache=true) at /data/src/10.11/sql/sql_connect.cc:1416
#22 0x000055b0485483ff in handle_one_connection (arg=0x55b04c63e798) at /data/src/10.11/sql/sql_connect.cc:1318
#23 0x000055b048a48102 in pfs_spawn_thread (arg=0x55b04c628b18) at /data/src/10.11/storage/perfschema/pfs.cc:2201
#24 0x00007f6987a08ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
#25 0x00007f69875f7aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
{noformat}

Elena Stepanova made changes - 2022-10-04 17:07

Description

Since the test case involves turning off foreign key checks, it is not always obvious what the expected result should be for each statement. There are several checkpoints here where things may have gone wrong.
First, I will put here the test case as a whole, and then intermediate results which I suspect to be incorrect. The final assertion failure is obviously a remote aftermath and as such is the least of our problems, but I'll paste it too for completeness.

{code:sql}
--source include/have_innodb.inc

CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = OFF;

CREATE TABLE t2 (
  pk INT PRIMARY KEY,
  a INT,
  FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
  FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = ON;

--error ER_ERROR_ON_RENAME
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

# Cleanup
DROP TABLE IF EXISTS t3, t2, t1;
{code}

So, creation of {{t2}} requires {{FOREIGN_KEY_CHECKS=OFF}} because it references non-existing {{t3}}.

Then {{t3}} is created, but it doesn't contain a key on {{x}} column, so it's not valid from the t2 FK perspective. It is still happening under {{FOREIGN_KEY_CHECKS=OFF}}, so it's unclear whether it should succeed or not. The problem is, {{CREATE OR REPLACE}} succeeds, while the same {{CREATE}} fails. Naturally it shouldn't be so.

{code:sql|title=10.11 ba875e93}
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = ON;
{code}
{code:sql|same test case with CREATE instead of CREATE OR REPLACE}
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE TABLE t3 (x INT) ENGINE=InnoDB;
bug.dup2 'innodb' [ fail ]
        Test ended at 2022-10-04 19:49:22

CURRENT_TEST: bug.dup2
mysqltest: At line 14: query 'CREATE TABLE t3 (x INT) ENGINE=InnoDB' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed")
{code}

Before MDEV-25292 both {{CREATE OR REPLACE}} and {{CREATE}} fail.

Back to the original test case with {{CREATE OR REPLACE t3}}; after it succeeded and we set {{FOREIGN_KEY_CHECKS}} back to {{ON}}, we CREATE OR REPLACE TABLE t2. The existing one still has a bad foreign key, but we create a new one without foreign keys, so one would expect it should succeed. However, it fails.

{code:sql}
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;
ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36f9c3-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")
{code}

If we instead do {{DROP TABLE}} and {{CREATE TABLE}}, it naturally succeeds.

Further, if we accept that {{CREATE OR REPLACE}} fails, it should not change the structure of {{t2}} (atomic, right?). But it does. If we add {{SHOW CREATE}} before and after the failed statement, we see:

{code:sql}
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `fk2` (`a`),
  CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`f1`),
  CONSTRAINT `fk2` FOREIGN KEY (`a`) REFERENCES `t3` (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;
ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36fae4-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `fk2` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
{code}

So, it keeps the old table but loses the foreign keys upon the failed CREATE OR REPLACE (both of them, even the good one).

And finally, after all this, the first CREATE OR REPLACE on t1 goes smoothly, but the second identical one fails the assertion:

{noformat:title=10.11 ba875e93}
2022-10-04 19:59:28 4 [Warning] InnoDB: In ALTER TABLE `test`.`t3` has or is referenced in foreign key constraints which are not compatible with the new table definition.
2022-10-04 19:59:28 4 [ERROR] InnoDB: In RENAME TABLE table `test`.`#sql-backup-36fc27-4-t2` is referenced in foreign key constraints which are not compatible with the new table definition.
mariadbd: /data/src/10.11/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.
221004 19:59:28 [ERROR] mysqld got signal 6 ;

#7 0x00007f698752c662 in __GI___assert_fail (assertion=0x55b049477e4d "err != DB_DUPLICATE_KEY", file=0x55b049476480 "/data/src/10.11/storage/innobase/row/row0mysql.cc", line=2676, function=0x55b049477d18 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, rename_fk)") at assert.c:101
#8 0x000055b048cebd5d in row_rename_table_for_mysql (old_name=0x7f69802ad980 "test/t1", new_name=0x7f69802ad780 "test/#sql-backup-36fc27-4-t1", trx=0x7f6981b6e680, fk=RENAME_FK) at /data/src/10.11/storage/innobase/row/row0mysql.cc:2676
#9 0x000055b048b34843 in innobase_rename_table (trx=0x7f6981b6e680, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1", fk=RENAME_FK) at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:13792
#10 0x000055b048b36249 in ha_innobase::rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:14192
#11 0x000055b0487478b1 in handler::ha_rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/sql/handler.cc:5347
#12 0x000055b04848c707 in mysql_rename_table (base=0x55b04bf92168, old_db=0x7f69500156b8, old_name=0x7f69802af220, new_db=0x7f69802afa18, new_name=0x7f69802af230, id=0x7f69802af240, flags=2) at /data/src/10.11/sql/sql_table.cc:5536
#13 0x000055b0483c12de in rename_table_and_triggers (thd=0x7f6950000db8, param=0x7f69802af220, ddl_log_state=0x0, ren_table=0x7f69500156b8, new_db=0x7f69802afa18, skip_error=false, force_if_exists=0x7f69802af1ee) at /data/src/10.11/sql/sql_rename.cc:384
#14 0x000055b04848921d in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f69802af7b0, thd=0x7f6950000db8, orig_table=0x7f69500156b8) at /data/src/10.11/sql/sql_table.cc:4462
#15 0x000055b04848b80b in mysql_create_table (thd=0x7f6950000db8, create_table=0x7f69500156b8, create_info=0x7f69802af7b0, alter_info=0x7f69802af6c0) at /data/src/10.11/sql/sql_table.cc:5255
#16 0x000055b0484a2e87 in Sql_cmd_create_table_like::execute (this=0x7f6950015658, thd=0x7f6950000db8) at /data/src/10.11/sql/sql_table.cc:12816
#17 0x000055b04837c88e in mysql_execute_command (thd=0x7f6950000db8, is_called_from_prepared_stmt=false) at /data/src/10.11/sql/sql_parse.cc:5997
#18 0x000055b048382a00 in mysql_parse (thd=0x7f6950000db8, rawbuf=0x7f6950015580 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", length=61, parser_state=0x7f69802b03c0) at /data/src/10.11/sql/sql_parse.cc:8037
#19 0x000055b04836eebb in dispatch_command (command=COM_QUERY, thd=0x7f6950000db8, packet=0x7f695000bb09 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", packet_length=61, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1894
#20 0x000055b04836d894 in do_command (thd=0x7f6950000db8, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1407
#21 0x000055b048548694 in do_handle_one_connection (connect=0x55b04c628fa8, put_in_cache=true) at /data/src/10.11/sql/sql_connect.cc:1416
#22 0x000055b0485483ff in handle_one_connection (arg=0x55b04c63e798) at /data/src/10.11/sql/sql_connect.cc:1318
#23 0x000055b048a48102 in pfs_spawn_thread (arg=0x55b04c628b18) at /data/src/10.11/storage/perfschema/pfs.cc:2201
#24 0x00007f6987a08ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
#25 0x00007f69875f7aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
{noformat}

Since the test case involves turning off foreign key checks, it is not always obvious what the expected result should be for each statement. There are several checkpoints here where things may have gone wrong.
First, I will put here the test case as a whole, and then intermediate results which I suspect to be incorrect. The final assertion failure is obviously a remote aftermath and as such is the least of our problems, but I'll paste it too for completeness.

{code:sql}
--source include/have_innodb.inc

CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = OFF;

CREATE TABLE t2 (
  pk INT PRIMARY KEY,
  a INT,
  FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
  FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;

SET SESSION FOREIGN_KEY_CHECKS = ON;

--error ER_ERROR_ON_RENAME
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

# Cleanup
DROP TABLE IF EXISTS t3, t2, t1;
{code}

So, creation of {{t2}} requires {{FOREIGN_KEY_CHECKS=OFF}} because it references non-existing {{t3}}.

Then {{t3}} is created, but it doesn't contain a key on {{x}} column, so it's not valid from the t2 FK perspective. It is still happening under {{FOREIGN_KEY_CHECKS=OFF}}, so it's unclear whether it should succeed or not. The problem is, {{CREATE OR REPLACE}} succeeds, while the same {{CREATE}} fails. Naturally it shouldn't be so.

{code:sql|title=10.11 ba875e93}
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE OR REPLACE TABLE t3 (x INT) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = ON;
{code}
{code:sql|title=same test case with CREATE instead of CREATE OR REPLACE}
CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS = OFF;
CREATE TABLE t2 (
pk INT PRIMARY KEY,
a INT,
FOREIGN KEY fk1 (a) REFERENCES t1 (f1),
FOREIGN KEY fk2 (a) REFERENCES t3 (x)
) ENGINE=InnoDB;
CREATE TABLE t3 (x INT) ENGINE=InnoDB;
bug.dup2 'innodb' [ fail ]
        Test ended at 2022-10-04 19:49:22

CURRENT_TEST: bug.dup2
mysqltest: At line 14: query 'CREATE TABLE t3 (x INT) ENGINE=InnoDB' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed")
{code}

Before MDEV-25292 both {{CREATE OR REPLACE}} and {{CREATE}} fail.

Back to the original test case with {{CREATE OR REPLACE t3}}; after it succeeded and we set {{FOREIGN_KEY_CHECKS}} back to {{ON}}, we CREATE OR REPLACE TABLE t2. The existing one still has a bad foreign key, but we create a new one without foreign keys, so one would expect it should succeed. However, it fails.

{code:sql}
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;
ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36f9c3-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")
{code}

If we instead do {{DROP TABLE}} and {{CREATE TABLE}}, it naturally succeeds.

Further, if we accept that {{CREATE OR REPLACE}} fails, it should not change the structure of {{t2}} (atomic, right?). But it does. If we add {{SHOW CREATE}} before and after the failed statement, we see:

{code:sql}
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `fk2` (`a`),
  CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`f1`),
  CONSTRAINT `fk2` FOREIGN KEY (`a`) REFERENCES `t3` (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
CREATE OR REPLACE TABLE t2 (a INT) ENGINE=InnoDB;
ERROR HY000: Error on rename of './test/t2' to './test/#sql-backup-36fae4-4-t2' (errno: 150 "Foreign key constraint is incorrectly formed")
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `pk` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `fk2` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
{code}

So, it keeps the old table but loses the foreign keys upon the failed CREATE OR REPLACE (both of them, even the good one).

And finally, after all this, the first CREATE OR REPLACE on t1 goes smoothly, but the second identical one fails the assertion:

{noformat:title=10.11 ba875e93}
2022-10-04 19:59:28 4 [Warning] InnoDB: In ALTER TABLE `test`.`t3` has or is referenced in foreign key constraints which are not compatible with the new table definition.
2022-10-04 19:59:28 4 [ERROR] InnoDB: In RENAME TABLE table `test`.`#sql-backup-36fc27-4-t2` is referenced in foreign key constraints which are not compatible with the new table definition.
mariadbd: /data/src/10.11/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.
221004 19:59:28 [ERROR] mysqld got signal 6 ;

#7 0x00007f698752c662 in __GI___assert_fail (assertion=0x55b049477e4d "err != DB_DUPLICATE_KEY", file=0x55b049476480 "/data/src/10.11/storage/innobase/row/row0mysql.cc", line=2676, function=0x55b049477d18 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, rename_fk)") at assert.c:101
#8 0x000055b048cebd5d in row_rename_table_for_mysql (old_name=0x7f69802ad980 "test/t1", new_name=0x7f69802ad780 "test/#sql-backup-36fc27-4-t1", trx=0x7f6981b6e680, fk=RENAME_FK) at /data/src/10.11/storage/innobase/row/row0mysql.cc:2676
#9 0x000055b048b34843 in innobase_rename_table (trx=0x7f6981b6e680, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1", fk=RENAME_FK) at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:13792
#10 0x000055b048b36249 in ha_innobase::rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/storage/innobase/handler/ha_innodb.cc:14192
#11 0x000055b0487478b1 in handler::ha_rename_table (this=0x7f69500174a0, from=0x7f69802ae510 "./test/t1", to=0x7f69802ae710 "./test/#sql-backup-36fc27-4-t1") at /data/src/10.11/sql/handler.cc:5347
#12 0x000055b04848c707 in mysql_rename_table (base=0x55b04bf92168, old_db=0x7f69500156b8, old_name=0x7f69802af220, new_db=0x7f69802afa18, new_name=0x7f69802af230, id=0x7f69802af240, flags=2) at /data/src/10.11/sql/sql_table.cc:5536
#13 0x000055b0483c12de in rename_table_and_triggers (thd=0x7f6950000db8, param=0x7f69802af220, ddl_log_state=0x0, ren_table=0x7f69500156b8, new_db=0x7f69802afa18, skip_error=false, force_if_exists=0x7f69802af1ee) at /data/src/10.11/sql/sql_rename.cc:384
#14 0x000055b04848921d in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f69802af7b0, thd=0x7f6950000db8, orig_table=0x7f69500156b8) at /data/src/10.11/sql/sql_table.cc:4462
#15 0x000055b04848b80b in mysql_create_table (thd=0x7f6950000db8, create_table=0x7f69500156b8, create_info=0x7f69802af7b0, alter_info=0x7f69802af6c0) at /data/src/10.11/sql/sql_table.cc:5255
#16 0x000055b0484a2e87 in Sql_cmd_create_table_like::execute (this=0x7f6950015658, thd=0x7f6950000db8) at /data/src/10.11/sql/sql_table.cc:12816
#17 0x000055b04837c88e in mysql_execute_command (thd=0x7f6950000db8, is_called_from_prepared_stmt=false) at /data/src/10.11/sql/sql_parse.cc:5997
#18 0x000055b048382a00 in mysql_parse (thd=0x7f6950000db8, rawbuf=0x7f6950015580 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", length=61, parser_state=0x7f69802b03c0) at /data/src/10.11/sql/sql_parse.cc:8037
#19 0x000055b04836eebb in dispatch_command (command=COM_QUERY, thd=0x7f6950000db8, packet=0x7f695000bb09 "CREATE OR REPLACE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB", packet_length=61, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1894
#20 0x000055b04836d894 in do_command (thd=0x7f6950000db8, blocking=true) at /data/src/10.11/sql/sql_parse.cc:1407
#21 0x000055b048548694 in do_handle_one_connection (connect=0x55b04c628fa8, put_in_cache=true) at /data/src/10.11/sql/sql_connect.cc:1416
#22 0x000055b0485483ff in handle_one_connection (arg=0x55b04c63e798) at /data/src/10.11/sql/sql_connect.cc:1318
#23 0x000055b048a48102 in pfs_spawn_thread (arg=0x55b04c628b18) at /data/src/10.11/storage/perfschema/pfs.cc:2201
#24 0x00007f6987a08ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
#25 0x00007f69875f7aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
{noformat}

Aleksey Midenkov made changes - 2022-10-04 18:52

Status

Open [ 1 ]

In Progress [ 3 ]

Marko Mäkelä added a comment - 2022-10-05 05:50

midenok, for debugging this, I would suggest to add queries like the following before and after each CREATE TABLE or CREATE OR REPLACE TABLE statement:

SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;

Foreign key constraint names within a schema are unique. I suppose that this requirement comes from some standard. In InnoDB, this is enforced by the primary key on the column ID in the system table SYS_FOREIGN. The ID comprises the schema ("database") name, a forward slash '/', and the constraint name (in this case, 'test/fk1' or 'test/fk2'). If no constraint name has been specified, then it will be derived from the name of the table, like 'test/t2_ibfk_1'.

Which table is the duplicate key error being issued for? I would expect that it is for the constraint name in SYS_FOREIGN or the table name in SYS_FOREIGN_COLS.

I would expect that CREATE OR REPLACE TABLE behaves in an equivalent way to DROP TABLE IF EXISTS followed by CREATE TABLE.

Marko Mäkelä added a comment - 2022-10-05 05:50 midenok , for debugging this, I would suggest to add queries like the following before and after each CREATE TABLE or CREATE OR REPLACE TABLE statement: SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; Foreign key constraint names within a schema are unique. I suppose that this requirement comes from some standard. In InnoDB, this is enforced by the primary key on the column ID in the system table SYS_FOREIGN . The ID comprises the schema ("database") name, a forward slash '/' , and the constraint name (in this case, 'test/fk1' or 'test/fk2' ). If no constraint name has been specified, then it will be derived from the name of the table, like 'test/t2_ibfk_1' . Which table is the duplicate key error being issued for? I would expect that it is for the constraint name in SYS_FOREIGN or the table name in SYS_FOREIGN_COLS . I would expect that CREATE OR REPLACE TABLE behaves in an equivalent way to DROP TABLE IF EXISTS followed by CREATE TABLE .

Aleksey Midenkov added a comment - 2022-10-05 14:08

Please review bb-10.11-midenok

Aleksey Midenkov added a comment - 2022-10-05 14:08 Please review bb-10.11-midenok

Aleksey Midenkov made changes - 2022-10-05 14:08

Assignee	Aleksey Midenkov [ midenok ]	Marko Mäkelä [ marko ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Elena Stepanova made changes - 2022-10-05 22:51

Link

This issue relates to TODO-3623 [ TODO-3623 ]

Elena Stepanova added a comment - 2022-10-07 11:21

The tentative bugfix also fixes failures on these simpler test cases (also introduced by atomic CoR, even though they don't execute CREATE OR REPLACE as such):

--source include/have_innodb.inc

CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t2 (id INT PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1 (id)) ENGINE=InnoDB;

CREATE INDEX idx ON t1 (id) ALGORITHM=COPY;

ALTER TABLE t2 FORCE, ALGORITHM=COPY;

# Cleanup

DROP TABLE t2, t1;

10.11 9206c1ea

mariadbd: /data/src/10.11/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.

221007 14:18:36 [ERROR] mysqld got signal 6 ;

--source include/have_innodb.inc

CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t2 (id INT PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1 (id)) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = OFF;

CREATE INDEX idx ON t1 (id) ALGORITHM=COPY;

ALTER TABLE t2 FORCE, ALGORITHM=COPY;

# Cleanup

DROP TABLE t2, t1;

10.11 9206c1ea
2022-10-07 14:19:30 0x7f6174d37700 InnoDB: Assertion failure in file /data/src/10.11/storage/innobase/dict/dict0dict.cc line 1214
InnoDB: Failing assertion: table2 == NULL

Elena Stepanova added a comment - 2022-10-07 11:21 The tentative bugfix also fixes failures on these simpler test cases (also introduced by atomic CoR, even though they don't execute CREATE OR REPLACE as such): --source include/have_innodb.inc CREATE TABLE t1 (id INT PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE t2 (id INT PRIMARY KEY , FOREIGN KEY (id) REFERENCES t1 (id)) ENGINE=InnoDB; CREATE INDEX idx ON t1 (id) ALGORITHM=COPY; ALTER TABLE t2 FORCE , ALGORITHM=COPY; # Cleanup DROP TABLE t2, t1; 10.11 9206c1ea mariadbd: /data/src/10.11/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. 221007 14:18:36 [ERROR] mysqld got signal 6 ; --source include/have_innodb.inc CREATE TABLE t1 (id INT PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE t2 (id INT PRIMARY KEY , FOREIGN KEY (id) REFERENCES t1 (id)) ENGINE=InnoDB; SET FOREIGN_KEY_CHECKS = OFF ; CREATE INDEX idx ON t1 (id) ALGORITHM=COPY; ALTER TABLE t2 FORCE , ALGORITHM=COPY; # Cleanup DROP TABLE t2, t1; 10.11 9206c1ea 2022-10-07 14:19:30 0x7f6174d37700 InnoDB: Assertion failure in file /data/src/10.11/storage/innobase/dict/dict0dict.cc line 1214 InnoDB: Failing assertion: table2 == NULL

Marko Mäkelä added a comment - 2022-10-10 07:13

The change to the InnoDB SQL code looks OK to me. Some code outside InnoDB could be cleaned up; I sent some suggestions.

Marko Mäkelä added a comment - 2022-10-10 07:13 The change to the InnoDB SQL code looks OK to me. Some code outside InnoDB could be cleaned up; I sent some suggestions .

Marko Mäkelä made changes - 2022-10-10 07:13

Assignee	Marko Mäkelä [ marko ]	Aleksey Midenkov [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2022-10-10 09:35

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Elena Stepanova made changes - 2022-10-12 19:37

Link

This issue relates to ~~MDEV-29779~~ [ ~~MDEV-29779~~ ]

Elena Stepanova made changes - 2022-10-12 23:01

Link

This issue relates to ~~MDEV-29783~~ [ ~~MDEV-29783~~ ]

Sergei Golubchik made changes - 2022-11-01 12:52

Fix Version/s		10.12 [ 28320 ]
Fix Version/s	10.11 [ 27614 ]

Sergei Golubchik made changes - 2022-12-07 15:34

Priority

Blocker [ 1 ]

Critical [ 2 ]

Aleksey Midenkov made changes - 2023-01-25 09:59

Fix Version/s		N/A [ 14700 ]
Fix Version/s	11.0 [ 28320 ]
Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

People

Assignee:: Aleksey Midenkov

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2022-10-04 17:02

Updated:: 2025-01-06 22:25

Resolved:: 2023-01-25 09:59

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration