MySQL Innodb corruption

Question

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:

1. Renamed existing logfiles, then increased the size of the logfiles to 64M and now up to 256M.

2. Verified there is plenty of memory

3. Started in innodb_force_recovery = 4

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                                        | 
+------------+---------+----------------------------------------------------------------+

Answer 1

Since the InnoDB Storage Engine looks operational at the time mysql is restated, you should take the time to cleanup the InnoDB infrastructure

• mysqldump the entire database instance
• reconfigure innodb from scratch
  • Start over with a fresh ibdata1 file
    1. Empty Undo Space
    2. Empty Rollback Segments
    3. Empty Data Dictionary
    4. Empty List of Tablespace IDs
  • Store Each InnoDB table in its own tablespace
  • Configure Buffer Pool to be 4 times the Log File Size
  • Perform Better Flushing of the Buffer Pool
• reload mysql data

Add this to 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   
innodb_file_per_table
innodb_buffer_pool_size=1G
innodb_flush_method=O_DIRECT
[mysqld_safe]   
log-error=/var/log/mysqld.log   
pid-file=/var/run/mysqld/mysqld.pid  

Run these commands to install mysql in a new folder

service mysql restart --skip-networking --skip-grant-tables
mysqldump --all-databases --routines --triggers > /root/MySQLData.sql
service mysql stop
mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_old/mysql/* /var/lib/mysql/mysql/.
chown -R mysql:mysql /var/lib/mysql/mysql/.
service mysql start --skip-networking --skip-grant-tables
mysql < /root/MySQLData.sql
service mysql restart

Give it a Try !!!