MySQL 5.6 – Fix InnoDB Operating System Error Number 23

innodbMySQLmysql-5.6windows

I use a local MySQL 5.6 Server (InnoDB) on a Windows 7 machine to store several schemas of a research project. Until yesterday the system ran without problems. During a query, MySQL Workbench displayed an error 1036 - Table data is read only. Before I had no problems writing information in that table.

Until then, i never stored the root password. When I restarted Workbench, I decided to store the root password in the "Password Storage Vault". Then I restarted the PC. After that, I couldn't reconnect to the server and got following message: Could not connect, server my not be running. - Can’t connect to MySQL Server on ‘127.0.0.1’(10061)

If I try to start the server with in cmd with mysqld, I get the following warning:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).

It seems that the file "ibdata1" is the cause for the problem. When i tried to copy the MySQL data directory, this file couldn't be accessed by the system.

Here is an excerpt from the error log:

2016-02-11 12:14:46 2164 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Normal shutdown

2016-02-11 12:14:46 2164 [Note] Giving 1 client threads a chance to die gracefully
2016-02-11 12:14:46 2164 [Note] Event Scheduler: Purging the queue. 0 events
2016-02-11 12:14:46 2164 [Note] Shutting down slave threads
2016-02-11 12:14:48 2164 [Note] Forcefully disconnecting 0 remaining clients
2016-02-11 12:14:48 2164 [Note] Binlog end
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'partition'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_METRICS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMP'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_TRX'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'InnoDB'
2016-02-11 12:14:48 2164 [Note] InnoDB: FTS optimize thread exiting.
2016-02-11 12:14:48 2164 [Note] InnoDB: Starting shutdown...
2016-02-11 12:14:49 2164 [Note] InnoDB: Shutdown completed; log sequence number 241353715815
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'BLACKHOLE'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'ARCHIVE'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'MRG_MYISAM'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'MyISAM'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'MEMORY'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'CSV'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'sha256_password'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'mysql_old_password'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'mysql_native_password'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'binlog'
2016-02-11 12:14:49 2164 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Shutdown complete

2016-02-11 12:15:19 2144 [Note] Plugin 'FEDERATED' is disabled.
2016-02-11 12:15:20 87c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-02-11 12:15:20 2144 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-02-11 12:15:20 2144 [Note] InnoDB: The InnoDB memory heap is disabled
2016-02-11 12:15:20 2144 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-02-11 12:15:20 2144 [Note] InnoDB: Memory barrier is not used
2016-02-11 12:15:20 2144 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-11 12:15:20 2144 [Note] InnoDB: Not using CPU crc32 instructions
2016-02-11 12:15:20 2144 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-02-11 12:15:20 2144 [Note] InnoDB: Completed initialization of buffer pool
2016-02-11 12:15:31 87c  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.6/en/operating-system-error-codes.html
2016-02-11 12:15:31 2144 [ERROR] InnoDB: File (unknown): 'read' returned OS error 123. Cannot continue operation
2016-02-11 12:16:51 2156 [Note] Plugin 'FEDERATED' is disabled.
2016-02-11 12:16:51 888 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-02-11 12:16:51 2156 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-02-11 12:16:51 2156 [Note] InnoDB: The InnoDB memory heap is disabled
2016-02-11 12:16:51 2156 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-02-11 12:16:51 2156 [Note] InnoDB: Memory barrier is not used
2016-02-11 12:16:51 2156 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-11 12:16:51 2156 [Note] InnoDB: Not using CPU crc32 instructions
2016-02-11 12:16:51 2156 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-02-11 12:16:51 2156 [Note] InnoDB: Completed initialization of buffer pool
2016-02-11 12:16:58 888  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.6/en/operating-system-error-codes.html
2016-02-11 12:16:58 2156 [ERROR] InnoDB: File (unknown): 'read' returned OS error 123. Cannot continue operation
2016-02-11 12:17:56 5004 [Note] Plugin 'FEDERATED' is disabled.
2016-02-11 12:17:56 13a4 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-02-11 12:17:56 5004 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-02-11 12:17:56 5004 [Note] InnoDB: The InnoDB memory heap is disabled
2016-02-11 12:17:56 5004 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-02-11 12:17:56 5004 [Note] InnoDB: Memory barrier is not used
2016-02-11 12:17:56 5004 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-11 12:17:56 5004 [Note] InnoDB: Not using CPU crc32 instructions
2016-02-11 12:17:56 5004 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-02-11 12:17:56 5004 [Note] InnoDB: Completed initialization of buffer pool
2016-02-11 12:18:01 13a4  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.6/en/operating-system-error-codes.html
2016-02-11 12:18:01 5004 [ERROR] InnoDB: File (unknown): 'read' returned OS error 123. Cannot continue operation
2016-02-11 12:20:19 1416 [Note] Plugin 'FEDERATED' is disabled.
2016-02-11 12:20:19 e7c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-02-11 12:20:19 1416 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-02-11 12:20:19 1416 [Note] InnoDB: The InnoDB memory heap is disabled
2016-02-11 12:20:19 1416 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-02-11 12:20:19 1416 [Note] InnoDB: Memory barrier is not used
2016-02-11 12:20:19 1416 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-11 12:20:19 1416 [Note] InnoDB: Not using CPU crc32 instructions
2016-02-11 12:20:19 1416 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-02-11 12:20:19 1416 [Note] InnoDB: Completed initialization of buffer pool
2016-02-11 12:20:29 e7c  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.6/en/operating-system-error-codes.html
2016-02-11 12:20:29 1416 [ERROR] InnoDB: File (unknown): 'read' returned OS error 123. Cannot continue operation

There is enough free space on the disks. MySQL is running on C:\ the data directory is on D:. The user account that runs MySQL has full permissions and privileges on the data directory. I really don't know what to do. Right now I'm backing up my data. Then I will try a chkdsk. But I'm really afraid that this won't help. Do you have other suggestions?

UPDATE:

The backup failed with the "ibdata1" file and one Table file "xyz.MYD" because of reading errors. I did a chkdsk and the check found and repaired some bad sectors. But the MySQL Server didn't start anyway.
Then I used "HDD Regenerator 2011" that was able to repair the bad sectors. Afterwards I could read and backup both files (ibdata1 & xyz.MYD). But the MySQL Server still does not start. What could I do next?

Best Answer

The cause for the problem was most likely an HDD error, while MySQL was writing in table "xyz". Finally I was able to recover the system. Here is what I did:

  1. Data Backup including MySQL datadir (table "xyz" and my "ibdata" could be read/copied, so no complete backup possible) Ran chkdsk —> found but didn’t repair bad sectors
  2. ran HDD Regenerator v2011 —> found and repaired bad sectors
  3. Completed Backup
  4. Tested if MySQL would run after regeneration —> didn’t work
  5. added innodb_force_recovery=1 … 6 to [mysqld] section in my config file (MySQL started only with max value "6")
  6. used mysqlcheck db_name -u root -p to check which table(s) are corrupted
  7. used mysqluc > mysqlfrm to obtain the table structure of the corrupted table (worked only with --diagnostic option)
  8. used myisamchk -r -q table_name to fix the broken table (this table used the myisam engine)
  9. see this: http://dev.mysql.com/doc/refman/5.7/en/myisam-repair.html
  10. I had to use this trick to get it working: FOR %G IN (dir \b c:\mysql\data\mydb\*.myi) DO myisamchk -r -f %G (https://iandunn.name/myisamchk-error-22-on-windows/)
  11. Originally the table had 55721190 rows, after myisamchk repair it had 55721166 rows (no problem for me, because i could identify and restore the lost rows)
  12. Backup
  13. mysqldump -u root -p my_schema > .../recovery_dump.sql
  14. mysql drop all_databases, stopped server, moved ibdata1 and iblogs to tmp folder, deleted innodb_force_recovery=6 in config file, started server
  15. mysql -u root -p my_schema < .../recovery_dump.sql

This "InnoDB Corruption Repair Guide" was a great help. (https://forums.cpanel.net/threads/innodb-corruption-repair-guide.418722/)

Related Question