Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5045

MyRocks altering a column/table comment rebuilds a table

Details

    • Bug
    • Status: On Hold
    • Medium
    • Resolution: Unresolved
    • 8.0.12-2rc1, 5.7.23-24
    • None
    • MyRocks
    • None

    Description

      Modifying a comment for a column or table should be instant operation.
      This one was already fixed for TokuDB here: https://jira.percona.com/browse/PS-4280
      and it is also probably related to this one: https://jira.percona.com/browse/PS-5031
      but this one only applies to RocksDB and is not 8.0 specific so decided to open a separate issue.
      Comments in RocksDB have special meaning for using column families but only when they are used on indexes so this might be a bit harder to fix for RocksDB vs TokuDB.

      Here's to show that it's not instant operation for RocksDB:
      8.0.12 RocksDB

      8.0.12>ALTER TABLE sbtest1 MODIFY `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment';
      Query OK, 3000000 rows affected (58.69 sec)
      Records: 3000000  Duplicates: 0  Warnings: 0
      
      8.0.12>show create table sbtest1;
      +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                           |
      +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sbtest1 | CREATE TABLE `sbtest1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment',
        `c` char(120) NOT NULL DEFAULT '' COMMENT 'test comment',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k_1` (`k`) /*!80000 INVISIBLE */
      ) ENGINE=ROCKSDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
      +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      8.0.12 InnoDB:

      8.0.12>alter table sbtest1 modify `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment2';
      Query OK, 0 rows affected (0.16 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      8.0.12>show create table sbtest1;
      +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                           |
      +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sbtest1 | CREATE TABLE `sbtest1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment2',
        `c` char(120) NOT NULL DEFAULT '' COMMENT 'test comment',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k_1` (`k`) /*!80000 INVISIBLE */
      ) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
      +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      8.0.12 TokuDB:

      8.0.12>alter table sbtest1 modify `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment3';
      Query OK, 0 rows affected (0.05 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      8.0.12>show create table sbtest1;
      +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                           |
      +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sbtest1 | CREATE TABLE `sbtest1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment3',
        `c` char(120) NOT NULL DEFAULT '' COMMENT 'test comment',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k_1` (`k`) /*!80000 INVISIBLE */
      ) ENGINE=TokuDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
      +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      
      8.0.12>alter table sbtest1 comment 'test table2';
      Query OK, 0 rows affected (0.04 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      

      5.7.23 RocksDB:

      5.7.23>ALTER TABLE sbtest1 MODIFY `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment';
      Query OK, 3000000 rows affected (45.62 sec)
      Records: 3000000  Duplicates: 0  Warnings: 0
      
      5.7.23>show create table sbtest1;
      +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table   | Create Table
                                                                                                                                                                  |
      +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sbtest1 | CREATE TABLE `sbtest1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k_1` (`k`)
      ) ENGINE=ROCKSDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=latin1 |
      +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      5.7.23 InnoDB:

      5.7.23>alter table sbtest1 modify `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment2';
      Query OK, 0 rows affected (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      5.7.23>show create table sbtest1;
      +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table   | Create Table
      
                         |
      +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sbtest1 | CREATE TABLE `sbtest1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment2',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k_1` (`k`)
      ) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=latin1 COMMENT='test table' |
      +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      5.7.23 TokuDB:

      5.7.23>alter table sbtest1 modify `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment3';
      Query OK, 0 rows affected (0.05 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      5.7.23>show create table sbtest1;
      +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table   | Create Table                                                                                                                                                                                                                                                                                                                       |
      +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sbtest1 | CREATE TABLE `sbtest1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `k` int(11) NOT NULL DEFAULT '0' COMMENT 'test comment3',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k_1` (`k`)
      ) ENGINE=TokuDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=latin1 COMMENT='test table' |
      +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      
      5.7.23>alter table sbtest1 comment 'test table2';
      Query OK, 0 rows affected (0.05 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              tomislav.plavcic@percona.com Tomislav Plavcic
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 hours, 8 minutes
                  3h 8m

                  Smart Checklist