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

altering a column comment does a full copy

Details

    • 10.0.25

    Description

      Altering column's comment does a full table copy. It should be executed by only changing the frm.

      initial issue description follow

      This is a new feature request and is similar to this request. http://bugs.mysql.com/bug.php?id=64439

      We have multiple mariadb instances managing ~2.5T of our data. We decided to document them using table/column comments. Unfortunately, there isnt an easier way of doing this.

      1) Filed a bug regarding altering table comment doing full copy. MDEV-9103
      2) Changing the column comment does the full copy (MDEV-9168)
      3) Also, current syntax is error prone since you need to re-declare the data type (MDEV-9878)

      It would be nice to have the below:
      ALTER TABLE 'myTable' ALTER COLUMN `myField` ADD COMMENT 'a comment'

      Instead of:
      ALTER TABLE `myTable` CHANGE `myField` `myField` varchar(20) NOT NULL COMMENT 'my comment'

      I agree that comments work great, if we have them created while creating the table. But, its almost impossible to update/add them at the later stage for the bigger tables in the production.

      Is there a better way of documenting the tables instead of using comments ? Please let me know.

      Thanks.

      -Bala

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-9103 Altering table comment does a full copy

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-9878 Awkward syntax for altering the column comments

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment - - edited

            Seems to be working:

            create table t1 (a int);
            		 
            alter online table t1 modify a int comment 'test';
            		 
             
            		 
            create table t2 (a int) engine=innodb;
            		 
            alter online table t2 modify a int comment 'test';
            		 
             
            		 
            create table t3 (a int) partition by hash(a) partitions 2;
            		 
            alter online table t3 modify a int comment 'test';

            serg Sergei Golubchik added a comment - - edited Seems to be working: create table t1 (a int ); alter online table t1 modify a int comment 'test' ;   create table t2 (a int ) engine=innodb; alter online table t2 modify a int comment 'test' ;   create table t3 (a int ) partition by hash(a) partitions 2; alter online table t3 modify a int comment 'test' ;
            bala.linux Bala added a comment - - edited

            I tried to verify this again on a MyISAM partitioned table of size 100 GB. As you can see below, it does the table copy. Please check. I guess it should happen instantly (irrespective of the table size).

            mysql> show full processlist;
            		 
            +----------+---------+-----------+-----+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------+----------+
            		 
            | Id       | User    | Host      | db  | Command | Time | State             | Info                                                                                                            | Progress |
            		 
            +----------+---------+-----------+-----+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------+----------+
            		 
            | 23871669 | xyz     | XYZ:52222 | xyz | Query   |   38 | copy to tmp table | alter online table ABC modify `col1` int(10) unsigned NOT NULL DEFAULT '0' comment 'Test Comment.', LOCK=SHARED |    0.165 |
            		 
            | 23875810 | xyz     | XYZ:52710 | xyz | Query   |    0 | init              | show full processlist                                                                                           |    0.000 |
            		 
            +----------+---------+-----------+-----+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------+----------+
            		 
            2 rows in set (0.00 sec)
            		 
             
            		 
            mysql> show variables like '%version%';
            		 
            +-------------------------+---------------------------------+
            		 
            | Variable_name           | Value                           |
            		 
            +-------------------------+---------------------------------+
            		 
            | innodb_version          | 5.6.28-76.1                     |
            		 
            | protocol_version        | 10                              |
            		 
            | slave_type_conversions  |                                 |
            		 
            | version                 | 10.0.24-MariaDB-1~trusty        |
            		 
            | version_comment         | mariadb.org binary distribution |
            		 
            | version_compile_machine | x86_64                          |
            		 
            | version_compile_os      | debian-linux-gnu                |
            		 
            | version_malloc_library  | bundled jemalloc                |
            		 
            +-------------------------+---------------------------------+

            Thanks.

            -Bala

            bala.linux Bala added a comment - - edited I tried to verify this again on a MyISAM partitioned table of size 100 GB. As you can see below, it does the table copy. Please check. I guess it should happen instantly (irrespective of the table size). mysql> show full processlist; +----------+---------+-----------+-----+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----------+---------+-----------+-----+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------+----------+ | 23871669 | xyz | XYZ:52222 | xyz | Query | 38 | copy to tmp table | alter online table ABC modify `col1` int(10) unsigned NOT NULL DEFAULT '0' comment 'Test Comment.', LOCK=SHARED | 0.165 | | 23875810 | xyz | XYZ:52710 | xyz | Query | 0 | init | show full processlist | 0.000 | +----------+---------+-----------+-----+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------+----------+ 2 rows in set (0.00 sec)   mysql> show variables like '%version%'; +-------------------------+---------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------+ | innodb_version | 5.6.28-76.1 | | protocol_version | 10 | | slave_type_conversions | | | version | 10.0.24-MariaDB-1~trusty | | version_comment | mariadb.org binary distribution | | version_compile_machine | x86_64 | | version_compile_os | debian-linux-gnu | | version_malloc_library | bundled jemalloc | +-------------------------+---------------------------------+ Thanks. -Bala
            serg Sergei Golubchik added a comment -

            Very interesting. Note that you have “copy to tmp table” for “alter online” that should not be possible.
            Could you share the table definition (not the data) so that I could try to repeat this?

            serg Sergei Golubchik added a comment - Very interesting. Note that you have “copy to tmp table” for “alter online” that should not be possible. Could you share the table definition (not the data) so that I could try to repeat this?
            bala.linux Bala added a comment - - edited 

            CREATE TABLE `test` (
            		 
              `day` date NOT NULL DEFAULT '0000-00-00',
            		 
              `col1` int(10) unsigned NOT NULL DEFAULT '0',
            		 
              `col2` mediumint(9) NOT NULL DEFAULT '0',
            		 
              `col3` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ff',
            		 
              `col4` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
            		 
              `col5` tinyint(1) NOT NULL DEFAULT '0',
            		 
              `col6` bigint(12) unsigned DEFAULT NULL,
            		 
              `col7` bigint(12) unsigned DEFAULT NULL,
            		 
              `col8` bigint(12) unsigned DEFAULT NULL,
            		 
              `col9` bigint(12) unsigned DEFAULT NULL,
            		 
              PRIMARY KEY (`day`,`col1`,`col2`,`col3`,`col4`,`col5`),
            		 
              KEY `col1` (`col1`),
            		 
              KEY `col2` (`col2`),
            		 
              KEY `col4` (`col4`(16))
            		 
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            		 
            /*!50100 PARTITION BY HASH ( year(day) %2 * 12 + month(day)) PARTITIONS 24 */
            		 
             
            		 
            alter online table test modify `col1` int(10) unsigned NOT NULL DEFAULT '0' comment 'Test Comment.', LOCK=SHARED;

            Please try and let me know if you can repeat this.

            Thanks.

            -Bala

            bala.linux Bala added a comment - - edited CREATE TABLE `test` ( ` day ` date NOT NULL DEFAULT '0000-00-00' , `col1` int (10) unsigned NOT NULL DEFAULT '0' , `col2` mediumint(9) NOT NULL DEFAULT '0' , `col3` varchar (16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ff' , `col4` varchar (64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' , `col5` tinyint(1) NOT NULL DEFAULT '0' , `col6` bigint (12) unsigned DEFAULT NULL , `col7` bigint (12) unsigned DEFAULT NULL , `col8` bigint (12) unsigned DEFAULT NULL , `col9` bigint (12) unsigned DEFAULT NULL , PRIMARY KEY (` day `,`col1`,`col2`,`col3`,`col4`,`col5`), KEY `col1` (`col1`), KEY `col2` (`col2`), KEY `col4` (`col4`(16)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_unicode_ci /*!50100 PARTITION BY HASH ( year(day) %2 * 12 + month(day)) PARTITIONS 24 */   alter online table test modify `col1` int (10) unsigned NOT NULL DEFAULT '0' comment 'Test Comment.' , LOCK=SHARED; Please try and let me know if you can repeat this. Thanks. -Bala
            serg Sergei Golubchik added a comment -

            No, unfortunately, I cannot.
            I've created a table using your CREATE TABLE statement, populated it with 262144 random rows, and run your ALTER TABLE. I've repeated it many times — ALTER was always instant and I've never was able to catch it in the copy to tmp table state.

            serg Sergei Golubchik added a comment - No, unfortunately, I cannot. I've created a table using your CREATE TABLE statement, populated it with 262144 random rows, and run your ALTER TABLE . I've repeated it many times — ALTER was always instant and I've never was able to catch it in the copy to tmp table state.
            serg Sergei Golubchik added a comment -

            Wait, a thought. I was trying it (and everything above) on my development tree, that includes the fix for MDEV-9868.
            While you're using 10.0.24 — without that fix.

            I've repeated your test case with an earlier version of the tree, without MDEV-9868 fix. And I've got copy to tmp table.

            So, I believe, your use case was fixed in MDEV-9868. The fix will be in 10.0.25.

            serg Sergei Golubchik added a comment - Wait, a thought. I was trying it (and everything above) on my development tree , that includes the fix for MDEV-9868 . While you're using 10.0.24 — without that fix. I've repeated your test case with an earlier version of the tree, without MDEV-9868 fix. And I've got copy to tmp table . So, I believe, your use case was fixed in MDEV-9868 . The fix will be in 10.0.25.
            bala.linux Bala added a comment -

            Awesome. I will look forward for 10.0.25.

            Thanks.

            -Bala

            bala.linux Bala added a comment - Awesome. I will look forward for 10.0.25. Thanks. -Bala

            People

              serg Sergei Golubchik
              bala.linux Bala
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.