Sunday, July 16, 2006

MySQL Failed to open log file (InnoDB)

One needs to be very careful before deleting files as once deleted the files are gone for good. A friend of mine today called as MySQL was failing to start on his server. Earlier when "cleaning" his server, he had deleted binary log files without fully understanding their role. When I logged in to his server, I noticed the following errors:

060716 17:21:53  mysqld started
060716 17:21:53 InnoDB: Started; log sequence number 0 1736000119
/usr/sbin/mysqld: File './mysql-bin.000002' not found (Errcode: 2)
060716 17:21:53 [ERROR] Failed to open log (file './mysql-bin.000002', errno 2)
060716 17:21:53 [ERROR] Could not open log file
060716 17:21:53 [ERROR] Can't init tc log
060716 17:21:53 [ERROR] Aborting

060716 17:21:53 InnoDB: Starting shutdown...
060716 17:21:55 InnoDB: Shutdown completed; log sequence number 0 1736000119
060716 17:21:55 [Note] /usr/sbin/mysqld: Shutdown complete

060716 17:21:55 mysqld ended


I deleted the entries from mysql-bin.index and MySQL started without a problem.

13 Comments:

At 8:59 PM, Blogger Sheeri said...

I've had problems doing that because sometimes the ownership or permissions on the files changes -- try using PURGE MASTER LOGS to get rid of files that are older than a certain filename (or time). If you need to, you can create fake binary log files so the server can find & delete them.

Please tell your friend the best way to clean the files is to use "PURGE MASTER LOGS" on the mysql commandline.

 
At 9:00 PM, Blogger Frankly Speaking! said...

Indeed, thanks for pointing that out.

Frank

 
At 4:15 PM, Anonymous nerkn said...

I checked /etc/init.d/mysqld and /usr/bin/mysql_safe for 2 hours but your entry did it in one minute.

Some one should write an automatic fix.

 
At 2:59 AM, Anonymous helper said...

delete the contents of the file /var/lib/mysql/mysql-bin-index.index (empty it) and start mysql /etc/init.d/mysql start
Mysql service will start without any problem

 
At 6:52 AM, Anonymous Anonymous said...

Adding the "expire_logs_days" environment variable to my.cnf seems to have done the job for me! Thanks, this blog did put me on the right track for getting rid of +600 MB of log files.

Dimitri

 
At 3:10 AM, Anonymous Anonymous said...

Old post, but still valid!

Thanks!

 
At 5:24 AM, Blogger Vikash Bucha said...

saved me a few hours.
Thanks.

 
At 9:01 AM, Blogger holgi said...

Helped me too at 2008-08-18. Thank you!

 
At 11:27 AM, Blogger pam said...

hi, i have a similar problem.
I haven't delete files. can you help me?

090824 17:03:26 [Note] Plugin 'FEDERATED' is disabled.
090824 17:03:26 InnoDB: Started; log sequence number 0 46409
wampmysqld: File '' not found (Errcode: 2)
090824 17:03:26 [ERROR] Failed to open log (file '', errno 2)
090824 17:03:26 [ERROR] Could not open log file
090824 17:03:26 [ERROR] Can't init tc log
090824 17:03:26 [ERROR] Aborting

 
At 4:31 AM, Blogger Grimnir said...

three years and all at same place...
clear index and start mysqld

echo '' >/var/db/mysql/mysql-bin.index

/usr/local/etc/rc.d/mysql-server start

 
At 5:17 AM, Anonymous Anonymous said...

thank you so much

 
At 3:48 PM, Blogger shorawitz said...

I had a very similar problem, but I found that (some of) the mysqld-bin.###### files had changed ownership from mysql to root. chown'd them to mysql, and it came back to life!

 
At 9:53 PM, Blogger hackerteamtest said...

Thanks so much

 

Post a Comment

<< Home