MySQL add CONSTRAINT without lock

Want to avoid ADD CONSTRAINT that take ages and delay users’ transactions ? Your database gets into metadata lock and “copying to tmp table” ? Other update are stuck in “Waiting for table metadata lock”

Here is how to fix that :

To add a column youn eed to specify inplace algorythm :

ALTER TABLE table1 ADD column30 DATETIME DEFAULT NULL, algorithm=inplace, lock=none;

For constraint you only need to deactivate foreing key check and MySQL will use the inplace algorithme. The folowwing works on a MySQL 5.6 running on an AWS Aurora :

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE table_a ADD CONSTRAINT FK_1234567890ABCDEF FOREIGN KEY (column_id) REFERENCES table_b(id) ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS = 1;

Source :
The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.

InnoDB and Online DDL :

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html#online-ddl-foreign-key-operations

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
jerem le

System and software engineer, former CTO. Custom software development