I recently inherited a Drupal site that has an Innodb corruption. MySQL will run for sometime–usually about 24-hours but will eventually need to be manually restarted(see log output below). I have tried/verified the following:
-
Renamed existing logfiles, then increased the size of the logfiles to 64M and now up to 256M.
-
Verified there is plenty of memory
-
Started in
innodb_force_recovery = 4
-
Verified that every table is ok with
CHECK TABLE
Despite all these efforts the problem persists. I don't have a stable backup to recover from. I have read that I may need to do the following:
- backup the datadir
- uninstall MySQL
- reinstall
but I don't understand what this would fix since all the tables pass the CHECK TABLE
query.
Here is a sample of my log files. This activity goes back a year:
Number of processes running now: 0
120424 16:05:25 mysqld restarted
120424 16:05:58 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
120424 16:06:14 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 12 657040373.
InnoDB: Doing recovery: scanned up to log sequence number 12 657234827
120424 16:06:14 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
120424 16:06:14 InnoDB: Started; log sequence number 12 657234827
120424 16:06:15 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
Number of processes running now: 0
120424 16:11:27 mysqld restarted
120424 16:11:28 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
120424 16:11:28 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 12 661789262.
InnoDB: Doing recovery: scanned up to log sequence number 12 661789262
120424 16:11:28 InnoDB: Started; log sequence number 12 661789262
120424 16:11:28 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
Number of processes running now: 0
120424 16:17:48 mysqld restarted
120424 16:17:49 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
120424 16:17:49 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 12 662677386.
InnoDB: Doing recovery: scanned up to log sequence number 12 662677386
120424 16:17:49 InnoDB: Started; log sequence number 12 662677386
120424 16:17:49 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
And here is my my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
#innodb_force_recovery = 4
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
innodb_log_file_size=256M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Here is my SHOW ENGINE output:
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | YES | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+---------+----------------------------------------------------------------+
Best Answer
Since the InnoDB Storage Engine looks operational at the time mysql is restated, you should take the time to cleanup the InnoDB infrastructure
Add this to my.cnf
Run these commands to install mysql in a new folder
Give it a Try !!!