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

Optimize DROP INDEX, ADD INDEX into RENAME INDEX

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23356 InnoDB: Failing assertion: field->col->mtype == type, crash or ASAN failures in row_sel_convert_mysql_key_to_innobase, InnoDB indexes are inconsistent after INDEX changes

          • Major - Major loss of function.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17376 Server fails to set ADD_PK_INDEX, DROP_PK_INDEX if unique index nominated as PK

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18259 ASAN heap-use-after-free or server crash in get_foreign_key_info

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23571 InnoDB does not raise a warning if ALTER TABLE index operations are optimized away

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Task - A task that needs to be done. MDEV-7318 RENAME INDEX

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-11424 Instant ALTER TABLE of failure-free record format changes

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-13134 Introduce ALTER TABLE attributes ALGORITHM=NOCOPY and ALGORITHM=INSTANT

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            novergaard Nicklas Laine Overgaard created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            Summary Support for alter table rename index Optimize DROP INDEX, ADD INDEX into RENAME INDEX
            marko Marko Mäkelä made changes -
            Issue Type Task [ 3 ] Bug [ 1 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            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.             		MDEV-7318 is requesting SQL syntax for {{ALTER TABLE…RENAME INDEX}}, [similar to MySQL 5.7|https://dev.mysql.com/doc/refman/5.7/en/alter-table.html].

            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
            marko Marko Mäkelä made changes -
            Comment [ I repurposed this ticket for a something similar that does not involve syntax changes. ]
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            kevg Eugene Kosov (Inactive) made changes -
            Assignee Eugene Kosov [ kevg ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Attachment rename_index+varchar_length.patch [ 46681 ]
            kevg Eugene Kosov (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            kevg Eugene Kosov (Inactive) made changes -
            Assignee Eugene Kosov [ kevg ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Eugene Kosov [ kevg ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            kevg Eugene Kosov (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            kevg Eugene Kosov (Inactive) made changes -
            Assignee Eugene Kosov [ kevg ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Sergei Golubchik [ serg ]
            kevg Eugene Kosov (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Eugene Kosov [ kevg ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            kevg Eugene Kosov (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            kevg Eugene Kosov (Inactive) made changes -
            Assignee Eugene Kosov [ kevg ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Eugene Kosov [ kevg ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            kevg Eugene Kosov (Inactive) made changes -
            Assignee Eugene Kosov [ kevg ] Sergei Golubchik [ serg ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4.4 [ 23310 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Eugene Kosov [ kevg ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 81646 ] MariaDB v4 [ 152469 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 114030

            People

              kevg Eugene Kosov (Inactive)
              novergaard Nicklas Laine Overgaard
              Votes:
              4 Vote for this issue
              Watchers:
              11 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.