MySQL Bugs: #72264: InnoDB: File operation call: 'Windows aio' returned OS error 221.

Bug #72264	InnoDB: File operation call: 'Windows aio' returned OS error 221.
Submitted:	7 Apr 2014 16:51	Modified:	20 Feb 2015 13:34
Reporter:	scott mankowitz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6.12	OS:	Windows (64 bit; server 2008)
Assigned to:		CPU Architecture:	Any
Tags:	221, checkout, crash, ERROR_CHECKOUT_REQUIRED, innodb

Description:
I have an innodb table with about 21GB of data in it. Fairly reliably, even simple access of the table causes a crash. For example, 

SELECT * FROM mirth.d_mc1 limit 1;

causes the server to crash. This is in the log.

Timestamp, Thread, Type, Details
, , , 2014-04-07 11:28:06 1cb8  InnoDB: File name .\mirth\d_mc1.ibd
, , , 2014-04-07 11:28:06 1cb8  InnoDB: File operation call: 'Windows aio' returned OS error 221.
, , , 2014-04-07 11:28:06 1cb8  InnoDB: Cannot continue operation.

After restart, it seems to be able to recover from binary log. According to the microsoft website, Error 221 is

ERROR_CHECKOUT_REQUIRED
221 (0xDD)
The file must be checked out before saving changes.

How to repeat:
I really have no idea how to repeat this bug. Whenever this particular table gets too big, I get this error.

Does the d_mc1.ibd file happen to be compressed in NTFS?

NTFS: yes; compressed: no

I'd guess it's some locking/sharing problem, although I'm not exactly sure about this error 221.  Make sure no antivirus or file indexing utilities scan 
the datadir of mysql..

Can we see the structure of this table:

show create table mirth.d_mc1 \G
show table status from mirth like 'd_mcl';

The table structure is

CREATE TABLE `d_mc1` (
  `METADATA_ID` int(11) NOT NULL,
  `MESSAGE_ID` bigint(20) NOT NULL,
  `CONTENT_TYPE` int(11) NOT NULL,
  `CONTENT` longtext,
  `IS_ENCRYPTED` tinyint(1) NOT NULL,
  `DATA_TYPE` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`MESSAGE_ID`,`METADATA_ID`,`CONTENT_TYPE`),
  KEY `D_MC1_FKI` (`MESSAGE_ID`,`METADATA_ID`),
  CONSTRAINT `D_MC1_FKEY` FOREIGN KEY (`MESSAGE_ID`, `METADATA_ID`) REFERENCES `d_mm1` (`MESSAGE_ID`, `ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The result from 
>show table status from mirth like 'd_mcl';
is empty set.

cross posted:
http://forums.mysql.com/read.php?22,611995,611995

Thank you for the feedback.

Shane did a typo, proper command should contain your table name:

show table status from mirth like 'd_mc1';

Please also check your disk for errors.

show table status from mirth like 'd_mc1';

# Name, Engine, Version, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksum, Create_options, Comment
'd_mc1', 'InnoDB', '10', 'Compact', '6962438', '3148', '21921202176', '0', '208535552', '4194304', NULL, '2014-03-03 15:42:21', NULL, NULL, 'latin1_swedish_ci', NULL, '', ''

Thank you for the feedback.

Such errors are usually symptom of corruption. Please check your hardware. Also check your tables (d_mc1 and d_mm1) using CHECK TABLE command (note that InnoDB will crash the server if it find any error while run CHECK TABLE).

If you don't find any error, please, upgrade to current version 5.6.22, check with it and if problem still exits send us:

1. Full MySQL server error log file
2. Configuration file or output of SHOW GLOBAL VARIABLES
3. Output of SHOW CREATE TABLE d_mm1 and tables which table d_mm1 references.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".