MySQL Bugs: #54634: Alter table query fails and did not written to bin log

Bug #54634	Alter table query fails and did not written to bin log
Submitted:	19 Jun 2010 17:07	Modified:	21 Jun 2010 6:11
Reporter:	Devang Modi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	mysql Ver 14.12 Distrib 5.0.77	OS:	Linux (Linux (Linux Server release 5.4 (Tikanga)))
Assigned to:		CPU Architecture:	Any
Tags:	alter, bin, did, fails, log, not, query, table, to, written

Description:
Dear Supporter;

On one of our RHEL server , We are using MySQL with multiple databases instances.

This server is also acting as Master Replication server.

On this server we are having 1 database which contains all tables belongs to INNODB and 20 databases which contains all tables belongs to MyIsam.

Today we issued "ALTER TABLE" for adding a field on a table which is presented in all databases ( 1 + 20 ).

Surprisingly, on Master server it was worked perfectly but on slave servers it affected 1 database only. The affected database is that which is containing innodb tables only.

When I studied the case deeply I found that on Master server the bin log which is responsible for replication contains "Alter Table" queries for the first database only. I tried to reproduce the same case on other server also but same thing repeated.

With example the case you can understand in better way.

Master Server
1. Database with Innodb tables is "CVT"
2. Database with MyIsam tables is "CVTA0001" to "CVTA0020".
3. Applied query is "ALTER TABLE specificrouteloc001 ADD latlongactual VARCHAR( 30 ) NULL AFTER timeactual;" on cvt database.
4. Applied query is "ALTER TABLE specificrouteloc ADD latlongactual VARCHAR( 30 ) NULL AFTER timeactual;"
on cvta0001 to cvta0020 databases.

On master server every queries were executed perfectly OK.

But on replication server the 3. number query only applied and other will not.
I found that in bin log of master server only 3. number query registered and other are not.

For your reference I am attaching my.cnf of master server with this ticket.

Devang

How to repeat:
1. Activate bin log on a mysql server
2. Create two databases
3. Create a table with same structure in both databases
4. in database create table with myIsam and in another create same with innodb engine.
5. now issue "ALTER TABLE <table name> ADD <field name> VARCHAR( 30 ) NULL AFTER <field name>;" on both tables one after another.
6. after execution try to find same query in bin log

I did not find query belongs to MyIsam table but for innodb table it is there.

Devang

I do not see any my.cnf. Please, upload it.

My.cnf without ip, userid and password description

Attachment: master_my.cnf (application/octet-stream, text), 7.15 KiB.

Dear Friend;

Please see it with this.

Sorry, for the inconvenience .

Devang

You have this:

binlog-do-db = cvt

in my.cnf. Why do you expect any statements on other databases to be logged? Read http://dev.mysql.com/doc/refman/5.1/en/replication-rules-db-options.html.

I think this is NOT a bug.

Dear sir;

We are using this setup since a long back, we found that all other statements like Insert, Update and Delete are applied to other databases perfectly and without any error.

That is why I am doubting .

For example

Insert into cvta0001.t1 .... will be logged and replicated perfectly on all replication server.

only "Alter ..." was rejected ....

Devang

Dear Sir;

I have tried the same by change binlog-do-db option.

I set 

binlog-do-db=cvt
binlog-do-db=cvta0001

than

binlog-do-db=cvt;cvta0001

but I am not able solve the case.

Please guide me further in this regards.

Please also consider my previous comments on this.

Devang

Dear Sir;

Thanking you very much for the information regarding binlog-do-db,

I able to record "Alter ..." to binlog but still my question is still their

"Why master bin log rejected only ALTER statement and not insert,delete and update statement"

May be I am wrong somewhere please update me.

Devang

Let me ask a question: what is the default database (those set with USE explicitly or those you initially connect to) in case of ALTER and in case of INSERT etc? Maybe they are different? Check your code.

Dear Sir;

Thanks a lot for pointing 

I am able to understand the set up and you are perfectly right this is not "BUG"

Sorry for the reporting.

Please accept my Heartiest thanking you for the very well support.

Devang

Dear Sir;

Thanks a lot for pointing 

I am able to understand the set up and you are perfectly right this is not "BUG"

Sorry for the reporting but 

Heartiest thanking you for the very well support.

Devang