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: | |
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
[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.