Description:
Hi,
Adding a column along a check constraint does not use the INSTANT algorithm:
How to repeat:
mysql> select @@version ;
+-----------+
| @@version |
+-----------+
| 8.4.3 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `t1` (
-> `c1` int(11) DEFAULT NULL,
-> `c2` int(11) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql> ALTER TABLE t1 add column c3 integer CHECK (c3 >= 0), ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.
mysql> ALTER TABLE t1 add column c3 integer CHECK (c3 >= 0), ALGORITHM=COPY;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL,
`c3` int DEFAULT NULL,
CONSTRAINT `t1_chk_1` CHECK ((`c3` >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
That said, when dropping the column it does use the INSTANT algorithm:
mysql> ALTER TABLE t1 drop column c3, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
And the constraint is removed:
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Would be possible adding a CHECK constraint using the INSTANT algorithm?
Also, I didn't find any related limitation reference in the MySQL documentation:
https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html
https://dev.mysql.com/doc/refman/8.4/en/create-table-check-constraints.html
Thanks!
Description: Hi, Adding a column along a check constraint does not use the INSTANT algorithm: How to repeat: mysql> select @@version ; +-----------+ | @@version | +-----------+ | 8.4.3 | +-----------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `t1` ( -> `c1` int(11) DEFAULT NULL, -> `c2` int(11) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> insert into t1 values (1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (1,1); Query OK, 1 row affected (0.00 sec) mysql> ALTER TABLE t1 add column c3 integer CHECK (c3 >= 0), ALGORITHM=INSTANT; ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY. mysql> ALTER TABLE t1 add column c3 integer CHECK (c3 >= 0), ALGORITHM=COPY; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int DEFAULT NULL, `c2` int DEFAULT NULL, `c3` int DEFAULT NULL, CONSTRAINT `t1_chk_1` CHECK ((`c3` >= 0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) That said, when dropping the column it does use the INSTANT algorithm: mysql> ALTER TABLE t1 drop column c3, ALGORITHM=INSTANT; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 And the constraint is removed: mysql> show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int DEFAULT NULL, `c2` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Would be possible adding a CHECK constraint using the INSTANT algorithm? Also, I didn't find any related limitation reference in the MySQL documentation: https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.4/en/create-table-check-constraints.html Thanks!