InnoDB: Operating system error number 23 in a file operation

Out of no where I could not initiate the WAMP MySQLd service. Looking in the logs, I found the following:

120103  6:24:07 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use Windows interlocked functions
InnoDB: Compressed tables use zlib 1.2.3
120103  6:24:07  InnoDB: Initializing buffer pool, size = 1.0G
120103  6:24:07  InnoDB: Completed initialization of buffer pool
120103  6:24:07  InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 116924317882
120103  6:24:07  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 116925735845
InnoDB: Transaction 91F293B was in the XA prepared state.
InnoDB: 2 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 1 row operations to undo
InnoDB: Trx id counter is 91F2B00
120103  6:24:12  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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 117578624, file name .\mysql-bin.000073
InnoDB: Starting in background the rollback of uncommitted transactions
120103  6:24:24  InnoDB: Rolling back trx with id 91F2939, 1 rows to undo
120103  6:24:24  InnoDB: 1.1.4 started; log sequence number 116925735845
120103  6:24:24 [Note] Recovering after a crash using mysql-bin
120103  6:24:32  InnoDB: Operating system error number 23 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 e:\wamp\bin\mysql\mysql5.1.53\data\ibdata1
InnoDB: File operation call: 'Windows aio'.
InnoDB: Cannot continue operation.

What can I do to:
1. Get the WAMP MySQLD service up and running
2. Loose the least amount of data

On a side note, I see alteast several files (approx 1GB each) mysql-bin.000001 to mysql-bin.000073. What are these files? Can I safely purge them?
nainilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
The mysql-bin are the transaction logs
Do you have a full backup of the database?
Using the backup and the mysql-bin for transaction that occurred after the backup, you can restore the database to a point prior to the crash.

check the security settings on e:\wamp\bin\mysql\mysql5.1.53\data\ibdata1
xcacls e:\wamp\bin\mysql\mysql5.1.53\data\ibdata1

You can try and check the files offline
http://dev.mysql.com/doc/refman/5.1/en/innochecksum.html
0
nainilAuthor Commented:
@arnold: How / where can I find the innochecksum utility?

How can I use the backup and mysql-bin for restore?
0
skullnobrainsCommented:
error23 is described as "file table overflow"

looks like you cannot create more files on the partition where you installed mysql or possibly hit another limit such as the max number of files in the directory

you need to first correct that and then let mysql do its job and you will not use any data
tell us what filesystem you use, but i guess your best bet is start by running the defragmentation tool

i guess it is also likely that you really have a task (or a virus such as nimda) that creates files endlessly : you definitely should find what thes files are
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

arnoldCommented:
Oops, the innochecksum utility is available on the unix/linuix distro.
mysqlcheck is what available on the windows side.
as the comment http:#a37369004 check where you have mysql installed and then get properties for the data dir.

not sure it is related to a number of files.
0
nainilAuthor Commented:
I actually added the following to my.ini:
innodb_force_recovery=6

I believe this allowed me to start the MySQLDaemon. I am currently backing up the DB using MySQL Dump.

I am wondering if I can clean the Data directory of the BIN-Logs after this backup is complete?
0
arnoldCommented:
0
nainilAuthor Commented:
I am trying to run a MySQL Dump... however, it is failing with the following logs:

120103 17:26:40 [Note] Event Scheduler: Loaded 0 events
120103 17:26:40 [Note] wampmysqld: ready for connections.
Version: '5.5.8-log'  socket: ''  port: 13306  MySQL Community Server (GPL)
120103 17:42:33  InnoDB: Assertion failure in thread 4852 in file ..\..\..\mysql-5.5.8\storage\innobase\btr\btr0cur.c line 4451
InnoDB: Failing assertion: page_no == page_get_page_no(page)
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.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
120103 17:42:33 - mysqld got exception 0xc0000005 ;
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.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

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

thd: 0x5014fd0
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...
01488D74    mysqld.exe!?check_next_symbol@Gis_read_stream@@QAE_ND@Z()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 46CF16C0=SELECT /*!40001 SQL_NO_CACHE */ * FROM `rss`
thd->thread_id=1
thd->killed=NOT_KILLED
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.
InnoDB: Thread 1940 stopped in file ..\..\..\mysql-5.5.8\storage\innobase\os\os0sync.c line 813
InnoDB: Thread 7104 stopped in file ..\..\..\mysql-5.5.8\storage\innobase\os\os0sync.c line 474
InnoDB: Thread 5576 stopped in file ..\..\..\mysql-5.5.8\storage\innobase\os\os0sync.c line 474
0
arnoldCommented:
which my.ini did you setup?
In the example there are different ini files as examples.



Do you have the mysql workbench?
get mysql-front and then run check/repair on the database/tables.

mysqldump databaseofinteret  > database_backup.sql

What command are you running?
0
skullnobrainsCommented:
<quote>
Oops, the innochecksum utility is available on the unix/linuix distro.
mysqlcheck is what available on the windows side.
</quote>

both are available on unix. mysqlcheck is also available on windows, not sure about innochecksum on windows but should exist as well

mysqlcheck only knows how to check and repair myisam tables.
innochecksum only checks innodb pages and i don't believe it repairs anything

----

<quote>
innodb_force_recovery=6
</quote>

this instructs mysql to forget about running transactions but does not adress the problem you have.
if the current transaction cannot complete, the next one possibly will not either

----

<quote>
I am wondering if I can clean the Data directory of the BIN-Logs after this backup is complete?
</quote>

you do not care about binlogs if you do not use replication, which i would assume since you are trying to backup

if you wonder about innodb logs, with the above option you can destroy (or rename) both innnodb logs before startup. mysql will recreate empty logs when it starts. actually, you had better always remove the logs when you use this option.

----

considering the mysqldump, i believe your innodb engine never managed to start properly.
beware toying with innodb recovery options on a system that has problems already is more than likely to produce data loss, and by that i mean the whole data that innodb holds, espetially if you do not use file_per_table

you have an error that originates from the operationg system, or possibly an intermiediate library between mysql and the OS.
the more you toy with innodb without correcting this error the more likely you will lose your data
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.