Bug #90816 Incorrect key file for table during ALTER TABLE
Submitted: 9 May 2018 22:09 Modified: 10 May 2018 19:33
Reporter: monty solomon Email Updates:
Status: Can't repeat Impact on me:
None 
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
Description:
Running an ALTER table statement fails repeatedly with the following error after running for around 30 minutes.

ERROR 1034 (HY000): Incorrect key file for table 'se'; try to repair it

The error log contains the messages

2018-05-09T21:45:27.966085Z 4928038 [ERROR] InnoDB: Encryption algorithm support missing: N
2018-05-09T21:45:27.966543Z 4928038 [ERROR] InnoDB: Unable to read temporary file for table se

How to repeat:
ALTER TABLE se 
ADD c tinyint(1) NOT NULL DEFAULT 0, 
ADD INDEX (p, w, c, s);
[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] Miguel Solorzano
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] Sinisa Milivojevic
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] Sinisa Milivojevic
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.