1

I am running MySQL (5.7) on a Windows 2012 server. One of the table (> 1 million records) seems to be corrupted. The mysql error log shows

2019-01-16T09:41:09.256990Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190116 14:41:09
2019-01-16T09:41:25.153201Z 2 [Warning] IP address 'ip' could not be resolved: No such host is known. 
2019-01-16T09:41:25.620502Z 3 [ERROR] InnoDB: Trying to access page number 3 in space 470, space name shop/products, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2019-01-16T09:41:25.623268Z 3 [ERROR] InnoDB: Server exits.

This issue raised when I was inserting records in this table. This table have 22 indexed columns and one of them is FULLTEXT.

I have also tried innodb_force_recovery = [1 - 6], but I am not able to dump the products table. Whenever I tried to access this table it crashes the DB server.

is there any way to get dump or recover this table. I have old dated backup so I can't use that one. Thanks :)

MYSQL Server Details:

  • Version 5.7 (32 bit)

  • Windows Server 2012

  • Database Engine: InnoDB

Updated

I am facing the strange issue. When I execute the table status command it shows the rows count is zero,

but products.ibd file size is 4GB in mysql data folder.

SHOW TABLE STATUS FROM shop LIKE 'products';

enter image description here

File size by OS

enter image description here

  • Mr. Ijaz, Have you check out this detail from your error log? 2019-01-16T09:41:25.620502Z 3 [ERROR] InnoDB: Trying to access page number 3 in space 470, space name shop/products, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server..... Question for you, can you SHOW DATABASES; when started with innodb_force_recovery=3; ? I have invited you to Skype. – Wilson Hauck Jan 16 at 13:33
  • Hi, I have managed to get backup of others tables by innodb_force_recovery = 4, I have sent you the skype request. Thanks – Shoaib Ijaz Jan 16 at 13:54
  • Mr. Ijaz, Thanks for your Skype TALK time today. Please post the details also of all the innodb_force_recovery= numbers you have tried during this incident and screen shot showing Windows is NTFS managed files. – Wilson Hauck Jan 16 at 15:00
  • @WilsonHauck Tried all applicable options but not success, even tried to recover the data from ibd file, voxteneo.com/… – Shoaib Ijaz Jan 17 at 5:04
  • Will check in with you next week by Skype. – Wilson Hauck Jan 17 at 21:58
0

According to the error log, have you chosen the correct my.cnf configure file when you start the MySQL server? Then after you trying set innodb_force_recovery=6, it failed, and could you paste the error log here? (You should check innodb_purge_thread=0 if the "innodb_force_recovery" is greater than 0)

If you set innodb_force_recovery=6, then was the MySQL server running? Could you use mysql client connect and "select * from ..." ? And if you can do this, then you can use mysqldump to backup your data.

  • Hi, I have tried all the the options [1-6] , but only working option is 4 and I have managed to get dump of others tables except the "products" table. Whenever I tried to access this problematic table the server goes down. – Shoaib Ijaz Jan 17 at 8:04
  • What's the log shows when the server goes down? – woson_wang Jan 17 at 8:26
  • InnoDB: Trying to access page number 3 in space 470, space name cartrightmmc/products, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server. – Shoaib Ijaz Jan 17 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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