Description:
Hello,
About "Column Operations" part in chapter 17.12.1 Online DDL Operations of reference manual, "Permits Concurrent DML" attribute for operation ""Dropping the column default value is writen as "YES", actually it's not exact.
The following is described in the same page of reference manual:
"""
For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY).
"""
So, if the size of VARCHAR column is extended from less than 256 bytes to a size equal to or greater than 256 bytes, copy algorithm is used, and it will not be a online operation, which means COPY algorithm is used and EXCLUSIVE mdl lock is aquired. Here is the illustration:
[session 1]
create database ddl;
use ddl;
create table ddl.t1 (c1 varchar(40) charset utf8mb4);
mysql> insert into t1 values ('A'),('A'),('A'),('A'),('A'),('A'),('A'),('A');
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into t1 select a.c1 from t1 a join t1 b;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into t1 select a.c1 from t1 a join t1 b;
Query OK, 5184 rows affected (0.03 sec)
Records: 5184 Duplicates: 0 Warnings: 0
mysql> insert into t1 select a.c1 from t1 a join t1 b;
Query OK, 27625536 rows affected (1 min 56.49 sec)
Records: 27625536 Duplicates: 0 Warnings: 0
mysql> alter table t1 change column c1 c1 varchar(400);
While the alter table is in execution, operate session 2:
[session 2]
mysql> set session lock_wait_timeout = 5;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into ddl.t1 values ('CC');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> show processlist;
+----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 1174797 | Waiting on empty queue | NULL |
| 61 | root | localhost | ddl | Sleep | 1983 | | NULL |
| 75 | root | localhost | ddl | Query | 17 | copy to tmp table | alter table t1 change column c1 c1 varchar(400) |
| 79 | root | localhost | ddl | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> insert into ddl.t1 values ('CC');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
we can see that concurrent DML is not permitted when the DDL is in execution, attemption of DML operation result in Lock wait timeout error. Alse, State of DDL is "copy to tmp table" show that it's rebulding a new table but "Rebuilds Table" attribute of "Extending VARCHAR column size" is writen as "No".
So I believe operation of "Extending VARCHAR column size" from less than 256 bytes to a size equal to or greater than 256 bytes uses copy algrithm, needs to Rebuild table, and dose not permit concurrent DML, and of course not modify metadata only.
How to repeat:
[session 1]
create database ddl;
use ddl;
create table ddl.t1 (c1 varchar(40) charset utf8mb4);
mysql> insert into t1 values ('A'),('A'),('A'),('A'),('A'),('A'),('A'),('A');
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into t1 select a.c1 from t1 a join t1 b;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into t1 select a.c1 from t1 a join t1 b;
Query OK, 5184 rows affected (0.03 sec)
Records: 5184 Duplicates: 0 Warnings: 0
mysql> insert into t1 select a.c1 from t1 a join t1 b;
Query OK, 27625536 rows affected (1 min 56.49 sec)
Records: 27625536 Duplicates: 0 Warnings: 0
mysql> alter table t1 change column c1 c1 varchar(400);
While the alter table is in execution, operate session 2:
[session 2]
mysql> set session lock_wait_timeout = 5;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into ddl.t1 values ('CC');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> show processlist;
+----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 1174797 | Waiting on empty queue | NULL |
| 61 | root | localhost | ddl | Sleep | 1983 | | NULL |
| 75 | root | localhost | ddl | Query | 17 | copy to tmp table | alter table t1 change column c1 c1 varchar(400) |
| 79 | root | localhost | ddl | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> insert into ddl.t1 values ('CC');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Description: Hello, About "Column Operations" part in chapter 17.12.1 Online DDL Operations of reference manual, "Permits Concurrent DML" attribute for operation ""Dropping the column default value is writen as "YES", actually it's not exact. The following is described in the same page of reference manual: """ For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). """ So, if the size of VARCHAR column is extended from less than 256 bytes to a size equal to or greater than 256 bytes, copy algorithm is used, and it will not be a online operation, which means COPY algorithm is used and EXCLUSIVE mdl lock is aquired. Here is the illustration: [session 1] create database ddl; use ddl; create table ddl.t1 (c1 varchar(40) charset utf8mb4); mysql> insert into t1 values ('A'),('A'),('A'),('A'),('A'),('A'),('A'),('A'); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into t1 select a.c1 from t1 a join t1 b; Query OK, 64 rows affected (0.00 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> insert into t1 select a.c1 from t1 a join t1 b; Query OK, 5184 rows affected (0.03 sec) Records: 5184 Duplicates: 0 Warnings: 0 mysql> insert into t1 select a.c1 from t1 a join t1 b; Query OK, 27625536 rows affected (1 min 56.49 sec) Records: 27625536 Duplicates: 0 Warnings: 0 mysql> alter table t1 change column c1 c1 varchar(400); While the alter table is in execution, operate session 2: [session 2] mysql> set session lock_wait_timeout = 5; Query OK, 0 rows affected (0.00 sec) mysql> insert into ddl.t1 values ('CC'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> show processlist; +----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 1174797 | Waiting on empty queue | NULL | | 61 | root | localhost | ddl | Sleep | 1983 | | NULL | | 75 | root | localhost | ddl | Query | 17 | copy to tmp table | alter table t1 change column c1 c1 varchar(400) | | 79 | root | localhost | ddl | Query | 0 | init | show processlist | +----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) mysql> insert into ddl.t1 values ('CC'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction we can see that concurrent DML is not permitted when the DDL is in execution, attemption of DML operation result in Lock wait timeout error. Alse, State of DDL is "copy to tmp table" show that it's rebulding a new table but "Rebuilds Table" attribute of "Extending VARCHAR column size" is writen as "No". So I believe operation of "Extending VARCHAR column size" from less than 256 bytes to a size equal to or greater than 256 bytes uses copy algrithm, needs to Rebuild table, and dose not permit concurrent DML, and of course not modify metadata only. How to repeat: [session 1] create database ddl; use ddl; create table ddl.t1 (c1 varchar(40) charset utf8mb4); mysql> insert into t1 values ('A'),('A'),('A'),('A'),('A'),('A'),('A'),('A'); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into t1 select a.c1 from t1 a join t1 b; Query OK, 64 rows affected (0.00 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> insert into t1 select a.c1 from t1 a join t1 b; Query OK, 5184 rows affected (0.03 sec) Records: 5184 Duplicates: 0 Warnings: 0 mysql> insert into t1 select a.c1 from t1 a join t1 b; Query OK, 27625536 rows affected (1 min 56.49 sec) Records: 27625536 Duplicates: 0 Warnings: 0 mysql> alter table t1 change column c1 c1 varchar(400); While the alter table is in execution, operate session 2: [session 2] mysql> set session lock_wait_timeout = 5; Query OK, 0 rows affected (0.00 sec) mysql> insert into ddl.t1 values ('CC'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> show processlist; +----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 1174797 | Waiting on empty queue | NULL | | 61 | root | localhost | ddl | Sleep | 1983 | | NULL | | 75 | root | localhost | ddl | Query | 17 | copy to tmp table | alter table t1 change column c1 c1 varchar(400) | | 79 | root | localhost | ddl | Query | 0 | init | show processlist | +----+-----------------+-----------+------+---------+---------+------------------------+-------------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) mysql> insert into ddl.t1 values ('CC'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction