CODEWITHSUNDEEP
mysqldatabaseinnodblogfile
kari.patila
kari.patila

Reputation: 1079

Setting innodb_log_file_size crashes MySQL

So, I'd like to be able to set the max log file size to 64M, but after doing so with innodb_log_file_size=64M MySQL starts OK, but nothing seems to work properly.

EDIT: and by properly I mean not at all. Setting other InnoDB variables aren't causing any problems.

How should I go about troubleshooting this one?

Upvotes: 3

Views: 4306

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

Before changing the innodb_log_file_size, you must flush all remaining transactional data out of it. You simply set innodb_fast_shutdown to 0 or 2.

  • innodb_fast_shutdown = 0 : InnoDB does a slow shutdown, a full purge and an insert buffer merge before shutting down
  • innodb_fast_shutdown = 2 : InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

In light of this, this is how you handle it

mysql -ANe"SET GLOBAL innodb_fast_shutdown = 2"
vi /etc/my.cnf                  # Change innodb_log_file_size = 64M
service mysql stop              # Stop MySQL
rm /var/lib/mysql/ib_logfile0   # Delete log file 1
rm /var/lib/mysql/ib_logfile1   # Delete log file 2
service mysql start             # Start MySQL

Upvotes: 1

Jonathan
Jonathan

Reputation: 19109

I ran into this problem too, and as per @porneL's answer, here were my specific bash steps to correct this:

service mysql stop              # Stop MySQL
rm /var/lib/mysql/ib_logfile0   # Delete log file 1
rm /var/lib/mysql/ib_logfile1   # Delete log file 2
vim my.conf                     # Change innodb_log_file_size = 64M
service mysql start             # Start MySQL

I found these specific steps on the MySQL forums.

Upvotes: 3

Kornel
Kornel

Reputation: 100110

Make sure MySQL shuts down cleanly, and delete (or move elsewhere) all ib_logfile* files from MySQL data directory (/var/lib/mysql/ usually).

I've tested it and worked for me. Here's source of this hint.

InnoDB reports some errors in show table status comment field. You'll find other problems in MySQL error log (hostname.err in MySQL data directory).

Upvotes: 4

Related Questions