Description:
According to the document
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-colum...
Extending VARCHAR column size less than 255 bytes should not cause the table rebuild.
In my test, the table will rebuild at the first time VARCHAR column size change, the following changes will not rebuild the table. The table rebuild happens with each varchar column size change at the first time.
My testing MySQL version is 5.7.37
How to repeat:
--Step 1, create table
mysql> create table test1
-> ( id int(10) unsigned NOT NULL AUTO_INCREMENT,
-> s1 varchar(10) NOT NULL DEFAULT '',
-> s2 varchar(10) NOT NULL DEFAULT '',
-> PRIMARY KEY(id)) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.02 sec)
mysql>
-- step 2, create sp to load data
mysql> delimiter //
mysql>
mysql> create procedure insert_test1(in row_num int )
-> begin
-> declare i int default 0;
-> while i < row_num do
-> insert into test1(s1,s2) values('hello','hello2');
-> set i = i+1;
-> END while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
-- step 3, load sample data
mysql> begin; call insert_test1(10000000);
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (4 min 29.42 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--step 4, extending the varchar length testing.
mysql> ALTER TABLE test1 CHANGE COLUMN s1 s1 VARCHAR(15), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (20.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE test1 CHANGE COLUMN s1 s1 VARCHAR(20), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE test1 CHANGE COLUMN s2 s2 VARCHAR(15), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (20.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE test1 CHANGE COLUMN s2 s2 VARCHAR(20), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> ALTER TABLE test1 CHANGE COLUMN s1 s1 VARCHAR(25), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
From above, the first time column size change took longer time.
During the column size change, the ibd file size changed and also the #sql-ib temporary files was generated.
[root@ip-172-31-43-64 testdb]# ls -lt
total 389140
-rw-r----- 1 mysql mysql 398458880 Apr 8 05:57 test1.ibd
-rw-r----- 1 mysql mysql 8608 Apr 8 05:53 test1.frm
-rw-r----- 1 mysql mysql 65 Apr 8 05:41 db.opt
[root@ip-172-31-43-64 testdb]# ls -lt
total 557092
-rw-r----- 1 mysql mysql 171966464 Apr 8 06:10 #sql-ib88-3227774033.ibd
-rw-r----- 1 mysql mysql 8608 Apr 8 06:10 #sql-153a_6.frm
-rw-r----- 1 mysql mysql 398458880 Apr 8 05:57 test1.ibd
-rw-r----- 1 mysql mysql 8608 Apr 8 05:53 test1.frm
-rw-r----- 1 mysql mysql 65 Apr 8 05:41 db.opt
[root@ip-172-31-43-64 testdb]#
[root@ip-172-31-43-64 testdb]# ls -lt
total 450580
-rw-r----- 1 mysql mysql 8608 Apr 8 06:10 test1.frm
-rw-r----- 1 mysql mysql 461373440 Apr 8 06:10 test1.ibd
-rw-r----- 1 mysql mysql 65 Apr 8 05:41 db.opt
[root@ip-172-31-43-64 testdb]# ls -lt
total 753700
-rw-r----- 1 mysql mysql 310378496 Apr 8 06:11 #sql-ib89-3227774035.ibd
-rw-r----- 1 mysql mysql 8608 Apr 8 06:11 #sql-153a_6.frm
-rw-r----- 1 mysql mysql 8608 Apr 8 06:10 test1.frm
-rw-r----- 1 mysql mysql 461373440 Apr 8 06:10 test1.ibd
-rw-r----- 1 mysql mysql 65 Apr 8 05:41 db.opt
[root@ip-172-31-43-64 testdb]#
[root@ip-172-31-43-64 testdb]# ls -lt
total 450580
-rw-r----- 1 mysql mysql 8608 Apr 8 06:11 test1.frm
-rw-r----- 1 mysql mysql 461373440 Apr 8 06:11 test1.ibd
-rw-r----- 1 mysql mysql 65 Apr 8 05:41 db.opt
[root@ip-172-31-43-64 testdb]#
