| Bug #90816 | Incorrect key file for table during ALTER TABLE | ||
|---|---|---|---|
| Submitted: | 9 May 2018 22:09 | Modified: | 22 Jun 2020 12:29 |
| Reporter: | monty solomon | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.7.21 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[9 May 2018 22:09]
monty solomon
[9 May 2018 22:10]
monty solomon
*************************** 1. row ***************************
Name: se
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 59562482
Avg_row_length: 79
Data_length: 4734853120
Max_data_length: 0
Index_length: 3023585280
Data_free: 88080384
Auto_increment: NULL
Create_time: 2018-04-05 15:11:30
Update_time: 2018-05-09 21:56:38
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8
Comment:
1 row in set (0.00 sec)
[9 May 2018 22:11]
monty solomon
I was watching the file to monitor progess # stat *ib229* File: `#sql-ib229-2048817663.ibd' Size: 6488588288 Blocks: 12673032 IO Block: 4096 regular file Device: ca50h/51792d Inode: 6442451194 Links: 1 Access: (0640/-rw-r-----) Uid: ( 27/ mysql) Gid: ( 27/ mysql) Access: 2018-05-09 21:03:46.614783558 +0000 Modify: 2018-05-09 21:45:27.709268111 +0000 Change: 2018-05-09 21:45:27.709268111 +0000 # stat *ib229* stat: cannot stat `*ib229*': No such file or directory
[9 May 2018 22:11]
monty solomon
I was watching the file to monitor progess # stat *ib229* File: `#sql-ib229-2048817663.ibd' Size: 6488588288 Blocks: 12673032 IO Block: 4096 regular file Device: ca50h/51792d Inode: 6442451194 Links: 1 Access: (0640/-rw-r-----) Uid: ( 27/ mysql) Gid: ( 27/ mysql) Access: 2018-05-09 21:03:46.614783558 +0000 Modify: 2018-05-09 21:45:27.709268111 +0000 Change: 2018-05-09 21:45:27.709268111 +0000 # stat *ib229* stat: cannot stat `*ib229*': No such file or directory
[9 May 2018 23:01]
MySQL Verification Team
Thank you for the bug report. Could you please provide the show create table too and some insert data if needed. Thanks.
[10 May 2018 0:54]
monty solomon
CREATE TABLE `se` ( `p` int(10) unsigned NOT NULL, `w` int(10) unsigned NOT NULL, `v` bigint(20) unsigned NOT NULL, `s` int(10) unsigned NOT NULL, `si` int(10) unsigned NOT NULL, `sp` varchar(255) CHARACTER SET latin1 DEFAULT NULL, `eet` bigint(20) NOT NULL, `ed` bigint(20) NOT NULL, `ro` bigint(20) NOT NULL DEFAULT '0', `ad` char(10) CHARACTER SET latin1 DEFAULT NULL, `t` bigint(20) NOT NULL, `ec` int(10) unsigned NOT NULL, PRIMARY KEY (`p`,`v`,`w`), KEY `p` (`p`,`w`,`s`), KEY `w` (`w`,`p`,`eet`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
[10 May 2018 13:05]
MySQL Verification Team
Hi Monty, Unfortunately, I am not capable of repeating your problem. I have even tried using different page size and key block sizes and it always works (provided key block size is smaller then the size of the page): +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | se | CREATE TABLE `se` ( `p` int(10) unsigned NOT NULL, `w` int(10) unsigned NOT NULL, `v` bigint(20) unsigned NOT NULL, `s` int(10) unsigned NOT NULL, `si` int(10) unsigned NOT NULL, `sp` varchar(255) CHARACTER SET latin1 DEFAULT NULL, `eet` bigint(20) NOT NULL, `ed` bigint(20) NOT NULL, `ro` bigint(20) NOT NULL DEFAULT '0', `ad` char(10) CHARACTER SET latin1 DEFAULT NULL, `t` bigint(20) NOT NULL, `ec` int(10) unsigned NOT NULL, `c` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`p`,`v`,`w`), KEY `p` (`p`,`w`,`s`), KEY `w` (`w`,`p`,`eet`), KEY `p_2` (`p`,`w`,`c`,`s`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ As you can see index is added without problems. It could be something with your I/O or just with configuration settings. I hope that you use innodb strict mode. Can't repeat.
[10 May 2018 16:17]
monty solomon
mysql> select @@sql_mode, @@innodb_strict_mode\G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
@@innodb_strict_mode: 1
[10 May 2018 16:19]
monty solomon
The last time we saw the "Incorrect key file for table" error was in bug #82997.
[10 May 2018 18:31]
monty solomon
It may be related to the server being busy. The server was executing 15K+ QPS while the ALTER TABLE statement was running and the ALTER STATEMENT was failing with that error near the end of its run.
[10 May 2018 19:33]
monty solomon
We successfully completed the migration using pt-online-schema-change and kept the old table. Do you need any information from the old table before we DROP it?
[11 May 2018 11:44]
MySQL Verification Team
Hi, You could mysqldump it, gzip it and preserve it. Then let us know how big it is. We can not repeat 15 K QPS on the hardware that we have for bugs processing, so it could be scheduled for some future moment. Also, then let us know if the ALTER was INPLACE or with COPY.
[22 Jun 2020 2:44]
Jie Zhou
I figure out a way to repeat the bug. Refer to: https://bugs.mysql.com/bug.php?id=99938
[22 Jun 2020 12:29]
MySQL Verification Team
This bug is now a duplicate of: https://bugs.mysql.com/bug.php?id=99938
