[MDEV-13668] InnoDB unnecessarily rebuilds table when renaming a column and adding index - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.24, 10.1.12, 10.2.0, 10.3.0
Fix Version/s: 10.0.33, 10.1.30, 10.2.12, 10.3.3
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Labels:
- compat56
- ddl
- performance

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

is caused by

MDEV-9469 'Incorrect key file' on ALTER TABLE

Closed

MDEV-9548 Alter table (renaming and adding index) fails with "Incorrect key file for table"

Closed

is part of

MDEV-4784 merge test cases from 5.6

Stalled

MDEV-13625 Merge InnoDB test cases from MySQL 5.6

Closed

relates to

MDEV-10535 ALTER TABLE causes standalone/wsrep cluster crash

Closed

MDEV-13640 ALTER TABLE CHANGE and ADD INDEX on auto_increment column fails with "Incorrect key file for table..."

Closed

MDEV-13838 Wrong result after altering a partitioned table

Closed

(2 relates to)

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä created issue - 2017-08-29 07:19

Marko Mäkelä made changes - 2017-08-29 07:19

Field	Original Value	New Value
Link		This issue is caused by ~~MDEV-9548~~ [ ~~MDEV-9548~~ ]

Marko Mäkelä made changes - 2017-08-29 07:19

Link

This issue is caused by ~~MDEV-9469~~ [ ~~MDEV-9469~~ ]

Marko Mäkelä made changes - 2017-08-29 07:20

Link

This issue is part of MDEV-4784 [ MDEV-4784 ]

Marko Mäkelä made changes - 2017-08-29 07:20

Status

Open [ 1 ]

Confirmed [ 10101 ]

Marko Mäkelä added a comment - 2017-08-29 08:13

The same problem is repeated in innodb.innodb-wl5980-alter.

The test innodb.innodb-alter is also demonstrating some other problems, including what looks like a duplicate of ~~MDEV-9469~~, and a proof that the MySQL Oracle Bug #19465984 fix was not merged to MariaDB.

Marko Mäkelä added a comment - 2017-08-29 08:13 The same problem is repeated in innodb.innodb-wl5980-alter. The test innodb.innodb-alter is also demonstrating some other problems, including what looks like a duplicate of MDEV-9469 , and a proof that the MySQL Oracle Bug #19465984 fix was not merged to MariaDB.

Marko Mäkelä made changes - 2017-08-29 08:28

Link

This issue is part of ~~MDEV-13625~~ [ ~~MDEV-13625~~ ]

Marko Mäkelä made changes - 2017-08-29 11:22

Link

This issue relates to ~~MDEV-13640~~ [ ~~MDEV-13640~~ ]

Marko Mäkelä added a comment - 2017-09-21 07:57

As part of fixing this, I think that we must carefully review the changes to the inplace_alter_table functions between MySQL 5.6 and MariaDB 10.0/10.1 as well as MySQL 5.7 and MariaDB 10.2.

Marko Mäkelä added a comment - 2017-09-21 07:57 As part of fixing this, I think that we must carefully review the changes to the inplace_alter_table functions between MySQL 5.6 and MariaDB 10.0/10.1 as well as MySQL 5.7 and MariaDB 10.2.

Marko Mäkelä made changes - 2017-09-21 07:57

Link

This issue relates to ~~MDEV-10535~~ [ ~~MDEV-10535~~ ]

Jan Lindström (Inactive) made changes - 2017-09-21 11:35

Assignee

Marko Mäkelä [ marko ]

Jan Lindström [ jplindst ]

Marko Mäkelä made changes - 2017-09-21 11:40

Link

This issue relates to ~~MDEV-13838~~ [ ~~MDEV-13838~~ ]

Jan Lindström (Inactive) made changes - 2017-09-22 09:27

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Jan Lindström (Inactive) added a comment - 2018-01-18 12:48

Fixed on:

commit fc9ff69578fa8c3d818d6eaaa171b4be49d70814

Author: Jan Lindström <jan.lindstrom@mariadb.com>

Date:   Tue Oct 10 10:19:10 2017 +0300

    MDEV-13838: Wrong result after altering a partitioned table

    Reverted incorrect changes done on MDEV-7367 and MDEV-9469. Fixes properly

    also related bugs:

    MDEV-13668: InnoDB unnecessarily rebuilds table when renaming a column and adding index

    MDEV-9469: 'Incorrect key file' on ALTER TABLE

    MDEV-9548: Alter table (renaming and adding index) fails with "Incorrect key file for table"

    MDEV-10535: ALTER TABLE causes standalone/wsrep cluster crash

    MDEV-13640: ALTER TABLE CHANGE and ADD INDEX on auto_increment column fails with "Incorrect key file for table..."

    Root cause for all these bugs is the fact that MariaDB .frm file

    can contain virtual columns but InnoDB dictionary does not and

    previous fixes were incorrect or unnecessarily forced table

    rebuilt. In index creation key_part->fieldnr can be bigger than

    number of columns in InnoDB data dictionary. We need to skip not

    stored fields when calculating correct column number for InnoDB

    data dictionary.

    dict_table_get_col_name_for_mysql

            Remove

    innobase_match_index_columns

            Revert incorrect change done on MDEV-7367

    innobase_need_rebuild

            Remove unnecessary rebuild force when column is renamed.

    innobase_create_index_field_def

            Calculate InnoDB column number correctly and remove

            unnecessary column name set.

    innobase_create_index_def, innobase_create_key_defs

            Remove unneeded fields parameter. Revert unneeded memset.

    prepare_inplace_alter_table_dict

            Remove unneeded col_names parameter

    index_field_t

            Remove unneeded col_name member.

    row_merge_create_index

            Remove unneeded col_names parameter and resolution.

    Effected tests:

             innodb-alter-table : Add test case for MDEV-13668

             innodb-alter : Remove MDEV-13668, MDEV-9469 FIXMEs

                            and restore original tests

             innodb-wl5980-alter : Remove MDEV-13668,  MDEV-9469 FIXMEs

                            and restore original tests

Jan Lindström (Inactive) added a comment - 2018-01-18 12:48 Fixed on: commit fc9ff69578fa8c3d818d6eaaa171b4be49d70814 Author: Jan Lindström <jan.lindstrom@mariadb.com> Date: Tue Oct 10 10:19:10 2017 +0300 MDEV-13838: Wrong result after altering a partitioned table Reverted incorrect changes done on MDEV-7367 and MDEV-9469. Fixes properly also related bugs: MDEV-13668: InnoDB unnecessarily rebuilds table when renaming a column and adding index MDEV-9469: 'Incorrect key file' on ALTER TABLE MDEV-9548: Alter table (renaming and adding index) fails with "Incorrect key file for table" MDEV-10535: ALTER TABLE causes standalone/wsrep cluster crash MDEV-13640: ALTER TABLE CHANGE and ADD INDEX on auto_increment column fails with "Incorrect key file for table..." Root cause for all these bugs is the fact that MariaDB .frm file can contain virtual columns but InnoDB dictionary does not and previous fixes were incorrect or unnecessarily forced table rebuilt. In index creation key_part->fieldnr can be bigger than number of columns in InnoDB data dictionary. We need to skip not stored fields when calculating correct column number for InnoDB data dictionary. dict_table_get_col_name_for_mysql Remove innobase_match_index_columns Revert incorrect change done on MDEV-7367 innobase_need_rebuild Remove unnecessary rebuild force when column is renamed. innobase_create_index_field_def Calculate InnoDB column number correctly and remove unnecessary column name set. innobase_create_index_def, innobase_create_key_defs Remove unneeded fields parameter. Revert unneeded memset. prepare_inplace_alter_table_dict Remove unneeded col_names parameter index_field_t Remove unneeded col_name member. row_merge_create_index Remove unneeded col_names parameter and resolution. Effected tests: innodb-alter-table : Add test case for MDEV-13668 innodb-alter : Remove MDEV-13668, MDEV-9469 FIXMEs and restore original tests innodb-wl5980-alter : Remove MDEV-13668, MDEV-9469 FIXMEs and restore original tests

Jan Lindström (Inactive) made changes - 2018-01-18 12:52

Fix Version/s		10.3.3 [ 22644 ]
Fix Version/s		10.2.12 [ 22810 ]
Fix Version/s		10.1.30 [ 22637 ]
Fix Version/s		10.0.33 [ 22552 ]
Fix Version/s	10.0 [ 16000 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 82293 ]

MariaDB v4 [ 152720 ]

People

Assignee:: Jan Lindström (Inactive)

Reporter:: Marko Mäkelä

Votes:: 1 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-08-29 07:19

Updated:: 2022-08-31 12:58

Resolved:: 2018-01-18 12:52

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration