[MDEV-31987] Cannot disable FOREIGN_KEY_CHECKS anymore for converting character set Created: 2023-08-23  Updated: 2023-11-08  Resolved: 2023-08-23

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Soner Sayakci Assignee: Marko Mäkelä
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-32003 MODIFY COLUMN no longer possible with... Closed
Problem/Incident
is caused by MDEV-31086 MODIFY COLUMN can break FK constraint... Closed
Relates
relates to MDEV-32171 When updating cascaded foreign keys, ... Closed
relates to MDEV-32729 look at SET FOREIGN_KEY_CHECK=0 Closed

 Description   

We had in our migration some tables with a wrong character set created (we didn't set explicit which character set). So we added a while ago a migration to fix this.

For this we disable first the foreign key checks and then run the ALTER TABLE. It seems now that since the last patch versions this does not work anymore.

Simplified test case:

CREATE TABLE `t1` (
 
  `hash` varchar(32) NOT NULL COLLATE 'utf8_unicode_ci',
 
  `title` varchar(100) DEFAULT NULL COLLATE 'utf8_unicode_ci',
 
  PRIMARY KEY (`hash`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	
 
 
CREATE TABLE `t2` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `t1_hash` varchar(32) NOT NULL  COLLATE 'utf8_unicode_ci',
 
  `name` varchar(100) DEFAULT NULL  COLLATE 'utf8_unicode_ci',
 
  PRIMARY KEY (`id`),
 
  KEY `FK_t1_hash` (`t1_hash`),
 
  CONSTRAINT `FK_t1_hash` FOREIGN KEY (`t1_hash`) REFERENCES `t1` (`hash`) ON DELETE CASCADE ON UPDATE NO ACTION
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 
 
 
SET FOREIGN_KEY_CHECKS = 0;
 
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
SET FOREIGN_KEY_CHECKS = 1;

error is:

SET FOREIGN_KEY_CHECKS = 0
 
Query executed OK, 0 rows affected. (0.000 s) Edit
 
 
 
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 
Error in query (1833): Cannot change column 'hash': used in a foreign key constraint 'FK_t1_hash' of table 'test.t2'
 
 
 
ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 
Error in query (1832): Cannot change column 't1_hash': used in a foreign key constraint 'FK_t1_hash'
 
 
 
SET FOREIGN_KEY_CHECKS = 1
 
Query executed OK, 0 rows affected. (0.000 s) Edit, Warnings



 Comments   
Comment by Marko Mäkelä [ 2023-08-23 ]

This change is intentional due to MDEV-31086. As I wrote there, you would need 3 ALTER TABLE statements, like this:

ALTER TABLE t2 DROP CONSTRAINT FK_t1_hash, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
ALTER TABLE t2 ADD CONSTRAINT `FK_t1_hash` FOREIGN KEY (`t1_hash`) REFERENCES `t1` (`hash`) ON DELETE CASCADE ON UPDATE NO ACTION;

In this way, there is no need to disable foreign_key_checks at any point either.

It would be ideal if multiple DDL statements could be combined into a single atomic transaction, but that would be quite hard to change.

Comment by Rajitha Kumara [ 2023-09-04 ]

Seems this is reproducible with 10.9.8 also

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