Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33214

Table is getting rebuild with ALTER TABLE ADD COLUMN

Details

    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

          People

            thiru Thirunarayanan Balathandayuthapani
            niljoshi Nilnandan Joshi
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.