By default, ALTER TABLE ADD COLUMN should be instant and it should not rebuild the table but when we add column with VARCHAR(5000) and charset is UTF8 then it is rebuilding the table.
MariaDB [test]> CREATE TABLE nil_test(id int, name varchar(10)) DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci;
|
Query OK, 0 rows affected (0.109 sec)
|
|
MariaDB [test]> INSERT INTO nil_test VALUES (1, 'abc'), (2, 'def');
|
Query OK, 2 rows affected (0.003 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> quit
|
Bye
|
[root@centos8 test]# ls -al nil*
|
-rw-rw---- 1 mysql mysql 486 Dec 20 10:03 nil_test.frm
|
-rw-rw---- 1 mysql mysql 98304 Dec 20 10:03 nil_test.ibd
|
..
|
[root@centos8 test]# date
|
Wed Dec 20 10:04:06 IST 2023
|
[root@centos8 test]# mysql
|
MariaDB [(none)]> use test
|
Database changed
|
MariaDB [test]> ALTER TABLE nil_test ADD COLUMN address varchar(5000), ADD COLUMN city varchar(20);
|
Query OK, 0 rows affected (0.026 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> quit
|
Bye
|
[root@centos8 test]# ls -al nil*
|
-rw-rw---- 1 mysql mysql 15596 Dec 20 10:04 nil_test.frm
|
-rw-rw---- 1 mysql mysql 98304 Dec 20 10:04 nil_test.ibd
|
If I use varchar(1000) for the address then it doesn't rebuild.
[root@centos8 test]# ls -al nil*
|
-rw-rw---- 1 mysql mysql 569 Dec 20 10:06 nil_test.frm
|
-rw-rw---- 1 mysql mysql 98304 Dec 20 10:04 nil_test.ibd
|
I can see only .frm file's time and size is updated but not .ibd.
Bug
Major
