When altering a table to add a new foreign key constraint, the referential integrity validation of existing rows is now skipped if innodb_alter_copy_bulk=ON, which is the default value for this new variable. This is a surprising behavior change, especially for a quarterly point release, so I assume it is unintentional.

Setting innodb_alter_copy_bulk=OFF restores the old behavior (existing data is verified if session foreign_key_checks=ON).

MariaDB [testing]> CREATE TABLE companies (
-> id int unsigned not null auto_increment primary key,
-> name varchar(30));
Query OK, 0 rows affected (0.019 sec)

MariaDB [testing]> CREATE TABLE websites (
-> id int unsigned not null auto_increment primary key,
-> url varchar(100) not null,
-> company_id int unsigned not null);
Query OK, 0 rows affected (0.012 sec)

MariaDB [testing]> INSERT INTO websites (url, company_id) VALUES ('https://www.skeema.io', 123);
Query OK, 1 row affected (0.011 sec)

MariaDB [testing]> SELECT version(), @@global.innodb_alter_copy_bulk, @@session.foreign_key_checks;
------------------------------------------------------------------------------------

version()

@@global.innodb_alter_copy_bulk

@@session.foreign_key_checks

------------------------------------------------------------------------------------

10.11.9-MariaDB-ubu2204

1

1

------------------------------------------------------------------------------------
1 row in set (0.003 sec)

MariaDB [testing]> ALTER TABLE websites ADD CONSTRAINT foo FOREIGN KEY (company_id) REFERENCES companies (id);
Query OK, 1 row affected (0.013 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [testing]> SET GLOBAL innodb_alter_copy_bulk = 0;
Query OK, 0 rows affected (0.001 sec)

MariaDB [testing]> ALTER TABLE websites ADD CONSTRAINT bar FOREIGN KEY (company_id) REFERENCES companies (id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testing`.`#sql-alter-1-3`, CONSTRAINT `bar` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`))
MariaDB [testing]>