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

XML

Word

Printable

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

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.

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

Stalled

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

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:: 2023-05-18 10:10

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.