Marko Mäkelä added a comment - 2023-05-30 07:50 As far as I understand, the first CREATE TABLE example in the Description would fail even if foreign_key_checks=OFF . To be consistent with that, a corresponding ALTER TABLE must fail too, regardless of whether ALGORITHM=COPY is being used.

Marko Mäkelä added a comment - 2023-05-30 12:02 It turns out in the SQL layer, the function fk_check_column_changes() would intentionally cause ALTER TABLE…ALGORITHM=COPY to ignore invalid foreign key constraints if foreign_key_checks=OFF . This would seem to be misguided, because a subsequent TRUNCATE TABLE or OPTIMIZE TABLE (if alter_algorithm=copy ) and possibly ALTER TABLE…FORCE, ALGORITHM=COPY would be refused on the table. thiru , please fill in the details. When foreign_key_checks=OFF , I think that it is reasonable to allow CREATE TABLE or ALTER TABLE to create FOREIGN KEY constraints that refer to non-existing tables. But I do not see the usefulness of allowing the creation of totally invalid foreign key constraints, that is, when columns are of incompatible type or use incompatible character encodings or collations. I think a good guideline is that the output of SHOW CREATE TABLE after any number of DDL operations will be a valid CREATE TABLE statement if foreign_key_checks=OFF . If someone wants to change the collation or encoding of a column that is connected to a FOREIGN KEY constraint, that can be done with 3 ALTER TABLE statements, something like this (untested SQL): SET foreign_key_checks= OFF ; ALTER TABLE child DROP CONSTRAINT fk, MODIFY m VARCHAR (200) CHARSET utf8mb4; ALTER TABLE parent MODIFY m VARCHAR (200) CHARSET utf8mb4; ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (m) REFERENCES PARENT(m); SET foreign_key_checks= ON ; Disabling the checks will allow the ADD CONSTRAINT to be instantaneous; see also MDEV-16356 .

Thirunarayanan Balathandayuthapani added a comment - 2023-05-30 12:49   CREATE TABLE t1( id SERIAL, msg VARCHAR(100) CHARACTER SET utf8mb3, KEY(msg))ENGINE=InnoDB;   CREATE TABLE t2( id SERIAL, msg varchar(100) CHARACTER SET utf8mb3, INDEX (msg), CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg) ON DELETE CASCADE)ENGINE=InnoDB;   SET FOREIGN_KEY_CHECKS=0; ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, algorithm=COPY;   SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `msg` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, UNIQUE KEY `id` (`id`), KEY `msg` (`msg`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci   SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `msg` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, UNIQUE KEY `id` (`id`), KEY `msg` (`msg`), CONSTRAINT `fk_t1` FOREIGN KEY (`msg`) REFERENCES `t1` (`msg`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci   SET FOREIGN_KEY_CHECKS=1;   TRUNCATE TABLE t2; failed: ER_CANNOT_ADD_FOREIGN (1215): Cannot add foreign key constraint for `t2`   ALTER TABLE t2 FORCE, ALGORITHM=COPY; failed: ER_ERROR_ON_RENAME (1025): Error on rename of './test/#sql-alter-1e9ed-4' to './test/t2' (errno: 150 "Foreign key constraint is incorrectly formed")

Marko Mäkelä added a comment - 2023-06-02 10:01 Thank you, it looks good. Once you have addressed my minor comments and this has passed testing, this will require another review of the change to fk_check_column_changes() (to make ALTER TABLE…ALGORITHM=COPY stricter).

Sergei Golubchik added a comment - 2023-06-20 19:39 - edited thiru , server changes in f1746ad6dd are ok