[MDEV-13640] ALTER TABLE CHANGE and ADD INDEX on auto_increment column fails with "Incorrect key file for table..." Created: 2017-08-24  Updated: 2020-08-25  Resolved: 2018-01-18

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.0, 10.1, 10.3.0, 10.1.26, 10.0.32, 10.2.8, 10.2
Fix Version/s: 10.0.33, 10.1.30, 10.2.12, 10.3.3

Type: Bug Priority: Major
Reporter: Chris Calender (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 2
Labels: None

Issue Links:
Relates
relates to MDEV-9469 'Incorrect key file' on ALTER TABLE Closed
relates to MDEV-9548 Alter table (renaming and adding inde... Closed
relates to MDEV-13625 Merge InnoDB test cases from MySQL 5.6 Closed
relates to MDEV-13626 Merge InnoDB test cases from MySQL 5.7 Closed
relates to MDEV-13668 InnoDB unnecessarily rebuilds table w... Closed
Sprint: 10.0.34

 Description   

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

CREATE TABLE t ( 
a bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
c bigint(20) unsigned NOT NULL, 
PRIMARY KEY (a) 
) ENGINE=InnoDB ;
 
 
 
ALTER TABLE t CHANGE a b BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ADD KEY uk (c);
 
 
 
ERROR 1034 (HY000): Index for table 't' is corrupt; try to repair it



 Comments   
Comment by Elena Stepanova [ 2017-08-24 ]

The error started happening in 10.0 after this fix:

commit 9b23f8054d2f37458901b4505429c30eddc440bc b3df257cfde490066933c4dc8329f9670aa8de58
 
Author: Jan Lindström <jan.lindstrom@mariadb.com>
 
Date:   Thu Aug 11 14:39:47 2016 +0300
 
 
 
    MDEV-10535: ALTER TABLE causes standalone/wsrep cluster crash
 
    
    When checking is any of the renamed columns part of the
 
    columns for new indexes we accessed NULL pointer if checked
 
    column used on index was added on same statement. Additionally,
 
    we tried to check too many indexes, added_index_count
 
    is enough here.

Before that, the same query caused the crash. The crash started happening not long before that, between 10.0.23 and 10.0.24. In 10.0.23 and earlier, the query works okay.

Also reproducible on current 10.1, 10.2, 10.3. Not reproducible on MySQL 5.6, 5.7.

Comment by Arnaud Adant [ 2017-08-25 ]

The workaround is to alter the table in 2 steps.

MariaDB [test]> CREATE TABLE t (
-> a bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> c bigint(20) unsigned NOT NULL,
-> PRIMARY KEY (a)
-> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> ALTER TABLE t CHANGE a b BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> ALTER TABLE t ADD KEY uk (c);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

I filed MDEV-13625 to merge missing InnoDB tests from MySQL 5.6 to MariaDB. Today, I started importing the ALTER TABLE tests.

While doing that, I filed MDEV-13668 which is a regression that was introduced by an attempted fix of MDEV-9469/MDEV-9548.
I think that we should revert the incorrect change and fix the root cause of this.

Comment by Arnaud Adant [ 2017-09-01 ]

Thanks Marko

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

When merging the test innodb.alter_crash from 5.7 to 10.2, I ran into the MDEV-9469/MDEV-9548 "fix" again. The following operation is not supported by MariaDB, but could and should be:

ALTER TABLE t1 ADD INDEX (b), CHANGE c d int, ALGORITHM=INPLACE;

Edit: The reason turned out to be that ENGINE=MyISAM was implied in a CREATE TABLE statement that lacked explicit ENGINE=InnoDB.

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:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.