My Windows Server 2003 lost power yesterday afternoon. Upon bringing the server back up the MySQL 5.0 database would not start. I checked the error log and saw that there were errors accessing pages in the ibdata1 file. There was also an assertion failure that indicated I need to enable innodb_force_recovery. I tried that and set the value to 4, 5, 6 and 8. Each time the mysql server would fail to start. I saw the innodb-tools here but it seems that I need at least a partially functional mysql instance for those to work.
I need to at least recover the data from the tables. The most recent backup does not contain everything that I need to recover. Everything that I have seen so far indicates that I am out of luck. I thought I would see if anyone could point me in the right direction.
Here is the related error log entry:
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
InnoDB: Error: trying to access page number 4294965119 in space 0,
InnoDB: space name .\ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
091015 18:46:22InnoDB: Assertion failure in thread 4648 in file .\fil\fil0fil.c line 3959
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
091015 18:46:22 [ERROR] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Got signal 11. Aborting!
091015 18:46:22 [ERROR] Aborting
091015 18:46:22 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete
Config file:
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
innodb_force_recovery = 8
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=103M
thread_cache_size=8
#*** MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=205M
key_buffer_size=175M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
#*** INNODB Specific options ***
innodb_additional_mem_pool_size=7M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=3498K
innodb_buffer_pool_size=339M
innodb_log_file_size=170M
innodb_thread_concurrency=8
Best Answer
Take a look at http://bugs.mysql.com/bug.php?id=16827. This sounds a lot like your error. The root cause in that case was an accidental change to the config. If you have a backup of your configuration file, you could compare it with your current one.
If you're not sure what to look for, post a copy of your my.cnf and a listing of the files in your mysql data directory.