[MDEV-18266] Changing an index comment unnecessarily rebuilds index Created: 2019-01-16  Updated: 2019-08-22  Resolved: 2019-07-11

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.1.4, 10.2.0, 10.3.0, 10.4.0
Fix Version/s: 10.4.7

Type: Bug Priority: Major
Reporter: Marko Mäkelä Assignee: Eugene Kosov (Inactive)
Resolution: Fixed Votes: 0
Labels: instant

Issue Links:
Problem/Incident
is caused by MDEV-7816 ALTER with DROP INDEX and ADD INDEX .... Closed
Relates
relates to MDEV-11424 Instant ALTER TABLE of failure-free r... Closed

 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.

 Comments   
Comment by Marko Mäkelä [ 2019-01-16 ]

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

Comment by Eugene Kosov (Inactive) [ 2019-07-10 ]

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.

Comment by Marko Mäkelä [ 2019-08-22 ]

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.

Generated at Thu Feb 08 08:42:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.