Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.4, 10.2.0, 10.3.0, 10.4.0
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
- is caused by
-
-
- Closed
-
- relates to
-
MDEV-11424 Instant ALTER TABLE of failure-free record format changes
-
- Closed
-
- links to
Activity
| Link |
This issue blocks |
| NRE Projects | RM_104ES_CANDIDATE RM_105_CANDIDATE | |
| Assignee | Eugene Kosov [ kevg ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
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.
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Assignee | Eugene Kosov [ kevg ] | Thirunarayanan Balathandayuthapani [ thiru ] |
| Status | Confirmed [ 10101 ] | In Review [ 10002 ] |
| Assignee | Thirunarayanan Balathandayuthapani [ thiru ] | Eugene Kosov [ kevg ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.4.7 [ 23720 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
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.
| Remote Link | This issue links to "Bug#94383 simple ALTER cause unnecessary InnoDB index rebuilds, 5.7.23 or later (Web Link)" [ 29113 ] |
| Workflow | MariaDB v3 [ 91759 ] | MariaDB v4 [ 155532 ] |
This logically belongs under the
MDEV-11424umbrella of "any ALTER TABLE that cannot fail due to invalid data should be instantaneous".