Skip to main content

MySQL crashed and corrupted database after graceful server reboot

TapanB
Hi, Recently there was a kernel upgrade which was advised by Security Advisor, as usual I clicked on Update button and it upgraded the kernel, after that security advisor suggested to restart the server, which I did using Graceful Server Restart option but after restart the mysql service started crashing, it was being restarted automagically every 4 mins. but I asked my server team to check on that. After their checking, they told me that databases were getting corrupted and they said they will contact cpanel support for this. After a while they told me that they are seeing more errors etc. Later they said they have backed up /var/lib/mysql directory to /var/lib/mysql.original and have restored the recovered databases to the server, but this did'nt fully resolve the issue, later they said they would be restoring site's db from old backups and I agreed, it resolved issue to some extent, but still some site if not many are still having issues connecting to mysql database. So basically for 2 working days mysql was not working due to unknown reasons, coz I don't fiddle with server much, its a simple shared hosting server running cloudlinux with lve with limited resources and tiny packages to make sure that users cannot abuse the resources and such. The server has 24 core processor and 96 GB or RAM with dual hard drives, I don't restart it much but now a days every now and then Security advisor keeps on suggesting that server should be restarted because binary's are outdated etc. I never did a force restart, so how innodb suddenly got corrupted ? Any help is appericiated. I am posting as much info as possible below: User was unable to change password:
---- FTP (File Transfer Protocol) password change has been queued. Error from MySQL query: (XID g9hrxj) Database Error: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. Error from MySQL query: (XID 3q2vqu) Database Error: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. The system could not perform "GRANT" statements for the user "loreipsum" due to errors: (XID g9hrxj) Database Error: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function., (XID 3q2vqu) Database Error: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. ---
Error after restarting mysql service:
--- The system will redirect you in a moment. Error while connecting to MySQL: (XID bdbc7p) Database Connect Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Error while connecting to MySQL: (XID bdbc7p) Database Connect Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /usr/local/cpanel/Cpanel/Mysql.pm line 173. ---
MySQL Log checked by server team:
--- 2016-10-13 17:08:28 7fdd64548700 InnoDB: Error: page 534 log sequence number 7907280233 InnoDB: is in the future! Current system log sequence number 1758571. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: MySQL :: MySQL 5.6 Reference Manual :: 14.21.2 Forcing InnoDB Recovery InnoDB: for more information. 2016-10-13 17:08:28 7fdd64548700 InnoDB: Error: page 741 log sequence number 8287519168 InnoDB: is in the future! Current system log sequence number 1758571. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: MySQL :: MySQL 5.6 Reference Manual :: 14.21.2 Forcing InnoDB Recovery InnoDB: for more information. 2016-10-13 17:08:28 150219 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-10-13 17:08:28 150219 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-10-13 17:08:29 150219 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 ---
More error found in log file:
--- Oct 13 18:48:56 host mysqld_safe: 161013 18:48:56 mysqld_safe Number of processes running now: 1 Oct 13 18:48:56 host mysqld_safe: 161013 18:48:56 mysqld_safe mysqld process hanging, pid 220260 - killed Oct 13 18:48:56 host mysqld_safe: 161013 18:48:56 mysqld_safe mysqld restarted Oct 13 18:48:56 host mysqld_safe: /usr/bin/mysqld_safe: line 166: 220260 Killed nohup /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/host.lorem-ipsum.com.err --open-files-limit=50000 --pid-file=/var/lib/mysql/host.lorem-ipsum.com.pid < /dev/null >> /var/lib/mysql/host.lorem-ipsum.com.err 2>&1 Oct 13 18:48:56 host mysqld_safe: 161013 18:48:56 mysqld_safe mysqld from pid file /var/lib/mysql/host.lorem-ipsum.com.pid ended Oct 13 18:48:56 host systemd: Stopping MySQL database server... ---
More innodb related issues as said by server team:
--- 2016-09-29 14:59:41 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:42 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:43 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:44 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:45 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:46 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:47 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 15:01:21 7fc4c5803740 InnoDB: Operating system error number 11 in a file operation. InnoDB: Error number 11 means 'Resource temporarily unavailable'. InnoDB: Some operating system error numbers are described at InnoDB: MySQL :: MySQL 5.6 Reference Manual 2016-09-29 15:01:21 3231 [ERROR] InnoDB: Can't open './ibdata1' 2016-09-29 15:01:21 3231 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data! 2016-09-29 15:01:21 3231 [ERROR] Plugin 'InnoDB' init function returned error. 2016-09-29 15:01:21 3231 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2016-09-29 15:01:21 3231 [ERROR] Unknown/unsupported storage engine: InnoDB 2016-09-29 15:01:21 3231 [ERROR] Aborting 2016-09-29 15:01:21 0 [ERROR] Governor not connected 2016-09-29 15:01:21 4828 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 15:06:07 0 [ERROR] Governor not connected 2016-09-29 15:06:07 0 [ERROR] Governor not connected ---
More errors in log file logging back to Sept 29:
--- 2016-09-29 14:59:41 3231 [Note] Plugin 'FEDERATED' is disabled. 2016-09-29 14:59:41 3231 [Note] InnoDB: Using atomics to ref count buffer pool pages 2016-09-29 14:59:41 3231 [Note] InnoDB: The InnoDB memory heap is disabled 2016-09-29 14:59:41 3231 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-09-29 14:59:41 3231 [Note] InnoDB: Memory barrier is not used 2016-09-29 14:59:41 3231 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-09-29 14:59:41 3231 [Note] InnoDB: Using Linux native AIO 2016-09-29 14:59:41 3231 [Note] InnoDB: Using CPU crc32 instructions 2016-09-29 14:59:41 3231 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-09-29 14:59:41 3231 [Note] InnoDB: Completed initialization of buffer pool 2016-09-29 14:59:41 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:41 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:41 3231 [Note] InnoDB: Retrying to lock the first data file 2016-09-29 14:59:42 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:42 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:43 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:43 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:44 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:44 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:45 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:45 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:46 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:46 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:47 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:47 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. ---

Comments

4 comments

Date Votes
  • cPanelMichael
    -09-29 14:59:47 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 15:01:21 7fc4c5803740 InnoDB: Operating system error number 11 in a file operation. InnoDB: Error number 11 means 'Resource temporarily unavailable'. InnoDB: Some operating system error numbers are described at InnoDB: MySQL :: MySQL 5.6 Reference Manual 2016-09-29 15:01:21 3231 [ERROR] InnoDB: Can't open './ibdata1' 2016-09-29 15:01:21 3231 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data! 2016-09-29 15:01:21 3231 [ERROR] Plugin 'InnoDB' init function returned error. 2016-09-29 15:01:21 3231 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2016-09-29 15:01:21 3231 [ERROR] Unknown/unsupported storage engine: InnoDB 2016-09-29 15:01:21 3231 [ERROR] Aborting

    Hello @TapanB, I'm sorry to see you are having trouble with the MySQL service. The output suggests InnoDB is corrupted on this system. We've created the following thread to guide users through the repair process: InnoDB Corruption Repair Guide Let us know if this helps. Thank you.
    0
  • Bazinga
    Hi, Recently there was a kernel upgrade which was advised by Security Advisor, as usual I clicked on Update button and it upgraded the kernel, after that security advisor suggested to restart the server, which I did using Graceful Server Restart option but after restart the mysql service started crashing, it was being restarted automagically every 4 mins. but I asked my server team to check on that. After their checking, they told me that databases were getting corrupted and they said they will contact cpanel support for this. After a while they told me that they are seeing more errors etc. Later they said they have backed up /var/lib/mysql directory to /var/lib/mysql.original and have restored the recovered databases to the server, but this did'nt fully resolve the issue, later they said they would be restoring site's db from old backups and I agreed, it resolved issue to some extent, but still some site if not many are still having issues connecting to mysql database. So basically for 2 working days mysql was not working due to unknown reasons, coz I don't fiddle with server much, its a simple shared hosting server running cloudlinux with lve with limited resources and tiny packages to make sure that users cannot abuse the resources and such. The server has 24 core processor and 96 GB or RAM with dual hard drives, I don't restart it much but now a days every now and then Security advisor keeps on suggesting that server should be restarted because binary's are outdated etc. I never did a force restart, so how innodb suddenly got corrupted ? Any help is appericiated. I am posting as much info as possible below: User was unable to change password:
    ---- FTP (File Transfer Protocol) password change has been queued. Error from MySQL query: (XID g9hrxj) Database Error: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. Error from MySQL query: (XID 3q2vqu) Database Error: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. The system could not perform "GRANT" statements for the user "loreipsum" due to errors: (XID g9hrxj) Database Error: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function., (XID 3q2vqu) Database Error: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. ---
    Error after restarting mysql service:
    --- The system will redirect you in a moment. Error while connecting to MySQL: (XID bdbc7p) Database Connect Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Error while connecting to MySQL: (XID bdbc7p) Database Connect Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /usr/local/cpanel/Cpanel/Mysql.pm line 173. ---
    MySQL Log checked by server team:
    --- 2016-10-13 17:08:28 7fdd64548700 InnoDB: Error: page 534 log sequence number 7907280233 InnoDB: is in the future! Current system log sequence number 1758571. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: MySQL :: MySQL 5.6 Reference Manual :: 14.21.2 Forcing InnoDB Recovery InnoDB: for more information. 2016-10-13 17:08:28 7fdd64548700 InnoDB: Error: page 741 log sequence number 8287519168 InnoDB: is in the future! Current system log sequence number 1758571. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: MySQL :: MySQL 5.6 Reference Manual :: 14.21.2 Forcing InnoDB Recovery InnoDB: for more information. 2016-10-13 17:08:28 150219 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-10-13 17:08:28 150219 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-10-13 17:08:29 150219 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 ---
    More error found in log file:
    --- Oct 13 18:48:56 host mysqld_safe: 161013 18:48:56 mysqld_safe Number of processes running now: 1 Oct 13 18:48:56 host mysqld_safe: 161013 18:48:56 mysqld_safe mysqld process hanging, pid 220260 - killed Oct 13 18:48:56 host mysqld_safe: 161013 18:48:56 mysqld_safe mysqld restarted Oct 13 18:48:56 host mysqld_safe: /usr/bin/mysqld_safe: line 166: 220260 Killed nohup /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/host.lorem-ipsum.com.err --open-files-limit=50000 --pid-file=/var/lib/mysql/host.lorem-ipsum.com.pid < /dev/null >> /var/lib/mysql/host.lorem-ipsum.com.err 2>&1 Oct 13 18:48:56 host mysqld_safe: 161013 18:48:56 mysqld_safe mysqld from pid file /var/lib/mysql/host.lorem-ipsum.com.pid ended Oct 13 18:48:56 host systemd: Stopping MySQL database server... ---
    More innodb related issues as said by server team:
    --- 2016-09-29 14:59:41 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:42 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:43 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:44 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:45 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:46 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:47 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 15:01:21 7fc4c5803740 InnoDB: Operating system error number 11 in a file operation. InnoDB: Error number 11 means 'Resource temporarily unavailable'. InnoDB: Some operating system error numbers are described at InnoDB: MySQL :: MySQL 5.6 Reference Manual 2016-09-29 15:01:21 3231 [ERROR] InnoDB: Can't open './ibdata1' 2016-09-29 15:01:21 3231 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data! 2016-09-29 15:01:21 3231 [ERROR] Plugin 'InnoDB' init function returned error. 2016-09-29 15:01:21 3231 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2016-09-29 15:01:21 3231 [ERROR] Unknown/unsupported storage engine: InnoDB 2016-09-29 15:01:21 3231 [ERROR] Aborting 2016-09-29 15:01:21 0 [ERROR] Governor not connected 2016-09-29 15:01:21 4828 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 15:06:07 0 [ERROR] Governor not connected 2016-09-29 15:06:07 0 [ERROR] Governor not connected ---
    More errors in log file logging back to Sept 29:
    --- 2016-09-29 14:59:41 3231 [Note] Plugin 'FEDERATED' is disabled. 2016-09-29 14:59:41 3231 [Note] InnoDB: Using atomics to ref count buffer pool pages 2016-09-29 14:59:41 3231 [Note] InnoDB: The InnoDB memory heap is disabled 2016-09-29 14:59:41 3231 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-09-29 14:59:41 3231 [Note] InnoDB: Memory barrier is not used 2016-09-29 14:59:41 3231 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-09-29 14:59:41 3231 [Note] InnoDB: Using Linux native AIO 2016-09-29 14:59:41 3231 [Note] InnoDB: Using CPU crc32 instructions 2016-09-29 14:59:41 3231 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-09-29 14:59:41 3231 [Note] InnoDB: Completed initialization of buffer pool 2016-09-29 14:59:41 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:41 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:41 3231 [Note] InnoDB: Retrying to lock the first data file 2016-09-29 14:59:42 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:42 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:43 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:43 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:44 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:44 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:45 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:45 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:46 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:46 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2016-09-29 14:59:47 3231 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2016-09-29 14:59:47 3231 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. ---

    The errors like "InnoDB: Unable to lock ./ibdata1, error: 11" looks to be nor related to the original crash. Indeed, there is open bugreport with governor about properly stopping/starting mysql service on CloudLinux 7 . This was not fixed yet. Hard to say why mysql crashed originally, see nothing that could shed some light in logs.
    0
  • aanbar
    I would check the hard disks just to be sure it isn't a hardware issue try running a smart check
    0
  • Sujoy Dhar
    I too faced this and I have too pay a lot for this ! Cloud Linux is crap, in the beginning it crashed my OS and then my databases after installing the MSQL governor ! What to say the most worst Cloudlinux system It gives headache instead of relaxation !
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post