[MDEV-34892] ALTER TABLE ADD FOREIGN KEY no longer validates integrity since 11.4.3 - Jira

XML

Word

Printable

Something is wrong with the 11.4.3 and 11.5.2 (2024-08) releases, they no longer validate the integrity of the data when a new constraint is added.

Was working until 11.4.2 and 11.5.1 and it works also in all MySQL versions I tested.

Code to reproduce:

CREATE TABLE `client` (

  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,

  `name` VARCHAR(255) DEFAULT NULL,

  `country_id` BIGINT UNSIGNED DEFAULT NULL,

  PRIMARY KEY(`id`)

) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE `country` (

  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,

  `name` VARCHAR(255) DEFAULT NULL,

  PRIMARY KEY(`id`)

) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

START TRANSACTION;

insert into `client` (`name`, `country_id`)

values

  ('Leos', 10);

COMMIT;

ALTER TABLE

  `client`

ADD

  CONSTRAINT FK_C7440455F92F3E705373C966BF3 FOREIGN KEY (`country_id`) REFERENCES `country` (`id`);

The last query must throw an integrity error as country with ID=10 does not exist.

is duplicated by

MDEV-34756 Validation of new foreign key skipped if innodb_alter_copy_bulk=ON

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.