Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.2, 10.3.0
Description
MDEV-7318 is requesting SQL syntax for ALTER TABLE…RENAME INDEX, similar to MySQL 5.7.
One could use DROP INDEX, ADD INDEX to simulate RENAME INDEX, but that will currently cause InnoDB to rebuild the index. The index rebuild should be optimized away, and only the index should be renamed. The InnoDB source code for renaming indexes exists starting with MariaDB 10.2, but it is disabled.
InnoDB should allow ALGORITHM=INSTANT operation for DROP INDEX, ADD INDEX when there is no actual data change. If an index is marked as corrupted, it would be nice to refuse ALGORITHM=INSTANT and actually rebuild the index.
Attachments
Issue Links
- causes
-
-
- Closed
-
- is blocked by
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Open
-
-
MDEV-7318 RENAME INDEX
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | Support for alter table rename index | Optimize DROP INDEX, ADD INDEX into RENAME INDEX |
| Issue Type | Task [ 3 ] | Bug [ 1 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Component/s | Data Definition - Alter Table [ 10114 ] | |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.3.0 [ 22127 ] | |
| Affects Version/s | 10.2.2 [ 22013 ] | |
| Description |
MySQL 5.7 added an extension to the SQL syntax that allows renaming indexes without dropping and creating the index:
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html I hit the missing support by accident as the dotnet core entity framework provider for mysql creates RENAME INDEX queries when renaming an index, which breaks the migrations. |
One could use {{DROP INDEX}}, {{ADD INDEX}} to simulate {{RENAME INDEX}}, but that will currently cause InnoDB to rebuild the index. The index rebuild should be optimized away, and only the index should be renamed. The InnoDB source code for renaming indexes exists starting with MariaDB 10.2, but it is disabled. InnoDB should allow {{ALGORITHM=INSTANT}} operation for {{DROP INDEX}}, {{ADD INDEX}} when there is no actual data change. If an index is marked as corrupted, it would be nice to refuse {{ALGORITHM=INSTANT}} and actually rebuild the index. |
| Labels | instant |
I repurposed this ticket for a something similar that does not involve syntax changes.
| Comment | [ I repurposed this ticket for a something similar that does not involve syntax changes. ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Eugene Kosov [ kevg ] |
| Attachment | rename_index+varchar_length.patch [ 46681 ] |
I attached the patch which does rename for add index & drop index which contains same column and same prefix.
Patch which does it inside innodb. (Patch also contains indexed varchar column changes). I hope it could be useful.
rename_index+varchar_length.patch
It is based on 10.2
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Eugene Kosov [ kevg ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
I would like to move the rename detection to the SQL layer, so that index renaming can be done instantly on a wider selection of storage engines.
| Assignee | Marko Mäkelä [ marko ] | Eugene Kosov [ kevg ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Eugene Kosov [ kevg ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Marko Mäkelä [ marko ] | Sergei Golubchik [ serg ] |
Note for myself. After the fix for MDEV-17376 lands it'll be worth look at it and check how much impact it will have on this issue.
| Link |
This issue is blocked by |
| Fix Version/s | 10.3 [ 22126 ] |
| Assignee | Sergei Golubchik [ serg ] | Eugene Kosov [ kevg ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Eugene Kosov [ kevg ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Golubchik [ serg ] | Eugene Kosov [ kevg ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Eugene Kosov [ kevg ] | Sergei Golubchik [ serg ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.4.4 [ 23310 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Assignee | Sergei Golubchik [ serg ] | Eugene Kosov [ kevg ] |
| Link |
This issue is duplicated by |
| Link |
This issue causes |
| Link | This issue relates to MDEV-23571 [ MDEV-23571 ] |
| Workflow | MariaDB v3 [ 81646 ] | MariaDB v4 [ 152469 ] |
| Zendesk Related Tickets | 114030 |
This looks like a duplicate of
MDEV-7318- https://jira.mariadb.org/browse/MDEV-7318And +1