InnoDB crashed, no MySQL service available

[Peter Horvath]

Dear Friends,

I have a severe issue on my VPS. After the common cron upgrade of cPanel at 2:00 AM tonight, the MySQL service died. At the morning as I tried to recover that, I saw this error message in the error log file:

2018-04-17 22:58:39 31551 [Note] Plugin 'FEDERATED' is disabled.
2018-04-17 22:58:39 31551 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-04-17 22:58:39 31551 [Note] InnoDB: The InnoDB memory heap is disabled
2018-04-17 22:58:39 31551 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-04-17 22:58:39 31551 [Note] InnoDB: Memory barrier is not used
2018-04-17 22:58:39 31551 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-04-17 22:58:39 31551 [Note] InnoDB: Using Linux native AIO
2018-04-17 22:58:39 31551 [Note] InnoDB: Using CPU crc32 instructions
2018-04-17 22:58:39 31551 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-04-17 22:58:39 31551 [Note] InnoDB: Completed initialization of buffer pool
2018-04-17 22:58:39 31551 [Note] InnoDB: Highest supported file format is Barracuda.
2018-04-17 22:58:39 31551 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2888683
2018-04-17 22:58:39 31551 [Note] InnoDB: Database was not shutdown normally!
2018-04-17 22:58:39 31551 [Note] InnoDB: Starting crash recovery.
2018-04-17 22:58:39 31551 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-04-17 22:58:39 31551 [ERROR] InnoDB: Tried to read 16384 bytes at offset 0. Was only able to read 0.
2018-04-17 22:58:39 7fd43daaa740 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2018-04-17 22:58:39 31551 [ERROR] InnoDB: File (unknown): 'read' returned OS error 71. Cannot continue operation

As you see this is the error log from 22:58 PM. I opened a ticket with cPanel, but got only a link how to solve the InnoDB problems, which I can't. On the whole day I tried different ways, the only which was successful was when I moved every ib* files from the /var/lib/mysql/ directory and restarted the mysql service. However it caused another problems with the .frm files of the databases.
So I "restored" the original status and now I have no any idea what to do. Please help, because I have more than 60.000 different files in the mysql directory.
Thank you for any help in advance!

 

[Peter Horvath]

I got very thorough answer from cPanel CUstomer Care, so I see that the issue wasn't caused by the upgrade. However, it is still there. Please help. Thank you!

 

[Infopro]

Check to see if this thread is of any use to you:
Leave that poor ibdata1 file alone!

 

[Peter Horvath]

Check to see if this thread is of any use to you:
Leave that poor ibdata1 file alone!

Yes, sure, I read almost every articles regarding the subject since the problem occured. This one too.

So. Now this is the situation:
- I recovered the MySQL, it is working now. I did it so, that installed a clean MySQL and restored every accounts from full cPanel backups. However, I missed one account (I was very tired than), so there were a situation that the databases were altered, because of their existence on the server (I hope that this make sense?!), i.e. gamerzon_ajax627 was altered to gamerzon_ajax6272. It was 3 of them. So, than I deleted these 3 databases from the /var/lib/mysql/ directory. Yes, I know, that was a mistake. But I didn't know that then. After all I restored these 3, set the file permissions, check the chown settings, etc., thinking that I can see these databases in the database list and can delete them from there. But it isn't worked.
I got always that the table_space ID is the same as in the other database so there is an Innodb error, and should handle the .ibd files (however the MySQL is running). The strange is that I used an online tool to recover the structure of the .ibd files, this was that:
- Removed -
I restored the files from this source as well, but the error was the same.
Finally I deleted the .ibd files from the databases of these, restart the MySQL with innodb_force_recovery = 1, but now I see this error message (for example):

"2018-05-14T14:30:09.090578Z 146462 [ERROR] InnoDB: Failed to find tablespace for table `gamerzon_pres3352`.`psat_tag_count` in the cache. Attempting to load the tablespace with space id 23549
2018-05-14T14:30:09.090636Z 146462 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-05-14T14:30:09.090651Z 146462 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-05-14T14:30:09.090665Z 146462 [ERROR] InnoDB: Cannot open datafile for read-only: './gamerzon_pres3352/psat_tag_count.ibd' OS error: 71
2018-05-14T14:30:09.090679Z 146462 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-05-14T14:30:09.090691Z 146462 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-05-14T14:30:09.090706Z 146462 [ERROR] InnoDB: Could not find a valid tablespace file for `gamerzon_pres3352/psat_tag_count`. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations for how to resolve the issue.
2018-05-14T14:30:09.090806Z 146462 [Warning] InnoDB: Cannot calculate statistics for table `gamerzon_pres3352`.`psat_tag_count` because the .ibd file is missing. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.21 InnoDB Troubleshooting for how to resolve the issue."

I read a lot of articles including the MySQL :: MySQL 5.7 Reference Manual :: 14.21 InnoDB Troubleshooting, but I have no any clue, what to do.
Have anybody any idea how to solve this?

 

[cPanelMichael]

Hello @Peter Horvath,

If the information on the InnoDB Corruption Repair Guide thread isn't helpful in solving the issue, then you may want to consider reaching out to a qualified system administrator for assistance. We provide a list of companies offering system administration services at:

System Administration Services | cPanel Forums

Thank you.