Mysql – Does having Default Database Engine MyISAM and keeping InnoDB tags in the.ini create any problem

innodbmyisamMySQLwindows

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

Best Answer

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 !!!

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!

MySQL – Master Binlog Corruption

Surprisingly, that's not gibberish.

That indeed appears at the top of binlogs whenever you do mysqlbinlog to a binary log generated using MySQL 5.1 and MySQL 5.5. You will not see that gibberish in binary logs for MySQL 5.0 and back.

This is why the start point for replication from an empty binary log is

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

This is good to remember if you do MySQL Replication where the Master if MySQL 5.1 and the slave is MySQL 5.0. This could present a really big headache.

Replication from Master using 5.0 and Slave using 5.1 works fine, not the other way around.(According to MySQL Documentation, it is generally not supported for 3 reasons: 1) Binary Log Format, 2) Row-based Replication, 3) SQL Incompatibility).

Anyway, do a mysqlbinlog on the offending binary log on the master. If the resulting dump produces gibberish in the middle of the dump (which I have seen a couple of times in my DBA career) you may have to skip to position 98 (MySQL 5.0) or 106 (MySQL 5.1) or 107 (MySQL 5.5) of the master's next binary log and start replicating from there (SOB :( you may need to use MAATKIT tools mk-table-checksum and mk-table-sync to reload master changes not on the slave [if you want to be a hero]; even worse, mysqldump the master and reload the slave and start replication totally over [if you don't want to be a hero])

If the mysqlbinlog of the master is completely readable after the top gibberish you saw, it is possible the master's binary log is fine but the relay log on the slave is corrupt (due to transmission/CRC errors). If that's the case, just reload the relay logs by issuing the CHANGE MASTER TO command as follows:

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='< master-host ip or DNS >',
MASTER_PORT=3306,
MASTER_USER='< usernmae >',
MASTER_PASSWORD='< password >',
MASTER_LOG_FILE='< MMMM >',
MASTER_LOG_POS=< PPPP >;
START SLAVE;

Where

MMMM is the last file used from the Master that was last processed on the Slave
PPPP is the last position used from the Master that was last processed on the Slave

You can get MMMM and PPPP by doing SHOW SLAVE STATUS\G and using

Relay_Master_Log_File for MMMM
Exec_Master_Log_Pos for PPPP

Try it out and let me know !!!

BTW running CHANGE MASTER TO command erases the slave's current relay logs and starts fresh.