MySQL Bugs: #9709: InnoDB inconsistensy causes "Operating System Error 32/33"

Bug #9709	InnoDB inconsistensy causes "Operating System Error 32/33"
Submitted:	7 Apr 2005 11:32	Modified:	23 Jul 2007 21:49
Reporter:	Joakim Ahlen
Status:	Closed
Category:	Server: InnoDB	Severity:	S1 (Critical)
Version:	4.0.24, 5.0, 5.1	OS:	Microsoft Windows (Windows 2003 Server)
Assigned to:	Vasil Dimov	Target Version:

Description:
We have an InnoDB-database of about 30Gb which apparently has got some type of
inconsistensy. When trying to check the affected tables with "mysqlcheck", mysqld
completely DIES and cannot be restarted by any other means than a reboot of the machine.

----------------------------
$ ./mysqlcheck.exe -uroot -p map
map.biglakes                                       OK
map.map                                            OK
map.map_area                                       OK
c:\mysql\bin\mysqlcheck.exe: Got error: 2013: Lost connection to MySQL server during
query when executing 'CHECK TABLE .
.. '
---------------------------

When trying to start the mysql server again after this error has occurred, the following
can be read in the .err-file:

----------------------------
050407  9:34:09  InnoDB: Operating system error number 32 in a file operation.
InnoDB: See http://dev.mysql.com/doc/mysql/en/InnoDB.html
InnoDB: for installation help.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html
InnoDB: File name D:\mysql\data\ibdata8
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
----------------------------

..after which the mysqld just returns and does not start. The same error keeps coming up
every time you try to restart mysql, although i think (not sure) the filename could be
different every time (ibdataN, where N is any of the files we have). The only way to be
able to start mysqld up again is to reboot the machine. 

Sometimes it states "Operating system error number 33" instead. These errors mean 32
(ERROR_SHARING_VIOLATION)  and 33 (ERROR_LOCK_VIOLATION).

No other program than mysqld accesses the ibdata-files or even the mysql/data-directory
when this error happens. 

No mysqld-nt.exe-process is in the process list after mysqld has died. 

I guess this bug report really consist of two bugs: 
1) Some InnoDB inconsistensy makes mysqld completely crash
2) Somehow windows maintains a lock on the ibdata-files even after mysqld has died, which
makes you unable to restart the mysqld service at all.

How to repeat:
When using our InnoDB-database, running "mysqlcheck" when the machine has been freshly
rebooted repeats the problem every time. 

Since my assumption is that this is caused by some obscure corruption of the InnoDB
database, i cannot repeat it with other databases. 

Suggested fix:
If the database weren't 30Gb large and contained sensitive information i would just send
it to you and let you examine it in detail, but now i don't know. I still have all the
files as they were in this corrupted state though.

It could also be that this isn't at all caused by database corruption but because of high
load on the InnoDB-database or anything. This should explain why the error isn't on the
same ibdataN-file every time.

Joakim,
Could you have a backup software running? If yes, make sure that InnoDB files are
excluded from backup runs, or that mysqld is shut down before making the backup. (Simple
file-copy backups would not be of any use anyway, if the database server weren't
completely idle.)

If no, it looks like that something has corrupted the operating system's file system
cache. 
Does the problem persist if you reboot the operating system?

Hi!

Make sure you do not have another mysqld server running and accessing those files.

Also make sure that you do not have a backup tool or a virus checker that locks those
files.

Regards,

Heikki

"
32 (ERROR_SHARING_VIOLATION) 

The process cannot access the file because it is being used by another process. 

33 (ERROR_LOCK_VIOLATION) 

The process cannot access the file because another process has locked a portion of the
file. 
"

Hi!

File locking was added in the recent 4.1 versions of MySQL.

Sinisa,

on Windows, InnoDB has used restricted file access for several years.

File locking was added to mysqld on Linux and some other Unix versions about a year ago.

Error 32 may result from a virus checker or a backup program accessing the ibdata file.

Regards,

Heikki

I still can't read it, after pausing Google Desktop and temporarily disabling the scanner
on AVG Free. Apache's not even running.
Error:
051217 21:13:19  InnoDB: Operating system error number 32 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html
InnoDB: File name .\ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

Running MySQL Version 5.0, Apache Server, PHP 4, on Windows XP Home Professional, with
Administrator privlages.

I am just wondering about the status of this "not-bug". Since this bug report was
submitted, backup of the ibdata-files in our backup software was excluded, the error
stopped and everything was fine. 

I posted the original bug report about 1.5 years ago and our company has since upgraded
to MySQL 5.0. During a recent reconfigure of the backup software by someone, the excluded
ibdata-files were mistakenly included again, causing MySQL to crash repeatedly for several
hours until i was contacted and remebered this "not-bug" and fixed it.

Has this behaviour still not been fixed even with a new MySQL major release since the
original bug report? From my point of view - an external program being able to cause
MySQL to crash simply by opening a data file for read should be fixed.

Every other scenario than MySQL crashing is better, for instance keeping a lock at all
times of all ibdata-files. This would instead cause the backup software not to be able to
backup files, but i'd much rather have that than database downtime. It would even be
better to fail all database queries being made to the particular ibdata-file that is
locked, than MySQL crashing.

Is there any work being done on this behaviour? Surely a lot of companies out there with
critic applications are using InnoDB on their servers and have backup software running
backuping their MySQL data dir. It's an almost standard setup. 

Regards

Joakim Ahlen

Joakim,

we do have in our TODO to fix this behavior by making InnoDB to retry the I/O operation
if it fails in a non-fatal error. But the fix is not trivial because it is an
asynchronous I/O operation on Windows. I am reopening this bug report so that we do not
forget this TODO item. You cannot expect a fix soon to this.

Regards,

Heikki

hi, i have been having issues with the error 32 on windows for the past few days...
looking through my my.ini file i have found a few inconsistincies...

first, to get to the point that i am here, i had to install the software a few times
(applying the configuration settings was giving an error until 'retry' was hit 3 or 4
times).
then, after that, i tried changing the data directories to no avail... getting frustrated
with it i started over, having a few my.ini files to look at...

which brings me to this discussion...
in one of my ini files i noticed a line 
innodb_data_home_dir='<datapath>'
not recognizing it in my other installs (from previous mysql installs as well as the
multiple installs i did recently) i commented it out...
after that point, when trying to stop/start the service, i was getting the
mysqladministrator hang at 'trying to start the server...' which led to the error 32.

i tried this a few times and then, instead of re-loading the service through the
administrator, i was able to start the service through the windows services.
doing this, it restarts the server properly and mysqladministrator continues normally.

i'm not sure if the change of innodb data directory was what caused it, however
immediately after i uncommented that line on the new install it worked perfectly.

Hi there,

Any news on this subject yet? 

We are using mysql 5.0.15 on windows today and are still experiencing this bug. It has
again caused us trouble since the backup software and mysql data file whereabouts is
subject to change from time to time.

Regards

Joakim Ahlén

Sorry, i mean we are running 5.0.24a.

regards

Joakim Ahlén

reporters, please check if you have windows shadow copy service running - it's been known
to cause these errors too.  also, if you cannot find out who is locking the files, using
a utility like 'handles' or 'filemon' can reveal more information.

I know that our veritas backup exec agent is the one locking our files. I have already
excluded these files from our backup. The problem is that this error occurrs again
whenever the backup software or whereabouts of the ibdata-files are changed.

I don't think that it is a reasonable limitation in mysql that if you have backup
software running and you are using innodb, mysql will crash and die helplessly. 

Simply failing all queries that involves the particular ibdata file being locked would be
more ok, but the preferred way would be that mysql locks these file once and for all att
startup, and noone else is allowed to read them. 

Keep in mind that we have 30Gb of innodb files, and if our backup software happens to be
misconfigured, backup of all these files will take many many hours leaving our mysql in
an unusable state for a whole night or more.

Now, is any work being done to make mysql behave more nicely when this bug is triggered,
and is there a release date for the fix? As stated in october by Heikki Turi, a fix would
be out "soon".

Joakim,

what does the .err log say about what file I/O operation is failing and on which file?

If it is always an 'open' call, then we could let InnoDB to sleep for 1 second, and retry
the open() call.

Regards,

Heikki

I am not sure if it's always 'open' that fails since i have no log files with the error
currently saved. I could check this out by reproducing the error in a sandbox if you
want.

Sleeping for 1 second could be a good solution if you do that in an infinite (or
semi-infinite) loop. Our ibdata-files are 2Gb large so it takes a couple of minutes per
file to backup by our backup software. This is the amount of time your calls would have
to be retried before it would start working again.

Regards 

Joakim Ahlén

You can see what errors are written in the .err-file in my original post. The filenames
for the ibdata-files are incremented as the backup goes along.

http://bugs.mysql.com/bug.php?id=26662 is related to this.

Grab

Queued to 5.0- and 5.1-maint team tree(s)

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

Backup software can cause ERROR_SHARING_VIOLATION or
ERROR_LOCK_VIOLATION conditions during file operations. InnoDB now
retries forever until the condition goes away.