MySQL Bugs: #87463: MySQL Crashes with operating system error number 995 in a file operation

Bug #87463	MySQL Crashes with operating system error number 995 in a file operation
Submitted:	18 Aug 2017 2:02	Modified:	18 Aug 2017 9:55
Reporter:	Guang Yang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.7.15	OS:	Windows (Server 2012R2)
Assigned to:		CPU Architecture:	Any
Tags:	assertion failure, fil0fil.cc, innodb

Description:
MySQL server crashes every a few days.
The error logs are almost the same as follows:
////////////////////////////////////////////////////////
017-08-14T17:19:24.748390Z 1419365 [Note] Aborted connection 1419365 to db: 'hf_rlive' user: 'reader' host: '192.168.1.102' (Got timeout reading communication packets)
2017-08-14T17:38:40.684786Z 1419472 [Note] Aborted connection 1419472 to db: 'hf_rlive' user: 'reader' host: '192.168.1.102' (Got timeout reading communication packets)
2017-08-14T17:38:41.171661Z 1441321 [Note] Aborted connection 1441321 to db: 'hf_rlive' user: 'reader' host: '192.168.1.102' (Got timeout reading communication packets)
2017-08-14T17:38:41.847389Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2017-08-14T17:38:41.847389Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2017-08-15 01:38:41 0x1a08  InnoDB: Assertion failure in thread 6664 in file fil0fil.cc line 5817
InnoDB: Failing assertion: err == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
17:38:42 UTC - mysqld got exception 0x80000003 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=8388608
max_used_connections=16
max_threads=300
thread_count=12
connection_count=12
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 732734 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff65837e262    mysqld.exe!my_sigabrt_handler()[my_thr_init.c:449]
7ff658728249    mysqld.exe!raise()[winsig.c:587]
7ff658727140    mysqld.exe!abort()[abort.c:82]
7ff65848f178    mysqld.exe!ut_dbg_assertion_failed()[ut0dbg.cc:67]
7ff6584b5f2a    mysqld.exe!fil_aio_wait()[fil0fil.cc:5819]
7ff6584587b4    mysqld.exe!io_handler_thread()[srv0start.cc:309]
7ff880f316ad    KERNEL32.DLL!BaseThreadInitThunk()
7ff881854629    ntdll.dll!RtlUserThreadStart()
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
//////////////////////////////////////////////////////////////////////

Our windows server has 12 cores and 64 GB ram. And Our MySQL my.ini file is:
/////////////////////////////////////////////////////////////////////
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 25000M
innodb_log_file_size = 500M
innodb_autoextend_increment = 500
explicit_defaults_for_timestamp = 1

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = D:\MySQL_Server
# datadir = D:\MySQL_Server\data
# port = .....
# server_id = .....

default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 8M
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
max_allowed_packet = 1000M
tmp_table_size = 6000M
max_connections = 300
max_user_connections = 50
innodb_read_io_threads = 64
innodb_write_io_threads = 8
connect_timeout = 60
net_read_timeout = 60
net_write_timeout = 90

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[client]
default-character-set = utf8
///////////////////////////////////////////////////////////////
Do you know how to solve this problem?

How to repeat:
Before every crash, I can see several logs like "2017-08-14T17:38:41.171661Z 1441321 [Note] Aborted connection 1441321 to db: 'xxx' user: 'xxx' host: 'xxx.xxx.xxx.xxx' (Got timeout reading communication packets)". I do not know whether the crash is caused by successive aborted connections. Besides, before a crash, usually multi-thread python programs are fetching data from MySQL server, I do not whether this has some relation with the crashes.

Hi,  Please upgrade to current version of 5.7.19 and test again.

You need the fix for this
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-17.html

"InnoDB; Microsoft Windows: On a MySQL 64-bit build on Windows, a file I/O retry result was misinterpreted due to a missing cast necessary for the correct operation of the retry path, resulting in a failing assertion and operating system error. (Bug #24711351)"

Thanks a lot, Shane. I upgraded MySQL to 5.7.19 just now.
Hope now the server can work well.

Would just like to note one case where i've seen this today.  Seems some race condition during shutdown of mysqld under a heavy write load.

Version: '5.7.23-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
2018-09-27T05:35:01.465406Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180927  7:35:01
2018-09-27T05:56:00.797321Z 0 [Note] mysqld.exe: Normal shutdown

2018-09-27T05:56:00.799321Z 0 [Note] Giving 6 client threads a chance to die gracefully
2018-09-27T05:56:00.800821Z 0 [Note] Shutting down slave threads
2018-09-27T05:56:00.814323Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:00.814823Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:00.915336Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:00.916336Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:01.016848Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:01.017849Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:01.118361Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:01.118861Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
...
2018-09-27T05:56:01.730439Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or 
2018-09-27T05:56:02.442029Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:02.443030Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:02.543542Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:02.544042Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:02.645055Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:02.646055Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:02.746568Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:02.747568Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:02.802075Z 0 [Note] Forcefully disconnecting 2 remaining clients
2018-09-27T05:56:02.802575Z 0 [Warning] mysqld.exe: Forcing close of thread 67  user: 'root'

2018-09-27T05:56:02.804575Z 0 [Warning] mysqld.exe: Forcing close of thread 83  user: 'root'

2018-09-27T05:56:02.806576Z 0 [Note] Event Scheduler: Purging the queue. 1 events
2018-09-27T05:56:02.807576Z 0 [Note] Binlog end
...
2018-09-27T05:56:02.843580Z 0 [Note] Shutting down plugin 'InnoDB'
2018-09-27T05:56:02.844080Z 0 [Note] InnoDB: FTS optimize thread exiting.
2018-09-27T05:56:02.845081Z 0 [Note] InnoDB: Starting shutdown...
2018-09-27T05:56:02.848081Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:02.849081Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:02.945593Z 0 [Note] InnoDB: Dumping buffer pool(s) to E:\mysql-5.7.23-winx64\data\ib_buffer_pool
2018-09-27T05:56:02.949594Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:02.950594Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:02.949594Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 180927  7:56:02
2018-09-27T05:56:03.051107Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:03.052107Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
...
2018-09-27T05:56:04.216755Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:04.217755Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:04.328769Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:04.329769Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:04.438783Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:04.440283Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:04.545797Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:04.546797Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:04.648310Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:04.649310Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:04.752323Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:04.753323Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:04.854336Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:04.855336Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2018-09-27T05:56:04.956349Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2018-09-27T05:56:04.957349Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
...
2018-09-27T05:56:07.348652Z 0 [Note] InnoDB: Shutdown completed; log sequence number 54992561385
2018-09-27T05:56:07.350653Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2018-09-27T05:56:07.350653Z 0 [Note] Shutting down plugin 'MEMORY'
2018-09-27T05:56:07.351153Z 0 [Note] Shutting down plugin 'CSV'
2018-09-27T05:56:07.351153Z 0 [Note] Shutting down plugin 'sha256_password'
2018-09-27T05:56:07.351653Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-09-27T05:56:07.352153Z 0 [Note] Shutting down plugin 'binlog'
2018-09-27T05:56:07.373656Z 0 [Note] mysqld.exe: Shutdown complete

Please let me know if anyone found any appropriate reason for this error and resolution for this bug.

Can anyone re-open this ticket and provide details ?