1

Sometime ago I noticed my WordPress sites wont load. I found the problem with MySQL.

The server I am running on is a VPS server with:

  • Debian 8.1
  • MariaDB - Ver 15.1 Distrib 10.1.14-MariaDB
  • 2048 MB RAM
  • 45GB SSD
  • 2 vCores

What I did was to add innodb_force_recovery=2 right after the [mysqld] section tag. I restarted mysql and it loaded in read only mode.

Then, I wanted to find which table is corrupt, so I ran mysqlcheck -u root -p --all-databases and then entered my password to login. The output started going table by table (line-by-line) showing OK at the end. Then it gets into the biggest database I have which is made of three tables (2.1 million rows, 45 million rows, and 1.9 million rows). It does not show any output at that stage, and it just hangs there.

Is there a better way to find out which table is corrupted? Can I print only the bad tables, instead of printing OK for everything?

UPDATE /var/log/syslog has the following entries after mysql service restart:

[Note] /usr/sbin/mysqld: Normal shutdown
[Note] Event Scheduler: Purging the queue. 0 events
[Note] InnoDB: FTS optimize thread exiting.
[Note] InnoDB: Starting shutdown...
[Note] InnoDB: Shutdown completed; log sequence number 32047521550
[Note] /usr/sbin/mysqld: Shutdown complete
[Note] /usr/sbin/mysqld (mysqld 10.1.14-MariaDB-1~jessie) starting as process 29607 ...
[Note] Using unique option prefix 'myisam_recover' is error-prone and can break in the future. $
[Note] InnoDB: Using mutexes to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Memory barrier is not used
[Note] InnoDB: Compressed tables use zlib 1.2.8
[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Using SSE crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 256.0M
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: Highest supported file format is Barracuda.
[Note] InnoDB: 128 rollback segment(s) are active.
[Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.29-76.2 started; log sequence numbe$
[Note] InnoDB: !!! innodb_force_recovery is set to 2 !!!
[Note] InnoDB: Dumping buffer pool(s) not yet started
[Note] Plugin 'FEEDBACK' is disabled.
[Note] Server socket created on IP: '127.0.0.1'.
[Note] Reading of all Master_info entries succeded
[Note] Added new Master_info '' to hash table
[Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.14-MariaDB-1~jessie'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
May 23 16:38:38 warp debian-start[29635]: Checking for corrupt, not cleanly closed and upgrade needing tables.
mysqld[29607]: InnoDB: innodb_force_recovery is on: we do not allow
mysqld[29607]: InnoDB: database modifications by the user. Shut down
mysqld[29607]: InnoDB: mysqld and edit my.cnf so thatInnoDB: innodb_force_... is removed.
  • Check the error log for more info. – jkavalik May 23 '16 at 20:08
  • which one? look for what? – KingsInnerSoul May 23 '16 at 20:30
  • server error log, the one from log_error - might be in /var/log/.., datadir or even logged to syslog, but you need to find it and see the exact reasons for the failure. – jkavalik May 23 '16 at 20:33
  • No error is present. It just hang the process.... – KingsInnerSoul May 23 '16 at 20:34
  • There should be some (older) errors when the service did not start and then some info about the force recovery progress etc.. It might even identify the "bad" table during startup. – jkavalik May 23 '16 at 20:37

Your Answer

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

Browse other questions tagged or ask your own question.