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

InnoDB unnecessarily rebuilds table when renaming a column and adding index

    Details

      Description

      A code change that was introduced to address MDEV-9548, MDEV-9469 introduced a severe performance regression to ALTER TABLE…ALGORITHM=INPLACE in MariaDB.

      The commit comment incorrectly claims that when a column is renamed and a secondary index is added, the whole table must be rebuilt.
      Such a rebuild is unnecessary. Index or column names are not written to the InnoDB data files (only to the data dictionary tables).

      Because of this bug, the MySQL 5.6 test innodb.innodb-alter fails in MariaDB 10.0 as follows:

      CURRENT_TEST: innodb.innodb-alter
      mysqltest: At line 301: query 'ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE' failed: 1846: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY.
      

      When the failing statement is executed, the table contains two FULLTEXT indexes on the same column. In MySQL 5.6, renaming the column from c2 to c4 and adding a secondary index on the renamed column will work just fine. The InnoDB in MariaDB unnecessarily insists that the table be rebuilt.

      To add insult to the injury, MariaDB inherits the MySQL InnoDB limitation that creating FULLTEXT indexes is not possible with LOCK=NONE. So, the whole ALTER TABLE operation has to be done while writes to the table are prohibited.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jplindst Jan Lindström
                Reporter:
                marko Marko Mäkelä
              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: