[MDEV-30416] Can't redefine constraint in a single ALTER TABLE Created: 2023-01-16  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Remy Fox Assignee: Vladislav Lesin
Resolution: Unresolved Votes: 0
Labels: foreign-keys

Issue Links:
Relates
relates to MDEV-28933 CREATE OR REPLACE fails to recreate s... Closed
relates to MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTR... Open
relates to MDEV-16417 Store Foreign Key metadata outside of... In Review

 Description   

It is possible to use one ALTER TABLE to drop and create a new column or index. This is not possible with a constraint; there will an error. See below

CREATE TABLE abc (id INT NOT NULL, PRIMARY KEY (id));
 
 
 
CREATE TABLE def (
 
	abc_id INT NOT NULL, 
	droppable_column INT NOT NULL,
 
	INDEX droppable_index(abc_id),
 
	CONSTRAINT constraint_name FOREIGN KEY (abc_id) REFERENCES abc (id)
 
);
 
 
 
# No problem
 
ALTER TABLE def
 
DROP COLUMN droppable_column ,
 
ADD COLUMN droppable_column INT NOT NULL;
 
 
 
# No problem
 
ALTER TABLE def
 
DROP INDEX droppable_index,
 
ADD INDEX droppable_index(abc_id);
 
 
 
# Problem
 
ALTER TABLE def
 
DROP FOREIGN KEY constraint_name,
 
ADD CONSTRAINT constraint_name FOREIGN KEY (abc_id) REFERENCES abc (id);



 Comments   
Comment by Alice Sherepa [ 2023-01-16 ]

Thanks for the report!
I repeated as described, there is a problem when a constraint has the same name:

MariaDB [test]> CREATE TABLE abc (id INT NOT NULL, PRIMARY KEY (id)) engine=innodb;
 
Query OK, 0 rows affected (0,038 sec)
 
 
 
MariaDB [test]> CREATE TABLE def (
 
    -> abc_id INT NOT NULL, 
    -> droppable_column INT NOT NULL,
 
    -> INDEX droppable_index(abc_id),
 
    -> CONSTRAINT constraint_name FOREIGN KEY (abc_id) REFERENCES abc (id)
 
    -> )engine=innodb;
 
Query OK, 0 rows affected (0,042 sec)
 
 
 
MariaDB [test]> ALTER TABLE def DROP FOREIGN KEY constraint_name, ADD CONSTRAINT constraint_name FOREIGN KEY (abc_id) REFERENCES abc (id);
 
ERROR 1005 (HY000): Can't create table `test`.`def` (errno: 121 "Duplicate key on write or update")
 
Warning (Code 121): Create or Alter table `test`.`#sql-alter-16ac6a-5` with foreign key constraint failed. Foreign key constraint `test`.`constraint_name` already exists on data dictionary. Foreign key constraint names need to be unique in database. Error in foreign key definition: CONSTRAINT `constraint_name` FOREIGN KEY (`abc_id`) REFERENCES `test`.`abc` (`id`).
 
Error (Code 1005): Can't create table `test`.`def` (errno: 121 "Duplicate key on write or update")
 
Warning (Code 1022): Can't write; duplicate key in table 'def'
 
 
 
 
 
MariaDB [test]> ALTER TABLE def DROP FOREIGN KEY constraint_name, ADD CONSTRAINT constraint_name1 FOREIGN KEY (abc_id) REFERENCES abc (id);
 
Query OK, 0 rows affected (0,099 sec)              
Records: 0  Duplicates: 0  Warnings: 0

Comment by Brent Van Sickle [ 2023-06-06 ]

This is affecting me too.

Comment by Marko Mäkelä [ 2023-06-07 ]

The problem is that in the InnoDB internal table SYS_FOREIGN there is a unique index on constraint name, which must be unique across all tables in the same database. When no constraint name is specified, it will be generated like databasename/tablename_ibfk_1, but it still has to be unique. Ultimately, this bug would be fixed by changing the metadata format (MDEV-16417).

In the test case that user2180613 posted, the duplicate key error is being reported at the time ALTER TABLE internally invokes ha_innobase::create() on a table with a temporary name, in my case it is #sql-alter-825f-4. Normally, the contents of the table def would be copied into that table, the original table def renamed to an intermediate name, the new table renamed to def, and the old table dropped. Because all this is internally done in several independent transactions (there is no API to start and commit DDL transactions), this bug cannot be fixed easily.

What could be fixed easily is a failure when SET foreign_key_checks=0; is being used and the foreign key constraint could be added by native ALTER TABLE:

10.6 f569e06e03a7efa6050258a8d167de0aaa4e124c

 
mysqltest: At line 23: query 'ALTER TABLE def
 
DROP FOREIGN KEY constraint_name,
 
ADD CONSTRAINT constraint_name FOREIGN KEY (abc_id) REFERENCES abc (id)' failed: ER_DUP_CONSTRAINT_NAME (1826): Duplicate FOREIGN KEY constraint name 'test/constraint_name'

Here is a start of a fix; the first hunk needs to ignore names of "duplicate" constraints that are being dropped at the same time.

diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
 
index 06101e6cca5..956aa0287f7 100644
 
--- a/storage/innobase/handler/handler0alter.cc
 
+++ b/storage/innobase/handler/handler0alter.cc
 
@@ -2884,7 +2884,7 @@ innobase_init_foreign(
 
 		/* Check if any existing foreign key has the same id,
 
 		this is needed only if user supplies the constraint name */
 
 
-		if (table->foreign_set.find(foreign)
 
+		if (false && table->foreign_set.find(foreign)
 
 		    != table->foreign_set.end()) {
 
 			return(false);
 
 		}
 
@@ -9804,6 +9804,16 @@ innobase_update_foreign_try(
 
 
 	foreign_id++;
 
 
+	for (i = 0; i < ctx->num_to_drop_fk; i++) {
 
+		dict_foreign_t* fk = ctx->drop_fk[i];
 
+
 
+		DBUG_ASSERT(fk->foreign_table == ctx->old_table);
 
+
 
+		if (innobase_drop_foreign_try(trx, table_name, fk->id)) {
 
+			DBUG_RETURN(true);
 
+		}
 
+	}
 
+
 
 	for (i = 0; i < ctx->num_to_add_fk; i++) {
 
 		dict_foreign_t*		fk = ctx->add_fk[i];
 
 
@@ -9852,16 +9862,6 @@ innobase_update_foreign_try(
 
 		}
 
 	}
 
 
-	for (i = 0; i < ctx->num_to_drop_fk; i++) {
 
-		dict_foreign_t* fk = ctx->drop_fk[i];
 
-
 
-		DBUG_ASSERT(fk->foreign_table == ctx->old_table);
 
-
 
-		if (innobase_drop_foreign_try(trx, table_name, fk->id)) {
 
-			DBUG_RETURN(true);
 
-		}
 
-	}
 
-
 
 	DBUG_RETURN(false);
 
 }

This partial fix would benefit everyone when MDEV-16356 is implemented.

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