[MDEV-29779] Unexpected ER_ERROR_ON_RENAME upon CREATE OR REPLACE Created: 2022-10-12  Updated: 2023-05-18  Resolved: 2023-01-25

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table, Storage Engine - InnoDB
Affects Version/s: 10.11
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-25292 Atomic CREATE OR REPLACE TABLE Stalled
Relates
relates to MDEV-29701 Inconsistencies and eventual failure ... Closed
relates to MDEV-29783 ER_NO_SUCH_TABLE_IN_ENGINE after fail... Closed

 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.

 Comments   
Comment by Aleksey Midenkov [ 2022-10-12 ]

Please review bb-10.11-midenok

Comment by Marko Mäkelä [ 2022-10-14 ]

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.

Comment by Aleksey Midenkov [ 2022-10-18 ]

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

Comment by Marko Mäkelä [ 2022-10-18 ]

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

Comment by Aleksey Midenkov [ 2022-10-18 ]

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

Generated at Thu Feb 08 10:11:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.