2

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?

asked Feb 12, 2016 at 13:59
6
  • Were you root when you installed MySQL? Commented Feb 13, 2016 at 5:33
  • Yes, i was a root user. And the whole system worked for month. Commented Feb 13, 2016 at 17:47
  • Could it be "too many open files in system"? Please add the output of SHOW VARIABLES LIKE '%open%'; to your question. Commented Feb 13, 2016 at 18:09
  • Also, go to the command line and run perror 23. Commented Feb 13, 2016 at 18:10
  • With "bad sectors", all bets are off. Turn off the computer and remove the drive that is reporting errors; continuing to try and "repair" things will likely do further damage. Again, in case it wasn't clear: stop trying to use this hard disk. You will lose more data than you already have. Commented Feb 13, 2016 at 18:13

1 Answer 1

1

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/)

answered Feb 16, 2016 at 11:15

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.