Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
    
Can you post the error logs? –  user8996 Oct 15 '09 at 22:23

2 Answers 2

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.

share|improve this answer
    
I have not made any changes to the config file in over a year. The last changes made to the structure of the database were over 1.5 months ago. As of yesterday morning the database was running perfectly. The only change was the power outage. –  James Oct 15 '09 at 23:01
    
Could you post your config anyway? It would be helpful to see. –  Jeff Snider Oct 15 '09 at 23:08
    
Was there a ibdata2 file before you followed Zak's instructions? I'm guessing not, since the config does not reference one. Restore the ibdata1 file and delete the ib_log* files. That's probably what Zak meant to say in his answer. –  Jeff Snider Oct 16 '09 at 0:10
    
There was no ibdata2 file. I restored the ibdata1 and deleted the ib_log* files. When I start MySQL it restores the ib_log* files and then fails with the same error that I posted above. I have tried both your suggestions with various levels of innodb_force_recovery set with no effect. –  James Oct 16 '09 at 12:04
    
It sounds likely that your ibdata1 file had garbage data written to it when the power failed. I did a quick calculation and the page it's trying to access is, I think, at the 65.5 Tb point. If your database wasn't that gigantic to begin with, then there's definitely some garbage in there. I'm out of ideas. Sorry. I hope you're able to find another way to get your data or at least reconstruct it to an acceptable point. Good luck! –  Jeff Snider Oct 16 '09 at 14:00

first, backup everything. Second, delete your ibdata1 & ibdata2 files. Now start the mysql service. When mysql tries to restart it will recreate those files clean from scratch, and your mysql should start back up.

share|improve this answer
    
mysql is running now but there are no tables listed in the database. –  James Oct 15 '09 at 22:45
    
This destroys data! What kind of answer is this! –  Joel E Salas Jun 13 '13 at 22:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.