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?

Improve this question
6
  • Were you root when you installed MySQL?
    – Rick James
    Commented Feb 13, 2016 at 5:33
  • Yes, i was a root user. And the whole system worked for month.
    – LarsVegas
    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.
    – Rick James
    Commented Feb 13, 2016 at 18:09
  • Also, go to the command line and run perror 23.
    – Rick James
    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.
    – Chris Harrington
    Commented Feb 13, 2016 at 18:13

1 Answer 1

Reset to default
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/)

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.