Does having Default Database Engine MyISAM and keeping InnoDB tags in my.ini create any problem?

Question

Recently I am getting table corruption issue very often and every time when I check the logs it says "Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL"

Every single time I've to move binary logs from it's existed location to somewhere else and then only I could start the mysql services. I need to know why it happens.

My database engine type is MyISAM, all the database files are in .MYD format. I also have InnoDB parameters in my.ini

(commented)Innodb parameters

innodb_flush_log_at_trx_commit=0
innodb_log_files_in_group=2
innodb_log_file_size=256M
default_table_type = MYISAM
transaction_isolation = REPEATABLE-READ
read_rnd_buffer_size = 8M
(commented)innodb_data_home_dir=D:/eClinicalWorks/mysql/data
innodb_data_file_path = mobiledoc_InnoDb_data1:300M:autoextend
innodb_file_per_table
innodb_additional_mem_pool_size=8M

(commented) configure innodb_buffer_pool_size according to available memory
innodb_buffer_pool_size=256M

innodb_log_buffer_size=8M


(commented)set-variable=key_buffer=16M
innodb_data_home_dir=D:\eClinicalWorks\\mysql\\data

Questions

• Will keeping InnoDB tags enabled in my.ini create problem in?
• Do you think these tags are creating problem?

Need help ASAP.

Regards Suhaas

Answer 1

Binary Logs

As for your binary logs, the bad magic number is making reference to an encoding done at the beginning of the a binary log. It is a base64 constant that looks something like : 0xfe 0x62 0x69 0x6e. This is referred to the binlog magic number. If this encoding is not in this format, it would be reported as corrupt.

You should erase all binary logs and start fresh.

In order to clear them, run this in the mysql client

mysql> RESET MASTER;
mysql> SHOW MASTER STATUS;

This will erase all binary logs and show you the first binary log.

I have written posts on this issue before

• Oct 28, 2012 : Binlog has bad magic number
• Dec 26, 2011 : mysql emits BINLOG rows even though binlog_­format=­STATEMENT

InnoDB Options

It is OK for the InnoDB options to be around. Keep in mind that every time mysql is restarted, InnoDB's infrastructure is validated

• mobiledoc_InnoDb_data1 exist in the innodb_data_file_path (D:/eClinicalWorks/mysql/data)
• mobiledoc_InnoDb_data1 must be at least 300M
• innodb_data_home_dir has ib_logfile0/ib_logfile1
• innodb_log_file_size=256M (checks if ib_logfile0/ib_logfile1 are 256M)
• innodb_buffer_pool_size=256M (allocates the 256M in RAM)
• innodb_log_buffer_size=8M (allocates the 8M in RAM)

If none of your tables are InnoDB but you want to keep the present InnoDB settings and corresponding files, I have a great suggestion:

Add this under the [mysqld] section of my.ini

[mysqld]
skip-innodb

then run

C:\> net stop mysql
C:\> net start mysql

This will make mysqld startup faster because all the steps for InnoDB initialization/validation are bypassed. The memory InnoDB is configured to use (264M [256 + 8]) will not be allocated.

Give it a Try !!!