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

Changing an index comment unnecessarily rebuilds index

Details

    Description

      MariaDB fails to optimize away DROP INDEX, ADD INDEX pairs if the index comment is being changed. This can be repeated with the example that was posted to MDEV-7816:

      CREATE TABLE t1(a INT, b INT);
      CREATE INDEX i1 ON t1(a) COMMENT 'comment1';  
      ALTER TABLE t1 DROP INDEX i1, ADD INDEX i1(a) COMMENT 'comment2';
      SHOW CREATE TABLE t1;
      

      Ever since the MDEV-7816 fix, the ALTER TABLE is unnecessarily requesting the storage engine to rebuild (drop and create) the index:

      diff --git a/sql/sql_table.cc b/sql/sql_table.cc
      index c4b07ad2035..5986e8201c4 100644
      --- a/sql/sql_table.cc
      +++ b/sql/sql_table.cc
      @@ -6368,6 +6368,13 @@ static bool fill_alter_inplace_info(THD *thd,
                 new_field->field->field_index != key_part->fieldnr - 1)
               goto index_changed;
           }
      +
      +    /* Check that key comment is not changed. */
      +    if (table_key->comment.length != new_key->comment.length ||
      +        (table_key->comment.length &&
      +         strcmp(table_key->comment.str, new_key->comment.str) != 0))
      +        goto index_changed;
      +
           continue;
       
         index_changed:
      

      We only need the metadata (t1.frm file) to be updated.

      Attachments

        Issue Links

          Activity

            This logically belongs under the MDEV-11424 umbrella of "any ALTER TABLE that cannot fail due to invalid data should be instantaneous".

            marko Marko Mäkelä added a comment - This logically belongs under the MDEV-11424 umbrella of "any ALTER TABLE that cannot fail due to invalid data should be instantaneous".

            To reduce merging effort I'll fix it in 10.4.

            Also, I don't think we can just ignore comments in all storage engines as it could be used to pass some information to a storage engine.

            kevg Eugene Kosov (Inactive) added a comment - To reduce merging effort I'll fix it in 10.4. Also, I don't think we can just ignore comments in all storage engines as it could be used to pass some information to a storage engine.

            For the record, I just tested that MariaDB is not affected by a similar bug that was recently introduced in MySQL 5.7:

            --source include/have_innodb.inc
            create table test_varchar(i int primary key, v varchar(40), key (v))
            engine=innodb;
            select table_id into @table_id
            from information_schema.innodb_sys_tables where name='test/test_varchar';
            alter table test_varchar comment 'test-5.7.25';
            select table_id=@table_id "not rebuilt"
            from information_schema.innodb_sys_tables where name='test/test_varchar';
            drop table test_varchar;
            

            Because the SELECT returns 1, the table avoids the rebuild, as expected.

            marko Marko Mäkelä added a comment - For the record, I just tested that MariaDB is not affected by a similar bug that was recently introduced in MySQL 5.7: --source include/have_innodb.inc create table test_varchar(i int primary key , v varchar (40), key (v)) engine=innodb; select table_id into @table_id from information_schema.innodb_sys_tables where name = 'test/test_varchar' ; alter table test_varchar comment 'test-5.7.25' ; select table_id=@table_id "not rebuilt" from information_schema.innodb_sys_tables where name = 'test/test_varchar' ; drop table test_varchar; Because the SELECT returns 1, the table avoids the rebuild, as expected.

            People

              kevg Eugene Kosov (Inactive)
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.