MySQL Bugs: #3139: Mysql crashes: "windows error 995" after several selects on a large DB

Bug #3139	Mysql crashes: "windows error 995" after several selects on a large DB
Submitted:	10 Mar 2004 22:10	Modified:	11 Nov 1:52
Reporter:	Alexey Skorokhodov
Status:	Patch approved
Category:	Server: InnoDB	Severity:	S1 (Critical)
Version:	4.1.19, 4.1.1 alpha, 5.0.27, 5.0.44, 5.4.4	OS:	Microsoft Windows (Windows 2000/XP/2003)
Assigned to:	Calvin Sun	Target Version:
Triage:	Triaged: D1 (Critical)

Description:
Mysql 4.1.1 alpha crashes when I execute several select operators on my DB with large
tables: one of the tables contains about 1.5 mln records. The sql-queries retrieve data
from this large table.
The problem begins to happen when the table size grown up from 2000 to 1500000 records.

"mysql.err" file contains:
MySQL: ready for connections.
Version: '4.1.1-alpha-max-nt'  socket: ''  port: 3306
040311 10:56:57  InnoDB: Operating system error number 995 in a file operation.
InnoDB: See section 13.2 at http://www.innodb.com/ibman.html
InnoDB: about operating system error numbers.
InnoDB: File name .\ibdata1
InnoDB: File operation call: 'Windows aio'.
InnoDB: Cannot continue operation.

How to repeat:
Create table with about 1500000 records, use several selects one-by-one. in my case sql
operators are like:

select count(1) from message where  InOut='1'  
and InOutDate >= '2004-03-10 15:06:00' and InOutDate < '2004-03-10 15:07:00'

Hi!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_c...

995 The I/O operation has been aborted because of either a thread exit or an application
request. ERROR_OPERATION_ABORTED

I am not sure, but I think someone else has reported the same error. My first guess is a
bug in Windows/drivers/hardware.

If you tune:
"
innodb_file_io_threads 

Number of file i/o threads in InnoDB. Normally, this should be 4, but on Windows disk i/o
may benefit from a larger number. Numeric my.cnf parameter format. 
"
in my.cnf or my.ini, does it have any effect on the problem?

Regards,

Heikki

I have added "innodb_file_io_threads=20" to my "my.cnf" file. Mysql still crashes when
executing those queries. :-(

Hi!

Try making the value smaller.

But this is probably a bug in the OS/drivers/hardware.

Regards,

Heikki

I updated the value to 8. The problem still exists. 
I have checked the hard drive with Windows HDD checker and found no errors. I have no
antivirus scanners or software, which can interrupt Mysql requests to HDD. How to
determine and solve the problem in this case?
This only happens with Mysql, and not with any other programs.

I'm updating the "Synopsis" field

I created an InnoDB table and filled it with about 2 millions rows without to
have success in repeat the behavior reported.

I'm unable to reproduce the bug on another computer with the same DB installed. Looks like
a bug in my Windows installation, as Heikki said.
I don't know what to do with my MySQL installation - I'll try to delete INNODB data files
and to recreate them. I hope this will help.
Thanks for your support and time!

Closed according your last post. Thank you for let know us about.

Hi!

A couple of weeks ago another user reported error number 995 in Windows. Still it looks
like an OS bug, or faulty hardware.

Regards,

Heikki

Can someone tell me why this bug #3139 was closed? As of July 2006 I'm still encountering
it on a daily basis and I see no resolution here on this website. Is the general
consensus that it is an OS bug in Windows? If so, is there a workaround??

In some cases, the problem cause might be a SAN storage system with too small command
queue for the load.

Hi! I've run into the same problem. There are other indications about the issue over the
internet. I've read that the problem thought to be related to windows/hardware but it
looks the reason is inside InnoDB.

Windows Error number 995 (ERROR_OPERATION_ABORTED) indicates that an async I/O operation
was cancelled. Besides "normal" I/O cancellation (with CancelIO call), windows
automatically cancels any pending I/O requests when the I/O-issuing thread exists. In my
case I've got a number of short query executions with mysql.exe against a large database
so clients connect and disconnect frequently. Every connection forces MySQL to allocate a
separate thread that lives until a connection is open. Under certain timing it is possible
for a client to disconnect before all issued write operations have been completed by
windows I/O sussystem. As it is a timing issue, I'm not sure if a reliable test case can
be created.

To see if the problem is in fact exists, I added simple tracing into the code creating
and closing threads/connections and where the I/O error is handled and it's actually
happenning that connection is close _before_ I/O completion.

If I add os_aio_wait_until_no_pending_writes() call in the innobase_close_connection()
function, the problem disappears.

Simplest fix would be just adding os_aio_wait_until_no_pending_writes() as I did,
however, that could cause performance problem in a high load scenario because _every_
connection would wait for I/O request of every other connection thus causing extra I/O
serialization.

I suggest tracking a number of outstanding write requests per each thread using either
thread local storage or providing a thread-related object (e.g. ) to I/O routines. On
connection termination, a worker threads would then wait until all I/O request have been
processed.

I'm using 5.0.45 version on Windows XP.

Should the bug be reopened?

Please disregard what I wrote about os_aio_wait_until_no_pending_writes() - it didn't
help, actually I was added Sleep(1000) when the problem disappeared.

I encountered this problem also.
MySQL version is 5.0.45community
The windows version is Windows Server 2003 with service pack 2

We observed the same problem with mysql 5.1.30, Windows 2003.

I do not think it is hardware issue, as problem was the same on two different (but in
identical configuration) boxes.

I've reopened this bug because we seem to have an ability to repeat the problem now.

The problem is being observed periodically in our tests.

Internal testing of a possible workaround fix for this is happening. No ETA at present.

Hi All,

I am facing the same ERROR "InnoDB: Operating system error number 995 in a file
operation."? Is this issue resolved? I found no solution on the site.

=======
091111 14:46:59  InnoDB: Operating system error number 995 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: File name .\ibdata1
InnoDB: File operation call: 'Windows aio'.
InnoDB: Cannot continue operation.
InnoDB: Log scan progressed past the checkpoint lsn 0 476737329
091111 16:03:47  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 0 476737892
091111 16:03:47  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 757583, file name .\mysql-bin.000018
091111 16:03:48  InnoDB: Started; log sequence number 0 476737892
091111 16:03:48 [Note] Recovering after a crash using mysql-bin
091111 16:03:48 [Note] Starting crash recovery...
091111 16:03:48 [Note] Crash recovery finished.
091111 16:03:48 [Note] Event Scheduler: Loaded 0 events
091111 16:03:48 [Note] wampmysqld: ready for connections.
Version: '5.1.32-community-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
====

Hi Samir,

Sorry to hear that you have the same problem. We have been testing a fix, and hope the
fix will be included in a near future release.

Thanks,
Calvin

Hello Calvin,

Thank you for your immediate response. I am sorry to say that this is my live server and
running live transactions. I request, if you find any solution for this please let me
know ASAP.

Thanks in advance.

Samir.