Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.0, 10.1.26, 10.0.32, 10.2.8, 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
-
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
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-13625 Merge InnoDB test cases from MySQL 5.6
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
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
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.
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.
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
|
The error started happening in 10.0 after this fix:
commit 9b23f8054d2f37458901b4505429c30eddc440bc b3df257cfde490066933c4dc8329f9670aa8de58Author: Jan Lindström <jan.lindstrom@mariadb.com>Date: Thu Aug 11 14:39:47 2016 +0300MDEV-10535: ALTER TABLE causes standalone/wsrep cluster crashWhen checking is any of the renamed columns part of thecolumns for new indexes we accessed NULL pointer if checkedcolumn used on index was added on same statement. Additionally,we tried to check too many indexes, added_index_countis 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.