Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.21, 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
-
None
Description
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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
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.
{code} 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 {code} If I use varchar(1000) for the address then it doesn't rebuild. {code} [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:05 nil_test.ibd {code} I can see only .frm file's time and size is updated but not .ibd. |
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.
{code} 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 {code} If I use varchar(1000) for the address then it doesn't rebuild. {code} [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 {code} I can see only .frm file's time and size is updated but not .ibd. |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.0 [ 28320 ] | |
| Fix Version/s | 11.1 [ 28549 ] | |
| Fix Version/s | 11.2 [ 28603 ] | |
| Fix Version/s | 11.3 [ 28565 ] | |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.6 [ 24028 ] | |
| Affects Version/s | 10.11 [ 27614 ] | |
| Affects Version/s | 11.0 [ 28320 ] | |
| Affects Version/s | 11.1 [ 28549 ] | |
| Affects Version/s | 11.2 [ 28603 ] | |
| Affects Version/s | 11.3 [ 28565 ] | |
| Affects Version/s | 11.4 [ 29301 ] | |
| Assignee | Thirunarayanan Balathandayuthapani [ thiru ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Thirunarayanan Balathandayuthapani [ thiru ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Marko Mäkelä [ marko ] | Thirunarayanan Balathandayuthapani [ thiru ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.4.34 [ 29625 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.0 [ 28320 ] | |
| Fix Version/s | 11.1 [ 28549 ] | |
| Fix Version/s | 11.3 [ 28565 ] | |
| Fix Version/s | 11.2 [ 28603 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 10.5.25 [ 29626 ] | |
| Fix Version/s | 10.6.18 [ 29627 ] | |
| Fix Version/s | 10.11.8 [ 29630 ] | |
| Fix Version/s | 11.0.6 [ 29628 ] | |
| Fix Version/s | 11.1.5 [ 29629 ] | |
| Fix Version/s | 11.2.4 [ 29631 ] |
| Zendesk Related Tickets | 111863 |