Bug #32397 Relayed data from master being written to DB, but not going to binlogs
Submitted: 14 Nov 2007 20:45 Modified: 30 Nov 2007 12:02
Reporter: Martin Houle Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.45 OS:Linux (CentOS4.5)
Assigned to: CPU Architecture:Any

[14 Nov 2007 20:45] Martin Houle
Description:
I have a dual-master replication setup, with multiple slaves replicating off of one of the 2 servers. 

Master1 (id1, offset 0, mysql v.5.0.27) and Master2(id2, offset 1 mysql v.5.0.27) are full sync, Slaves1 Slave2 & Slave3 (all slaves are v.5.0.45) are replicating from Master2. 2 existing tables and all new tables cause the same problem.

All commands I issue on Master2 will replicate to all slaves and the other master as well, but all commands executed on Master1 will make it to Master2, which will not write it to it's binlogs, causing all slaves to not get the informations. Even a simple insert:

[Master1] sql> INSERT INTO `martin10` ( `id` , `name` , `age` ) VALUES (NULL , 'ser342', '13');

[root@Master1 ~] mysqlbinlog binlog.00* |grep ser243
INSERT INTO `martin10` ( `id` , `name` , `age` ) VALUES (NULL , 'ser342', '13');

[root@Master2 ~] mysqlbinlog relaylog-file.00* |grep ser243
INSERT INTO `martin10` ( `id` , `name` , `age` ) VALUES (NULL , 'ser342', '13');

[root@Master2 ~] mysqlbinlog relaylog-file.00* |grep ser243
[root@Master2 ~]

I don't understand, it's a simple insert! All servers and replications have been restarted and nothing is wrong in the MASTER or SLAVE status outputs or in any of the error logs.

Help would be greatly appreciated.

Regards,

Martin H.
StreamTheWorld Senior Systems Administrator
www.streamtheworld.com

How to repeat:
sorry, can't repeat the problem. some other tables replicate fine. 

2 existing tables and all new tables cause the same problem.
[14 Nov 2007 20:49] Mats Kindahl
Could you please attach the my.cnf file for the master servers.
[14 Nov 2007 21:10] Martin Houle
configuration for Master1

Attachment: Master1.txt (text/plain), 1.53 KiB.

[14 Nov 2007 21:10] Martin Houle
Configuration for master2

Attachment: Master2.txt (text/plain), 1.45 KiB.

[14 Nov 2007 21:18] Martin Houle
I have made a mistake in typing my last log lookup on Master2, the INSERT query should be residing in binlog, not relaylog, so the command should be 

mysqlbinlog master2-binlog.00* |grep ser243

of course, doesn't find it there as explained, and so doesn't make it to the 3 slaves.
[14 Nov 2007 21:32] Mats Kindahl
You seem to be missing the log-slave-updates option, which means that the server does not "forward" statements coming through replication by writing them to the binary log. For more information, see http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
[15 Nov 2007 15:39] Martin Houle
log-slave-updates did the trick, but led me to another problem. I suppose I should open another discussion abour it? I will describe it here anyways:

On my Master2, I have 2 slaves, slave1 v5.0.27, slave2 v5.0.45, and they both get the relayed commands from Master2. Slave1 is in sync with no errors, but slave2 has a problem with one table, giving me this error in show slave status:

 Last_Errno: 1105
Last_Error: Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'streaming'. 
Query: 'INSERT INTO mount_infomusic (mountid, title) VALUES (42,'') ON 
DUPLICATE KEY UPDATE lasttitle2=lasttitle1, lasttitle1=title'

Even though the error describes a bug, I looked it up and read about it and I don't think that exactly what I experience here. Remember, my slave1 is v5.0.27 and works fine.

The table mount_infomusic, has an auto_increment id column, but so do other tables, and it was in sync until I added log-slave-updates on Master2 overnight.

Here's the table info on MASTER2:

CREATE TABLE `mount_infomusic` (
  `id` int(12) NOT NULL auto_increment,
  `mountid` int(12) NOT NULL default '0',
  `title` varchar(100) NOT NULL,
  `lasttitle1` varchar(100) NOT NULL,
  `lasttitle2` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `mountid` (`mountid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=282 ;

Here's the table info on SLAVE2:

CREATE TABLE `mount_infomusic` (
  `id` int(12) NOT NULL auto_increment,
  `mountid` int(12) NOT NULL default '0',
  `title` varchar(100) NOT NULL,
  `lasttitle1` varchar(100) NOT NULL,
  `lasttitle2` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `mountid` (`mountid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=282 ;

Seems that this is the only table (out of 45) that will get me the error 1105.
I appreciate your help very much, many thanks

Martin H.
[15 Nov 2007 18:17] Martin Houle
Actually, it's very much like Bug #20188, except that I seem to have some deletes that don't work either, and mysql versions on my servers are higher than the patched version for this Bug #20188, I'm still digging to find answers.

Thank you
Martin H.
[30 Nov 2007 12:02] Sveta Smirnova
Thank you for the feedback.

But this is not result of a bug. You should upgrade master to use it with 5.0.45 slave. Please read comment "[19 Mar 20:11] Paul DuBois" to bug #24432 for details.