MySQL Consulting and NoSQL Consulting: MySQL DBA: MySQL Failed to open log file (InnoDB)

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.

12 comments:

Frank said...: Indeed, thanks for pointing that out.

Frank; 9:00 PM
Anonymous 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.; 4:15 PM
Anonymous 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; 2:59 AM
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; 6:52 AM
Anonymous said...: Old post, but still valid!

Thanks!; 3:10 AM
Vikash Bucha said...: saved me a few hours.
Thanks.; 5:24 AM
holgi said...: Helped me too at 2008-08-18. Thank you!; 9:01 AM
Unknown 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; 11:27 AM
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; 4:31 AM
Anonymous said...: thank you so much; 5:17 AM
Unknown 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!; 3:48 PM
hackerteamtest said...: Thanks so much; 9:53 PM