[MDEV-13668] InnoDB unnecessarily rebuilds table when renaming a column and adding index Created: 2017-08-29  Updated: 2022-08-31  Resolved: 2018-01-18

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
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

Type: Bug Priority: Major
Reporter: Marko Mäkelä Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 1
Labels: compat56, ddl, performance

Issue Links:
PartOf
is part of MDEV-4784 merge test cases from 5.6 Stalled
is part of MDEV-13625 Merge InnoDB test cases from MySQL 5.6 Closed
Problem/Incident
is caused by MDEV-9469 'Incorrect key file' on ALTER TABLE Closed
is caused by MDEV-9548 Alter table (renaming and adding inde... Closed
Relates
relates to MDEV-10535 ALTER TABLE causes standalone/wsrep c... Closed
relates to MDEV-13640 ALTER TABLE CHANGE and ADD INDEX on a... Closed
relates to MDEV-13838 Wrong result after altering a partiti... Closed

 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.

 Comments   
Comment by Marko Mäkelä [ 2017-08-29 ]

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.

Comment by Marko Mäkelä [ 2017-09-21 ]

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.

Comment by Jan Lindström (Inactive) [ 2018-01-18 ]

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

Generated at Thu Feb 08 08:07:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.