Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Very interesting. Waiting for update. On Jun 15, 2011 4:51 AM, Hank hes...@gmail.com wrote: The slave is receiving null as the statement based insert, not an out of range number from the master. I've been doing more research all day on this bug and have a bit more information as to what's causing it. I plan to write it up tomorrow and post it. Basically, everything works perfectly, until I add a replication-ignore-table=xxx statement in my.cnf where xxx is a different table with a unique id INT auto-increment as the single primary key And then the values being inserted into the test table (above, not ignored) represent the last-insert-id of the replication *ignored* table on the slave Yeah, pretty strange, I know. But totally repeatable. -Hank 2011/6/14 Halász Sándor h...@tbbs.net 2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
You should also have a look at the slave relay log. But in any case sounds like a bug. Claudio On Jun 14, 2011 5:18 AM, Hank hes...@gmail.com wrote: Both my master and slave bin logs look OK (I think).. master bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; slave bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; -Hank On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote: Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.com wrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 |
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
That is the slave relay log dump I posted (and mis-labeled). Thanks. -Hank On Tue, Jun 14, 2011 at 2:34 AM, Claudio Nanni claudio.na...@gmail.comwrote: You should also have a look at the slave relay log. But in any case sounds like a bug. Claudio On Jun 14, 2011 5:18 AM, Hank hes...@gmail.com wrote: Both my master and slave bin logs look OK (I think).. master bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; slave relay log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; -Hank On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote: Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
The slave is receiving null as the statement based insert, not an out of range number from the master. I've been doing more research all day on this bug and have a bit more information as to what's causing it. I plan to write it up tomorrow and post it. Basically, everything works perfectly, until I add a replication-ignore-table=xxx statement in my.cnf where xxx is a different table with a unique id INT auto-increment as the single primary key And then the values being inserted into the test table (above, not ignored) represent the last-insert-id of the replication *ignored* table on the slave Yeah, pretty strange, I know. But totally repeatable. -Hank 2011/6/14 Halász Sándor h...@tbbs.net 2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | NO | PRI | NULL|| | cnt | int(11) | NO | PRI | NULL| auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | NO | PRI | NULL|| | cnt | int(11) | NO | PRI | NULL| auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt| +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt| +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master desc test; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL|| | cnt | smallint(5) unsigned | NO | PRI | NULL| auto_increment | +---+--+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! ++-+ slave select * from test; +++ | id | cnt| +++ | 1 | 65535 | should be 1 +++ but this is different: master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! | 1 | 2 | correct! ++-+ slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, missing second record, too ++---+ slave show slave status; Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test values (1,null)' .. at which point I have to restart the slave due to the error: slave SET GLOBAL sql_slave_skip_counter=1; slave start; slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, still missing second record, too (of course) ++---+ Now if I manually replicate the statements just on the slave - it works perfectly: slave: truncate table test; slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | ++-+ slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | ++-+ So something in the replication code is munging the 'null' into some random value and trying to insert it. Seems strange that direct statements would work, but replicated statements do not. Nothing really changed on my system, but for some reason, this all started happening about a week or so ago. I've been running this 5.5.8/5.5.11 configuration for months now (since 5.5.8 was released).The PHP code that does this hasn't changed one bit, and this is a simplified version of the database and code that is running in production. Additional note: If I drop the 'id' field, and the primary key is just the auto-increment field, it works correctly in replication. Any ideas? Can anyone else replicate
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master desc test; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment | +---+--+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 65535 | should be 1 +++ but this is different: master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! | 1 | 2 | correct! ++-+ slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, missing second record, too ++---+ slave show slave status; Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test values (1,null)' .. at which point I have to restart the slave due to the error: slave SET GLOBAL sql_slave_skip_counter=1; slave start; slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, still missing second record, too (of course) ++---+ Now if I manually replicate the statements just on the slave - it works perfectly: slave: truncate table test; slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | ++-+ slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | ++-+ So something in the replication code is munging the 'null' into some random value and trying to insert it. Seems strange that direct statements would work, but replicated statements do not. Nothing really changed on my system, but for some reason, this all started happening about a week or so ago. I've been running this 5.5.8/5.5.11 configuration for months now (since 5.5.8 was released). The PHP code that does this hasn't changed one bit, and this is a simplified version of the database and code that is running in production. Additional note: If I drop the
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master desc test; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment | +---+--+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 65535 | should be 1 +++ but this is different: master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! | 1 | 2 | correct! ++-+ slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, missing second record, too ++---+ slave show slave status; Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test values (1,null)' .. at which point I have to restart the slave due to the error: slave SET GLOBAL sql_slave_skip_counter=1; slave start; slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, still missing second record, too (of course) ++---+ Now if I manually replicate the statements just on the slave - it works perfectly: slave: truncate table test; slave: insert into test values (1,null);
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Both my master and slave bin logs look OK (I think).. master bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; slave bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; -Hank On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote: Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int
Re: Replication bug?
Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote: We are trying to put a monitoring solution in place at a client and have come up against something during testing. If the replication user disappears off the master and the slave cannot log in, the Slave_IO_Thread still shows running and no error in the last error number field. Does anybody know if this is intentional? I can't find any references to this in the doco. We will check. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication bug?
Yes, I confirm, it's a bug. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication bug?
Hi Folks, We are trying to put a monitoring solution in place at a client and have come up against something during testing. If the replication user disappears off the master and the slave cannot log in, the Slave_IO_Thread still shows running and no error in the last error number field. Does anybody know if this is intentional? I can't find any references to this in the doco. This is MySQL 4.0.20 and RH Advanced Server 2.1 After deleting the user on the master, the following message appears in the log but the slave status shows a healthy relationship. 40902 10:54:13 Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3307': Error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' errno: 1045 mysql show slave status \G *** 1. row ** Master_Host: hpim202-98.aus.hp.com Master_User: repl Master_Port: 3307 Connect_retry: 60 Master_Log_File: hpim202-98-bin.001 Read_Master_Log_Pos: 913879 Relay_Log_File: MAU023W-relay-bin.010 Relay_Log_Pos: 305 Relay_Master_Log_File: hpim202-98-bin.001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 913879 Relay_log_space: 301 1 row in set (0.00 sec) Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax
RE: Replication bug?
David, I haven't ever attempted to delete the slave user on the master, and since I only run replication on 4.1 boxes and not 4.0 boxes, I won't be able to help much. But I would probably submit it to http://bugs.mysql.com and they can verify that it is a bug. But they will probably not recommend deleting the slave user again. :) Donny -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 8:53 PM To: MySQL List Subject: Replication bug? Hi Folks, We are trying to put a monitoring solution in place at a client and have come up against something during testing. If the replication user disappears off the master and the slave cannot log in, the Slave_IO_Thread still shows running and no error in the last error number field. Does anybody know if this is intentional? I can't find any references to this in the doco. This is MySQL 4.0.20 and RH Advanced Server 2.1 After deleting the user on the master, the following message appears in the log but the slave status shows a healthy relationship. 40902 10:54:13 Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3307': Error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' errno: 1045 mysql show slave status \G *** 1. row ** Master_Host: hpim202-98.aus.hp.com Master_User: repl Master_Port: 3307 Connect_retry: 60 Master_Log_File: hpim202-98-bin.001 Read_Master_Log_Pos: 913879 Relay_Log_File: MAU023W-relay-bin.010 Relay_Log_Pos: 305 Relay_Master_Log_File: hpim202-98-bin.001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 913879 Relay_log_space: 301 1 row in set (0.00 sec) Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heap table in replication,bug?
MaFai [EMAIL PROTECTED] wrote: Hello, Victoria Reznichenko, The p_showing table,the following is the create table sql statement. | p_showing | CREATE TABLE `p_showing` ( `showing_timestamp` timestamp(14) NOT NULL, `showing_channel_name` varchar(50) NOT NULL default '', `showing_asset_name` varchar(50) NOT NULL default '', `showing_start_time` datetime default NULL, `showing_keywords` varchar(100) default NULL, `showing_ip` varchar(15) NOT NULL default '', `showing_port` varchar(10) NOT NULL default '', `showing_end_time` datetime default NULL, `showing_resource` varchar(20) NOT NULL default '', `showing_status` varchar(20) NOT NULL default '', `showing_AutoDelte` varchar(5) NOT NULL default '', `showing_Feed` varchar(20) NOT NULL default '', `showing_Interactive_Control` varchar(5) NOT NULL default '', `showing_start_time_ctime` datetime default NULL, `showing_end_time_ctime` datetime default NULL, `showing_URL` varchar(100) NOT NULL default '', `source_ip` varchar(15) default NULL ) TYPE=HEAP | Mysql Version mysqladmin Ver 8.40 Distrib 4.0.12, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.12-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 day 5 hours 9 min 2 sec Threads: 4 Questions: 1005973 Slow queries: 22 Opens: 102 Flush tables: 1 Open tables: 78 Queries per second avg: 9.586 I tested using your table structure and replication worked well. Please, upgrade MySQL server to 4.0.16 and let me know if the problem still exists. === At 2003-11-03, 14:51:00 you wrote: === MaFai [EMAIL PROTECTED] wrote: Hello, mysql, The replication running smoothly between the master and slave,except that the heap table can not be synchronized. While the master insert the record into the heap table,the slave would do the same job. While the master delete the record in the heap table,the slave wouldn't do so. After serval days passed,the heap table would overloading in the slave,because it never delete the record in heap table. The mysql seems doesn't support heap table in replication,right? If yes,i should change the table type to innodb,although it would lose the performance. The manaul doesn't mention this. Any idea apprecated. Env:Mysql 4.0,Linux Red Hat7,256mb ram. What exactly version of MySQL do you use? I tested replication with HEAP tables and all worked like a charm. Could you provide a test case? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heap table in replication,bug?
MaFai [EMAIL PROTECTED] wrote: Hello, mysql, The replication running smoothly between the master and slave,except that the heap table can not be synchronized. While the master insert the record into the heap table,the slave would do the same job. While the master delete the record in the heap table,the slave wouldn't do so. After serval days passed,the heap table would overloading in the slave,because it never delete the record in heap table. The mysql seems doesn't support heap table in replication,right? If yes,i should change the table type to innodb,although it would lose the performance. The manaul doesn't mention this. Any idea apprecated. Env:Mysql 4.0,Linux Red Hat7,256mb ram. What exactly version of MySQL do you use? I tested replication with HEAP tables and all worked like a charm. Could you provide a test case? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Heap table in replication,bug?
Hello, Victoria Reznichenko, The p_showing table,the following is the create table sql statement. | p_showing | CREATE TABLE `p_showing` ( `showing_timestamp` timestamp(14) NOT NULL, `showing_channel_name` varchar(50) NOT NULL default '', `showing_asset_name` varchar(50) NOT NULL default '', `showing_start_time` datetime default NULL, `showing_keywords` varchar(100) default NULL, `showing_ip` varchar(15) NOT NULL default '', `showing_port` varchar(10) NOT NULL default '', `showing_end_time` datetime default NULL, `showing_resource` varchar(20) NOT NULL default '', `showing_status` varchar(20) NOT NULL default '', `showing_AutoDelte` varchar(5) NOT NULL default '', `showing_Feed` varchar(20) NOT NULL default '', `showing_Interactive_Control` varchar(5) NOT NULL default '', `showing_start_time_ctime` datetime default NULL, `showing_end_time_ctime` datetime default NULL, `showing_URL` varchar(100) NOT NULL default '', `source_ip` varchar(15) default NULL ) TYPE=HEAP | Mysql Version mysqladmin Ver 8.40 Distrib 4.0.12, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.12-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 day 5 hours 9 min 2 sec Threads: 4 Questions: 1005973 Slow queries: 22 Opens: 102 Flush tables: 1 Open tables: 78 Queries per second avg: 9.586 Thank u in advance === At 2003-11-03, 14:51:00 you wrote: === MaFai [EMAIL PROTECTED] wrote: Hello, mysql, The replication running smoothly between the master and slave,except that the heap table can not be synchronized. While the master insert the record into the heap table,the slave would do the same job. While the master delete the record in the heap table,the slave wouldn't do so. After serval days passed,the heap table would overloading in the slave,because it never delete the record in heap table. The mysql seems doesn't support heap table in replication,right? If yes,i should change the table type to innodb,although it would lose the performance. The manaul doesn't mention this. Any idea apprecated. Env:Mysql 4.0,Linux Red Hat7,256mb ram. What exactly version of MySQL do you use? I tested replication with HEAP tables and all worked like a charm. Could you provide a test case? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2003-11-04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Heap table in replication,bug?
Hello, mysql, The replication running smoothly between the master and slave,except that the heap table can not be synchronized. While the master insert the record into the heap table,the slave would do the same job. While the master delete the record in the heap table,the slave wouldn't do so. After serval days passed,the heap table would overloading in the slave,because it never delete the record in heap table. The mysql seems doesn't support heap table in replication,right? If yes,i should change the table type to innodb,although it would lose the performance. The manaul doesn't mention this. Any idea apprecated. Env:Mysql 4.0,Linux Red Hat7,256mb ram. Best regards. MaFai [EMAIL PROTECTED] 2003-11-03 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication BUG
I have 2 servers: one asd master, second as a slave 1) I start master 2) start slave 3) stop slave 4)start slave 5)stop slave 6)start slave and i have errors as below. C:\mysql4\binmysqld-max-nt --defaults-file=../my_slave.cnf --standalone --c onso le 030909 18:23:19 InnoDB: Started 030909 18:23:19 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3300', r eplication started in log 'hq-bin.001' at position 79 030909 18:23:19 Slave SQL thread initialized, starting replication in log 'hq-b in.001' at position 79, relay log '.\hq-relay-bin.002' position: 41 030909 18:23:19 next log error: -1 offset: 19 log: 030909 18:23:19 Error reading relay log event: Error purging processed log 030909 18:23:19 Could not parse relay log event entry. The possible reasons are : the master's binary log is corrupted (you can check this by running 'mysqlbinl og' on the binary log), the slave's relay log is corrupted (you can check this b y running 'mysqlbinlog' on the relay log), a network problem, or a bug in the ma ster's or slave's MySQL code. If you want to check the master's binary log or sl ave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STA TUS' on this slave. 030909 18:23:19 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'hq-bin. 001' position 79 mysqld-max-nt: ready for connections. Version: '4.0.14-max-nt' socket: '' port: 3301 # --- C:\mysql4\binmysqld-max-nt --defaults-file=../my_slave.cnf --standalone --c onso le 030909 18:25:22 InnoDB: Started 030909 18:25:22 Failed to open the relay log (relay_log_name='.\hq-relay-bin.00 2', relay_log_pos=41 030909 18:25:22 Could not find first log during relay log initialization 030909 18:25:22 Failed to initialize the master info structure mysqld-max-nt: ready for connections. Version: '4.0.14-max-nt' socket: '' port: 3301 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3.23.56 Replication Bug
I know the replication method is different in MySQL 4.0 then MySQL
3.23.x, but I have a bug that causes problems. The following query will
cause MySQL's logic to not properly read any of the following my.cnf
commands on slave servers:
replicate-wild-do-table
replicate-wild-ignore-table
replicate-ignore-table
I am sure it would make any of the match commands fail as well. This
query caused replication to stop on 2 of my slaves because it was in a
database that was not made to be replicated on the slave.
Below is the query:
UPDATE phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5''
Below is the error log entry:
030803 16:24:33 Slave: connected to master
'[EMAIL PROTECTED]:3306', replication started in log
'blue-bin.013' at position 72475077
030803 16:24:33 Slave: did not get the expected error running query
from master - expected: 'Duplicate entry '%-.64s' for key %d' (1062),
got
'no error' (0)
030803 16:24:33 Slave: error running query 'UPDATE
phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5''
030803 16:24:33 Error running query, slave aborted. Fix the problem,
and re-start the slave thread with mysqladmin start-slave. We stopped
a
t log 'blue-bin.013' position 72475077
030803 16:24:33 Slave thread exiting, replication stopped in log
'blue-bin.013' at position 72475077
Below is the binlog entry, keep in mind, it will ignore all of the
queries after this one, it will just not ignore a query with those
special characters in it:
# at 72475077
#030803 0:35:26 server id 1 Query thread_id=191959
exec_time=0 error_code=1062
use bluecustforum;
SET TIMESTAMP=1059888926;
UPDATE phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5';
# at 72475467
#030803 0:35:29 server id 1 Query thread_id=191960
exec_time=0 error_code=0
SET TIMESTAMP=1059888929;
UPDATE phpbb_topics
SET topic_views = topic_views + 1
WHERE topic_id = 18;
# at 72475580
#030803 0:35:34 server id 1 Query thread_id=191961
exec_time=0 error_code=0
SET TIMESTAMP=1059888934;
UPDATE phpbb_sessions
SET session_time =
1059888934, session_page = 9
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5';
# at 72475755
#030803 0:35:34 server id 1 Query thread_id=191961
exec_time=0 error_code=0
SET TIMESTAMP=1059888934;
UPDATE phpbb_users
SET
user_session_time = 1059888934, user_session_page = 9
WHERE user_id =
8;
Below is the version info from mysqlbug
VERSION=3.23.56
COMPILATION_COMMENT=Official MySQL RPM
BUGmysql=[EMAIL PROTECTED]
# This is set by configure
COMP_ENV_INFO=CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -feli
de-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
CONFIGURE_LINE=./configure '--disable-shared'
'--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static'
'--without-berkeley-db'
'--without-innodb' '--enable-assembler' '--enable-local-infile'
'--with-mysqld-user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock'
'--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share'
'--locals
tatedir=/var/lib/mysql' '--infodir=/usr/share/info'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--with-comment=Official MySQL RPM' '
CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6
-fno-omit-frame-pointer -felide-constructors
-fno-exceptions
-fno-rtti -mpentium' 'CXX=gcc'
nickg
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: replication bug? - replace into db.table being recorded in the
On Tuesday 04 March 2003 22:34, Andrew Braithwaite wrote: This is quite an involved one... Using MySQL 4.0.11 on linux I have two logical db's on the same machine, lets say db1 and db2. I have perl apps doing the following: replace into db2.tablename . In my.cnf I have the line binlog-do-db= db1 The queries are being performed OK on db2, but they're being included in the replication bin-log. I also have inserts in the similar form of insert into db2.tablename . that work fine and don't show up in the same bin-log! Any ideas? Is this a bug? No, it's not a bug. From the manual: Tells the master that it should log updates to the binary log if the current (i.e. selected) database is 'database_name'. All others databases which are not explicitly mentioned are ignored. Note that if you use this you should ensure that you only do updates in the current database. So, if your current database is db1 and you do REPLACE on db2 this command is also written to the binary logs. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replication bug? - replace into db.table being recorded in the wrong bin-log....
Hi all, This is quite an involved one... Using MySQL 4.0.11 on linux I have two logical db's on the same machine, lets say db1 and db2. I have perl apps doing the following: replace into db2.tablename . In my.cnf I have the line binlog-do-db= db1 The queries are being performed OK on db2, but they're being included in the replication bin-log. I also have inserts in the similar form of insert into db2.tablename . that work fine and don't show up in the same bin-log! Any ideas? Is this a bug? Cheers, Andrew Sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication bug?
On Mon, Jan 13, 2003 at 09:41:12AM +1000, Jason Brooke wrote: No, I've been ignored on this problem for 18 months now, for some reason. Quite peculiar. The limitations of replicate-do-db are documented in: http://www.mysql.com/doc/en/Replication_Options.html I found out about this limitation the hard way, just like you seem to have done. Since we don't control the scripts that use the database, we couldn't use replicate-do-db. It just takes too much time to fix the problems when someone forgets about this limitation. Regards, Fred. - Original Message - From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED] To: 'Jason Brooke' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 9:16 AM Subject: RE: Replication bug? Did you ever get any confirmation that it will be added to the official bug list? -Original Message- From: Jason Brooke [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 12, 2003 3:14 AM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED] Subject: Re: Replication bug? Yes this is the same issue I've reported previously. Unless literally 'select' the database, the query is never written to the binary log. - Original Message - From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 12, 2003 12:38 PM Subject: Replication bug? I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication bug?
I don't think I have anything that should cause this. Here is my my.ini from the the slave. The tables that are being excluded are not listed. [mysqld] basedir=C:/mysql datadir=C:/mysql/data set-variable=max_allowed_packet=16M log-slave-updates log-bin # Replication variables master-host=x.x.x.x master-user=sasassas master-password=x master-port=3306 server-id=2 # Exclude some tables that we don't want here! replicate-wild-ignore-table=ra_scanner.system replicate-wild-ignore-table=ra_scanner.local_scan_log - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication bug?
Yes this is the same issue I've reported previously. Unless literally 'select' the database, the query is never written to the binary log. - Original Message - From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 12, 2003 12:38 PM Subject: Replication bug? I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication bug?
Yes, the slaves are doing the replication. (didn't know you could set it up any other way) As far as I am concerned this is a BIG bug. Anything that happens on the master should replicate to the slaves. Any chance this could get fixed in the next release? -Original Message- From: Frederick R. Doncillo [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 11, 2003 10:58 PM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED] Subject: Re: Replication bug? Are the slaves doing the replication process? If not, you may try it that way. Slaves should do the updating and must request from the server and not the server to the slave. :-) Fred. Ross Davis - DataAnywhere.net wrote: I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication bug?
No, I've been ignored on this problem for 18 months now, for some reason. Quite peculiar. - Original Message - From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED] To: 'Jason Brooke' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 9:16 AM Subject: RE: Replication bug? Did you ever get any confirmation that it will be added to the official bug list? -Original Message- From: Jason Brooke [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 12, 2003 3:14 AM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED] Subject: Re: Replication bug? Yes this is the same issue I've reported previously. Unless literally 'select' the database, the query is never written to the binary log. - Original Message - From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 12, 2003 12:38 PM Subject: Replication bug? I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication bug?
I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication bug?
Are the slaves doing the replication process? If not, you may try it that way. Slaves should do the updating and must request from the server and not the server to the slave. :-) Fred. Ross Davis - DataAnywhere.net wrote: I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication Bug
This may perhaps be an old bug and already fixed entry in my.cnf replicate-do-db=qbslive Slave stopped replicating at some time ( not in logs ) I tried to start it back up and it would not budge so I checked the logs 020530 9:55:15 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'QBSDB251-bin.009' at position 103512 ERROR: 1133 Can't find any matching row in the user table 020530 9:55:15 Slave: error running query 'GRANT Insert, Update, Select, Delete ON qbslive.* TO 'Remote'@'edited for security' IDENTIFIED BY 'edited for security' 020530 9:55:15 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave - log 'QBSDB251-bin.009' position 103512 020530 9:55:15 Slave thread exiting, replication stopped in log 'QBSDB251-bin.009' at position 103512 This is quite alarming!!! Trying to copy my GRANT's from the master server!! I restarted the slave mysql server and replication started up again MySQL support: email support Synopsis: Replication bug, trying to grab GRANTS from master Severity: not sure Priority: probably low Category: mysql Class: sw-bug Release: mysql-3.23.32 (Official MySQL RPM) Environment: machine, os, target, libraries (multiple lines) System: Linux QBSCOM250.911rush.com 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='egcs' CFLAGS='-O6 -fomit-frame-pointer -mpentium' CXX='egcs' CXXFLAGS='-O6 -fomit-frame-pointer -felide-constructor\ s -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Feb 21 03:23 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1282588 Sep 4 2001 /lib/libc-2.2.4.so -rw-r--r--1 root root 27304836 Sep 4 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 4 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --enable-assembler --with-mysqld-user=my\ sql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --\ datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --without-berkeley-db '--with-comment=Offic\ ial MySQL RPM' I don't think I can provide much else that will point in the right direction - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.2 Replication Bug...
Hi! Sasha == Sasha Pachev [EMAIL PROTECTED] writes: Sasha On Thursday 07 March 2002 12:42 am, Jeremy Zawodny wrote: My 4.0.2 slave has run through about 14 million queries and it's going well. Sasha Good news Do you have any feel for how much slower a debugging version of MySQL is compared to a normal version? ?I ask because my replication heartbeat monitor has noticed this particular slave falling behind on replication a fair amount. ?It usually doesn't get too far behind, but it's certainly farther behind that it's neighbor--which has a slower CPU. Sasha Depends on the queries. I would say it could be from 3 to 10 times slower. To Sasha know for sure, use BUILD/compile-pentium instead of Sasha BUILD/compile-pentium-debug. This will compile a perfectly optimal binary. If you are using --skip-safemalloc or just configure with --with-debug instead of --with-debug=full, the speed difference should not be larger than 15-35 % (This is from our manual). If you start mysqld with the --debug option, then things will be 3-10 times slower. cut Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.2 Replication Bug...
On Thursday 07 March 2002 12:42 am, Jeremy Zawodny wrote: My 4.0.2 slave has run through about 14 million queries and it's going well. Good news Do you have any feel for how much slower a debugging version of MySQL is compared to a normal version? ?I ask because my replication heartbeat monitor has noticed this particular slave falling behind on replication a fair amount. ?It usually doesn't get too far behind, but it's certainly farther behind that it's neighbor--which has a slower CPU. Depends on the queries. I would say it could be from 3 to 10 times slower. To know for sure, use BUILD/compile-pentium instead of BUILD/compile-pentium-debug. This will compile a perfectly optimal binary. And could that fact have anything to do with SHOW SLAVE STATUS being slow sometimes? I've figured out what's up with this one, I think. SHOW SLAVE STATUS needs to acquire a simultaneous lock on the I/O thread and on the SQL thread, and it might be a while before both of them will surrender that lock if they are busy. I have been rather conservative and very possibly made the critical region wider that what it should be. I think part of the problem is also in the FreeBSD mutex implementation - the current holder of the mutex surrenders it for only a very short time and it is possible that there could be some starvation issues. I woder if FreeBSD has different mutex types - in this case, we want the kind that puts fairness above speed. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.2 Replication Bug...
On Thu, Mar 07, 2002 at 09:57:54AM -0700, Sasha Pachev wrote: On Thursday 07 March 2002 12:42 am, Jeremy Zawodny wrote: Do you have any feel for how much slower a debugging version of MySQL is compared to a normal version? ?I ask because my replication heartbeat monitor has noticed this particular slave falling behind on replication a fair amount. ?It usually doesn't get too far behind, but it's certainly farther behind that it's neighbor--which has a slower CPU. Depends on the queries. I would say it could be from 3 to 10 times slower. To know for sure, use BUILD/compile-pentium instead of BUILD/compile-pentium-debug. This will compile a perfectly optimal binary. Okay, it's not a big deal. I was going to just run the debug binary in case things go wrong and you need more detail. And could that fact have anything to do with SHOW SLAVE STATUS being slow sometimes? I've figured out what's up with this one, I think. SHOW SLAVE STATUS needs to acquire a simultaneous lock on the I/O thread and on the SQL thread, and it might be a while before both of them will surrender that lock if they are busy. I have been rather conservative and very possibly made the critical region wider that what it should be. Ah, makes sense. Better safe than sorry. :-) I think part of the problem is also in the FreeBSD mutex implementation - the current holder of the mutex surrenders it for only a very short time and it is possible that there could be some starvation issues. I woder if FreeBSD has different mutex types - in this case, we want the kind that puts fairness above speed. Right. I'll ask some of the FreeBSD folks here when I get a chance. Thanks! Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 28 days, processed 989,034,558 queries (407/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.2 Replication Bug...
On Tue, Mar 05, 2002 at 12:13:25PM -0800, Jeremy Zawodny wrote: On Tue, Mar 05, 2002 at 09:19:35AM -0700, Sasha Pachev wrote: On Tuesday 05 March 2002 01:17 am, Jeremy Zawodny wrote: When the machine is pounding away on updates (over 300/sec), it can take a long time to get a response to SHOW SLAVE STATUS. ?I get one, but it can take between 5 and 30 seconds: My first inclination was to blame FreeBSD threads, but then I decided I need to gather some more hard evidence before I could do it. Is SHOW SLAVE STATUS the only command that is that slow when this happens? Yes. Try SHOW PROCESSLIST, SHOW VARIABLES, SHOW STATUS, SHOW MASTER STATUS. Tried all of them and they're fast. Just an update for you, Sasha. My 4.0.2 slave has run through about 14 million queries and it's going well. Do you have any feel for how much slower a debugging version of MySQL is compared to a normal version? I ask because my replication heartbeat monitor has noticed this particular slave falling behind on replication a fair amount. It usually doesn't get too far behind, but it's certainly farther behind that it's neighbor--which has a slower CPU. And could that fact have anything to do with SHOW SLAVE STATUS being slow sometimes? Thanks! Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 27 days, processed 977,270,881 queries (407/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.2 Replication Bug...
On Sat, Mar 02, 2002 at 09:57:58PM -0800, Jeremy Zawodny wrote: Murphy's law strikes! Just a few hours ago I blasted the relay logs on my 4.0.2 slave. It ran out of disk space! I'll rsync the slave and build a fresh MySQL from the bitkeeper tree and let you know. Sasha, I re-synced my 4.x slave and updated with the latest BitKeeper code and compile-pentium-debug. It is chugging along well now, but I've noticed something odd. When the machine is pounding away on updates (over 300/sec), it can take a long time to get a response to SHOW SLAVE STATUS. I get one, but it can take between 5 and 30 seconds: ---snip--- mysql show slave status \G *** 1. row *** Master_Host: db.finance.yahoo.com Master_User: repl Master_Port: 3306 Connect_retry: 15 Master_Log_File: binary-log.042 Read_Master_Log_Pos: 211593316 Relay_Log_File: db3-relay-bin.001 Relay_Log_Pos: 288092868 Relay_Master_Log_File: binary-log.042 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 211155824 1 row in set (24.79 sec) ---snip--- Any ideas? This is on FreeBSD 4.3 if that matters. Thanks, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 867,644,812 queries (389/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.2 Replication Bug...
On Tuesday 05 March 2002 01:17 am, Jeremy Zawodny wrote: When the machine is pounding away on updates (over 300/sec), it can take a long time to get a response to SHOW SLAVE STATUS. ?I get one, but it can take between 5 and 30 seconds: My first inclination was to blame FreeBSD threads, but then I decided I need to gather some more hard evidence before I could do it. Is SHOW SLAVE STATUS the only command that is that slow when this happens? Try SHOW PROCESSLIST, SHOW VARIABLES, SHOW STATUS, SHOW MASTER STATUS. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.2 Replication Bug...
On Tue, Mar 05, 2002 at 09:19:35AM -0700, Sasha Pachev wrote: On Tuesday 05 March 2002 01:17 am, Jeremy Zawodny wrote: When the machine is pounding away on updates (over 300/sec), it can take a long time to get a response to SHOW SLAVE STATUS. ?I get one, but it can take between 5 and 30 seconds: My first inclination was to blame FreeBSD threads, but then I decided I need to gather some more hard evidence before I could do it. Is SHOW SLAVE STATUS the only command that is that slow when this happens? Yes. Try SHOW PROCESSLIST, SHOW VARIABLES, SHOW STATUS, SHOW MASTER STATUS. Tried all of them and they're fast. In other news, the server hit another duplicate key problem. This time I was able to do this: slave stop; set sql_slave_skip_counter = 1; slave start; And it worked! So the new code seems to be better in that respect. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 26 days, processed 894,010,106 queries (394/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.2 Replication Bug...
On Monday 11 February 2002 01:01 pm, Jeremy Zawodny wrote: Okay, I've hit a bug. ?It happened after the slave had replicated about 5,397,000 queries. Jeremy: I have finally gotten around to this and I think I've found the bug. At least, on a different system where I could repeat it before my fix, I cannot anymore. I have pushed my changes into the public tree. So everything should work nicely ( until you find another bug, of course :)). -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.2 Replication Bug...
On Sat, Mar 02, 2002 at 10:32:39PM -0700, Sasha Pachev wrote: On Monday 11 February 2002 01:01 pm, Jeremy Zawodny wrote: Okay, I've hit a bug. ?It happened after the slave had replicated about 5,397,000 queries. Jeremy: I have finally gotten around to this and I think I've found the bug. At least, on a different system where I could repeat it before my fix, I cannot anymore. I have pushed my changes into the public tree. So everything should work nicely ( until you find another bug, of course :)). Murphy's law strikes! Just a few hours ago I blasted the relay logs on my 4.0.2 slave. It ran out of disk space! I'll rsync the slave and build a fresh MySQL from the bitkeeper tree and let you know. Thanks! Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 23 days, processed 769,725,515 queries (376/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
4.0.2 Replication Bug...
On Sat, Feb 09, 2002 at 09:41:25PM -0700, Sasha Pachev wrote: * Monitor your slave to make sure it does not crash ( watch error log for stack trace messages), slave keeps running ( check with SHOW SLAVE STATUS), and data is consistent. * If there are problems, I will need the following: - error logs on the master and on the slave - binary logs on the master, binary logs on the slave, and relay logs on the slave ( by default, placed in datadir and called `hostname`-relay-bin.* ) - output of SHOW SLAVE STATUS - version of the master Okay, I've hit a bug. It happened after the slave had replicated about 5,397,000 queries. I have a 4.0.2 slave runinng on FreeBSD (built from bitkeeper). It's master is a 3.23.47-max on Linux. The slave hit a duplicate key error and died. The IO thread appears to still be running, but the SQL thread is not. When I try to do a SLAVE START on the slave, the command never returns to the mysql prompt. The master error log has nothing in it. The last error was written days ago. The slave's status looks like this: ---snip--- mysql show slave status \G *** 1. row *** Master_Host: db.finance.yahoo.com Master_User: repl Master_Port: 3306 Connect_retry: 15 Master_Log_File: binary-log.015 Read_Master_Log_Pos: 375301751 Relay_Log_File: db3-relay-bin.002 Relay_Log_Pos: 84505219 Relay_Master_Log_File: binary-log.015 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 1062 Last_error: error 'Duplicate entry 'AAI' for key 1' on query ' INSERT INTO SymbolDirty SELECT DISTINCT Symbol,0 FROM udtable WHERE Date=20020208 AND Pubdate = 2002021101 ' Skip_counter: 0 Exec_master_log_pos: 51217892 1 row in set (0.00 sec) ---snip--- The slave's relay log (db3-relay-bin.002) is about 400MB in size and still growing, so that thread is alive and kicking. Strangely, the db3-relay-bin.001 file is no longer around. If you want the whole log, I can zip it up and upload it. Just let me know what you need. Here are the relevant bits of the slave's error log. ---snip--- /home/mysql-4-bk/libexec/mysqld: ready for connections 020210 16:48:26 Slave SQL thread initialized, starting replication in log 'FIRST' at position 0,relay log: name='./db3-relay-bin.001',pos='4' 020210 16:48:26 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'binary-log.013' at position 538131481 ERROR: 1062 Duplicate entry 'INMX' for key 1 020211 4:16:21 Slave: error 'Duplicate entry 'INMX' for key 1' on query ' INSERT INTO SymbolDirty SELECT DISTINCT Symbol,0 FROM udtable WHERE Date=20020208 AND Pubdate = 2002021101 ', error_code=1062 020211 4:16:21 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'binary-log.015' position 51217892 020211 4:16:21 Slave SQL thread exiting, replication stopped in log 'binary-log.015' at position 51217892 020211 11:42:45 Slave SQL thread initialized, starting replication in log 'binary-log.015' at position 51217892,relay log: name='./db3-relay-bin.002',pos='84505219' ERROR: 1062 Duplicate entry 'AAI' for key 1 020211 11:42:45 Slave: error 'Duplicate entry 'AAI' for key 1' on query ' INSERT INTO SymbolDirty SELECT DISTINCT Symbol,0 FROM udtable WHERE Date=20020208 AND Pubdate = 2002021101 ', error_code=1062 020211 11:42:45 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'binary-log.015' position 51217892 020211 11:42:45 Slave SQL thread exiting, replication stopped in log 'binary-log.015' at position 51217892 ---snip--- Thanks, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 4 days, processed 153,526,506 queries (419/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED]
Re: 4.0.2 Replication Bug...
On Monday 11 February 2002 12:55 pm, Jeremy Zawodny wrote: The slave hit a duplicate key error and died. ?The IO thread appears to still be running, but the SQL thread is not. ?When I try to do a SLAVE START on the slave, the command never returns to the mysql prompt. Jeremy: First, do SHOW PROCESSLIST. Then I have a dilema - on one hand I want to see if SLAVE STOP/SLAVE START will get it going, but on the other hand, I want to see the core from the time when slave start got stuck, and we cannot do both at the same time :-) So let's try SLAVE STOP/SLAVE START, and if that does not help, kill mysqld with signal 6 ( SIGABRT), find the core file in datadir, and FTP the core and the binary to ftp://support.mysql.com/pub/mysql/secret In the mean time, I will check the code to see if I can find a bug. Your help is very much appreciated. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
REPLICATION BUG
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: REPLICATION BUG Description: The bug manifests itself in the following situation. A temporary table has been created on the master server. A query is executed using an alias for that temporary table. The connection is dropped without explicitly dropping that temporary table. In the binary log, mysql records a drop of the temporary table using the table alias. When the replication server reads this command, it is unaware of a table of this name and replication is dropped. How-To-Repeat: # Perl code sample #!/usr/local/bin/perl5 -w use DBI; my $dbh = DBI-connect(DBI:mysql:DBNAME:DBHOST:DBPORT, User, Password); $dbh-do(CREATE TEMPORARY TABLE tmpTableBug(tempField int not null)); $dbh-do(SELECT TableAlias.tempField FROM tmpTableBug AS TableAlias); $dbh-do(CREATE TEMPORARY TABLE tmpTableOk(tempField int not null)); $dbh-do(SELECT tempField FROM tmpTableOk); $dbh-disconnect; # Server will log drop table APES.tmpTableOk,APES.TableAlias; Fix: Explicitly drop temporary table Submitter-Id: Originator: Organization: MySQL support: none Synopsis: TEMPORARY TABLE DROP causes disconnect of replication slave. Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.41 (Source distribution) Environment: System: SunOS flotsam 5.8 Generic_108528-08 sun4u sparc SUNW,Ultra-4 Architecture: sun4 Some paths: /bin/perl /usr/local/bin/make /usr/local/bin/gcc /opt/SUNWspro.5.0/SC5.0/bin/cc GCC: Reading specs from /usr/local/gcc-2.95.2-sl/lib/gcc-lib/sparc-sun-solaris2.6/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='/usr/local/gcc-2.95.2-sl/bin/gcc -R/usr/local/gcc-2.95.2-sl/lib' CFLAGS='' CXX='/usr/local/gcc-2.95.2-sl/bin/g++ -R/usr/local/gcc-2.95.2-sl/lib' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1759264 Jun 29 2001 /lib/libc.a lrwxrwxrwx 1 root root 11 Aug 22 20:13 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136692 Jun 29 2001 /lib/libc.so.1 -rw-r--r-- 1 root bin 1759264 Jun 29 2001 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Aug 22 20:13 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136692 Jun 29 2001 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/unique/apps/mysql-3.23.41 --with-innodb --without-docs Perl: This is perl, version 5.005_03 built for sun4-solaris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: REPLICATION BUG
The bug manifests itself in the following situation. A temporary table has been created on the master server. A query is executed using an alias for that temporary table. The connection is dropped without explicitly dropping that temporary table. In the binary log, mysql records a drop of the temporary table using the table alias. When the replication server reads this command, it is unaware of a table of this name and replication is dropped. ... Release: mysql-3.23.41 (Source distribution) This bug seems to have been fixed in 3.23.46. From the change log: --- D.2.2 Changes in release 3.23.46 Fixed problem with aliased temporary tables replication --- / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication bug
False alarm!! Turns out one of my engineers was using 'LOAD DATA INTO TABLE' instead of inserts. Thanks for all of the replies. - Original Message - From: Will French [EMAIL PROTECTED] To: Gabe E. Nydick [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, September 19, 2001 10:11 AM Subject: RE: replication bug I assume that you have already scanned the MySQL manual section 4.10.4 Replication Features and Known Problems to see if anything listed there as a problem is relevant to your situation. I found a couple of gotchas there that caused me some problems. -Original Message- From: Gabe E. Nydick [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 19, 2001 12:44 PM To: [EMAIL PROTECTED] Subject: Re: replication bug I have found that if I do manual changes to the table, it replicates. If the applications my company wrote make changes, they don't replicate. I am having the programmers find where they went sloppy. - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Gabe E. Nydick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, September 19, 2001 12:47 AM Subject: Re: replication bug On Tue, Sep 18, 2001 at 10:17:26PM -0700, Jeremy Zawodny wrote: On Tue, Sep 18, 2001 at 09:54:51PM -0700, Gabe E. Nydick wrote: I have a large set of tables that are 1-way replicating to an identical machine as the master db, and for some reason 1 table doesn't make it into the binary log. Why would updates to 1 specific table not make it into the binary log? What's the relvant section of your my.cnf file on the master look like? Just bin-log, or is there more there? Err, log-bin, not bin-log. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 13 days, processed 242,448,830 queries (213/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication bug
On Tue, Sep 18, 2001 at 10:17:26PM -0700, Jeremy Zawodny wrote: On Tue, Sep 18, 2001 at 09:54:51PM -0700, Gabe E. Nydick wrote: I have a large set of tables that are 1-way replicating to an identical machine as the master db, and for some reason 1 table doesn't make it into the binary log. Why would updates to 1 specific table not make it into the binary log? What's the relvant section of your my.cnf file on the master look like? Just bin-log, or is there more there? Err, log-bin, not bin-log. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 13 days, processed 242,448,830 queries (213/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication bug
server-id=1 log-bin=/usr/local/mysql-3.23.39/bin-log/db1-bin those are the only replication settings. Have you possibly heard of bad programming practices in Perl/DBI that would cause a query not to make it into the bin-log? - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Gabe E. Nydick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, September 19, 2001 12:47 AM Subject: Re: replication bug On Tue, Sep 18, 2001 at 10:17:26PM -0700, Jeremy Zawodny wrote: On Tue, Sep 18, 2001 at 09:54:51PM -0700, Gabe E. Nydick wrote: I have a large set of tables that are 1-way replicating to an identical machine as the master db, and for some reason 1 table doesn't make it into the binary log. Why would updates to 1 specific table not make it into the binary log? What's the relvant section of your my.cnf file on the master look like? Just bin-log, or is there more there? Err, log-bin, not bin-log. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 13 days, processed 242,448,830 queries (213/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication bug
I have found that if I do manual changes to the table, it replicates. If the applications my company wrote make changes, they don't replicate. I am having the programmers find where they went sloppy. - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Gabe E. Nydick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, September 19, 2001 12:47 AM Subject: Re: replication bug On Tue, Sep 18, 2001 at 10:17:26PM -0700, Jeremy Zawodny wrote: On Tue, Sep 18, 2001 at 09:54:51PM -0700, Gabe E. Nydick wrote: I have a large set of tables that are 1-way replicating to an identical machine as the master db, and for some reason 1 table doesn't make it into the binary log. Why would updates to 1 specific table not make it into the binary log? What's the relvant section of your my.cnf file on the master look like? Just bin-log, or is there more there? Err, log-bin, not bin-log. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 13 days, processed 242,448,830 queries (213/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: replication bug
I assume that you have already scanned the MySQL manual section 4.10.4 Replication Features and Known Problems to see if anything listed there as a problem is relevant to your situation. I found a couple of gotchas there that caused me some problems. -Original Message- From: Gabe E. Nydick [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 19, 2001 12:44 PM To: [EMAIL PROTECTED] Subject: Re: replication bug I have found that if I do manual changes to the table, it replicates. If the applications my company wrote make changes, they don't replicate. I am having the programmers find where they went sloppy. - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Gabe E. Nydick [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, September 19, 2001 12:47 AM Subject: Re: replication bug On Tue, Sep 18, 2001 at 10:17:26PM -0700, Jeremy Zawodny wrote: On Tue, Sep 18, 2001 at 09:54:51PM -0700, Gabe E. Nydick wrote: I have a large set of tables that are 1-way replicating to an identical machine as the master db, and for some reason 1 table doesn't make it into the binary log. Why would updates to 1 specific table not make it into the binary log? What's the relvant section of your my.cnf file on the master look like? Just bin-log, or is there more there? Err, log-bin, not bin-log. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 13 days, processed 242,448,830 queries (213/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replication bug
I have a large set of tables that are 1-way replicating to an identical machine as the master db, and for some reason 1 table doesn't make it into the binary log. Why would updates to 1 specific table not make it into the binary log? Thanks, Gabe E. Nydick
replication bug
I upgraded my running 3.23.39 to 3.23.40 in hopes of taking advantage of the replication bug fix, however, once I upgraded, I found that the master wouldn't run. My my.cnf read log-bin=/usr/local/mysql-3.23.40/bin-log/db1-bin and I get the error message 010801 10:49:27 Could not use /usr/local/mysql-3.23 for logging (error 13) This shows that the new mysqld is not reading the entire entry from the config file because when I down graded back to 3.23.39 everything worked. I did, of course, do all of the necessary changes to config and startup files along with mount points in order to point everything at the right directories. - Gabe E. Nydick Project Manager ClubPhoto, Inc. P - 408.423.6611 F - 408.557.6799 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Potential MySQL replication bug
Description:
I have two MySQL servers (version 3.23.39) configured for two-
way replication; call them server A and server B. When a large
row (~ 3Mb) is entered into a mediumblob field in a table on server A,
this row is replicated to server B. Since the servers are
configured for two-way replication, server B generates an entry
in its own binary log for the record, which server A then
attempts to replicate (it ultimately should ignore the row, since
the log indicates that it was generated at server A). However,
server A logs an error:
Error reading packet from server: Lost connection to MySQL server
during query (read_errno 0,server_errno=2013)
Slave: Failed reading log event, reconnecting to retry, log
'serverb-bin.007' position 2896726
How-To-Repeat:
Note that the following scenario is extrapolated from my actual
running servers. I don't have the machines available to set up
a test environment; the example below simply has names changed
to protect the innocent :)
/etc/my.cnf, server A:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_allowed_packet=16M
log-bin
log-slave-updates
server-id=1
master-host=servera
master-user=repl
master-password=foo
master-port=3306
replicate-do-db=test_db
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
/etc/my.cnf, server B:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_allowed_packet=16M
log-bin
log-slave-updates
server-id=2
master-host=serverb
master-user=repl
master-password=foo
master-port=3306
replicate-do-db=test_db
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Example table definition:
CREATE TABLE test_table (row_id varchar(17) NOT NULL default '',
bigdata mediumblob,
PRIMARY KEY (row_id)) TYPE=MyISAM;
Insert a large row into test_db.test_table on server A:
INSERT INTO test_table VALUES ('this_row', '[Approx. 3Mb data]');
Observe error logs on server A:
Error reading packet from server: Lost connection to MySQL
server during query (read_errno 0,server_errno=2013)
Slave: Failed reading log event, reconnecting to retry, log
'serverb-bin.007' position 2896726
Slave: reconnected to master 'repl@servera:3306',replication
resumed in log 'serverb-bin.007' at position 2896726
[error sequence repeats]
Fix:
Manually change server A's slave position using the following
methodology. Note that using slave_skip_counter does not work; it
reports the same error as above:
From the error log on server A, determine the byte position in server
B's binary log at which server A is failing:
In this example, 2896726
Shut down the MySQL server on server A:
mysqladmin shutdown
Determine the byte position of the row following the problematic
one in server B's binary log:
mysqlbinlog -j 2896726 serverb | less
Browse past the mediumblob insert, finding the position of
the following record (identified by ^# at )
Edit the master.info file on server A, replacing the slave position
(second line) with the value of from server B.
Restart MySQL on server A.
Submitter-Id: submitter ID
Originator:Simon Cocking
Organization:
Network Reconnaissance Pty. Ltd.
MySQL support: none
Synopsis: Two-way replication of rows ~3Mb failing
Severity: serious
Priority: high
Category: mysql
Class: sw-bug
Release: mysql-3.23.39 (Official MySQL RPM)
Server: /usr/bin/mysqladmin Ver 8.21 Distrib 3.23.39, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.39-log
Protocol version10
Replication Bug Across Databases
Description:
When setting replication to only replicate one database between servers via
the binlog-do-db option in my.cnf, it is possible to miss updates on the
master server, or to replicate updates on databases other than the one
specified. All updates are logged to the binary log and replicated if you have
spefied to use the target database, even if the updates are specifically to
a different database via the DATABASE.TABLE syntax. Alternately updates are
NOT Logged and replicated if you have not specified to use the target database, even
if you use the DATABASE.TABLE syntax.
This could cause undesirable effects as it could cause unintended
changes to databases other than the one you are intending on replicating.
How-To-Repeat:
Set up a two servers using replication on one table only using the
binlog-do-db=database in the my.cnf file. Open the mysql client on the master
server but do not specify a database to use. Perform an update to the
replicated database using the DATABASE.TABLE syntax in your query to specify
the exact database and table to update. This update will not be logged or
replicated. Alternately issue a USE DATABASE command to specify using the
replicated DATABASE. Now issue a command to update a table in a different
database, for example "UPDATE MYSQL.user SET Password=PASSWORD('test') WHERE
User = 'root';". This update will be logged to the bin_log and will be
replicated in the slave server.
Fix:
When using the binlog-do-db option, update queries should be checked
to see if they specify a DATABASE in the query, and logged if they Specify the
database to log, even if this database is not currently selected. They should
also be checked if the database to log is currently selected to ensure that
they do not specify a different database before they are logged.
Submitter-Id: [EMAIL PROTECTED]
Originator:[EMAIL PROTECTED]
Organization: I-Land Internet Services
MySQL support: none
Synopsis: Replication Undesireable Effects.
Severity: non-critical
Priority: low
Category: mysql
Class: sw-bug
Release: mysql-3.23.33 (Source distribution)
Server: /usr/local/bin/mysqladmin Ver 8.0 Distrib 3.23.33, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty
Server version 3.23.33-log
Protocol version10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 32 min 11 sec
Threads: 2 Questions: 114 Slow queries: 4 Opens: 8 Flush tables: 1 Open tables: 2
Queries per second avg: 0.059
Environment:
System: Linux systech.iland.net 2.2.17-14-JLH #1 Mon Feb 19 12:56:34 CST 2001 i686
unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC:
Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
Configure command: ./configure
Perl: This is perl, version 5.005_03 built for i386-linux
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication bug
Scott: See my comments below regarding the replication bug you have reported. error from log file: 010410 15:18:20 Slave: connected to master 'navrep@hsNavYkfPrd4:3306', replication started in log 'hsNavYkfPrd4-bin.060' at position 14290269 ERROR: 1064 You have an error in your SQL syntax near '' at line 1 010410 15:18:34 Slave: did not get the expected error running query from master - expected: 'Got an error reading communication packets', got 'no error' 010410 15:18:34 Slave: error running query 'drop table scratch.#sql-6fd2_9b3' 010410 15:18:34 Error running query, slave aborted. Fix the problem, and re-start the slave thread with "mysqladmin start-slave". We stopped at log 'hsNavYkfPrd4-bin.060' position 14298032 010410 15:18:34 Slave thread exiting, replication stopped in log 'hsNavYkfPrd4-bin.060' at position 14298032 The bug is that somehow the query that dropped the temporary table gets logged wrong - I will take a look at the code and see how this could be possible, and make sure it gets fixed before 3.23.37 is out. In the mean time, there are three possible workarounds: * do not manually drop the temporary table - the master will drop it when you disconnect, and it will use different ( hopefully bug-free) code to log the drop of the table * when you hit the replication error on the slave, do SET SQL_SKIP_COUNTER=1; SLAVE START to skip over the problem entry * since you seem to be generating a unique name for the temporary table anyway, and are dropping it manually later, there is really no advantage in using the TEMPORARY attribute - just drop the temporary keyword First off, I assumed that anything dealing with the scratch database wouldn't be logged into the binlog file (they are) Also, all of the tables created in the scratch database are temporary tables. How can I make it so that these updates do not go across a slow link when they are not needed Basically the sequence of commands that causes this connect to DB DROP TABLE IF EXISTS scratch.3340_tmp; CREATE TEMPORARY TABLE scratch.3340_tmp SELECT * FROM ntm_user_detail WHERE ; ALTER TABLE scratch.3340_tmp ADD PRIMARY KEY(vch_station,vch_series,vch_number); INSERT INTO scratch.3340_tmp SELECT * FROM ntm_detail WHERE ...; SELECT vch_station,vch_series,vch_number FROM scratch.3340_tmp; and all of that repeats until the connection is closed. I've also attempted adding 'replicate-ignore-db=scratch' to the /etc/my.cnf file with no apparent results. Am I doing something wrong here? replicate-ignore-db=scratch (on the slave) works only if you do "use scratch" before you start modifying the table in scratch. If you are using db.table syntax, you should use replicate-wild-ignore-table=scratch.%. The only way to not send the queries to the slave at all is not to log them. This can be accomplished in several ways: * issue SET SQL_LOG_BIN=0 to turn off binary logging and SET SQL_LOG_BIN=1 to turn this back ok - this requires PROCESS privilege * use binlog-ignore-db=scratch on the master and make sure to do "use scratch" before you start the updates you do not want to be logged, and "use some_other_db" to turn the logging back on -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication bug - PATCH
On Tuesday 10 April 2001 10:55, Sasha Pachev wrote:
Scott:
See my comments below regarding the replication bug you have reported.
error from log file:
010410 15:18:20 Slave: connected to master 'navrep@hsNavYkfPrd4:3306',
replication started in log 'hsNavYkfPrd4-bin.060' at position 14290269
ERROR: 1064 You have an error in your SQL syntax near '' at line 1
010410 15:18:34 Slave: did not get the expected error running query
from master - expected: 'Got an error reading communication packets',
got 'no error'
010410 15:18:34 Slave: error running query 'drop table
scratch.#sql-6fd2_9b3'
010410 15:18:34 Error running query, slave aborted. Fix the problem,
and re-start the slave thread with "mysqladmin start-slave". We stopped
at log 'hsNavYkfPrd4-bin.060' position 14298032
010410 15:18:34 Slave thread exiting, replication stopped in log
'hsNavYkfPrd4-bin.060' at position 14298032
Found the problem - if somehow there was a temporary table left over in the
temporary tables list of the the thread that was created internally by MySQL
to process some query, on disconnect the record of it being dropped was
erroneously made in the binary log. This is why you see the slave trying to
drop a table with a very strange name - scratch.#sql-6fd2_9b3.
The patch below not only takes care of the left over internal temp table bug,
but also addresses the issue of dealing with updates that only partially
complete because of some unusual conditions or errors, eg killed thread. If
the slave sees a query in the log that completed with an abnormal error, it
will now just abort and wait for the DBA to verify data integrity and restart
the slave with SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; . The patch will be
present in 3.23.37:
--- 1.73/sql/sql_base.ccSun Apr 1 16:45:24 2001
+++ edited/sql_base.cc Tue Apr 10 11:44:37 2001
@@ -497,13 +497,14 @@
TABLE *table,*next;
uint init_query_buf_size = 11, query_buf_size; // "drop table "
char* query, *p;
+ bool found_user_tables = 0;
+
LINT_INIT(p);
query_buf_size = init_query_buf_size;
for (table=thd-temporary_tables ; table ; table=table-next)
{
query_buf_size += table-key_length;
-
}
if(query_buf_size == init_query_buf_size)
@@ -519,15 +520,20 @@
{
if(query) // we might be out of memory, but this is not fatal
{
- p = strxmov(p,table-table_cache_key,".",
+ // skip temporary tables not created directly by the user
+ if(table-table_name[0] != '#')
+ {
+ p = strxmov(p,table-table_cache_key,".",
table-table_name,",", NullS);
- // here we assume table_cache_key always starts
- // with \0 terminated db name
+ // here we assume table_cache_key always starts
+ // with \0 terminated db name
+ found_user_tables = 1;
+ }
}
next=table-next;
close_temporary(table);
}
- if (query mysql_bin_log.is_open())
+ if (query found_user_tables mysql_bin_log.is_open())
{
uint save_query_len = thd-query_length;
*--p = 0;
--- 1.94/sql/slave.cc Tue Mar 13 23:07:11 2001
+++ edited/slave.cc Tue Apr 10 19:48:11 2001
@@ -59,6 +59,8 @@
static int create_table_from_dump(THD* thd, NET* net, const char* db,
const char* table_name);
inline char* rewrite_db(char* db);
+static int check_expected_error(THD* thd, int expected_error);
+
static void free_table_ent(TABLE_RULE_ENT* e)
{
my_free((gptr) e, MYF(0));
@@ -834,6 +836,27 @@
return len - 1;
}
+static int check_expected_error(THD* thd, int expected_error)
+{
+ switch(expected_error)
+{
+case ER_NET_READ_ERROR:
+case ER_NET_ERROR_ON_WRITE:
+case ER_SERVER_SHUTDOWN:
+case ER_NEW_ABORTING_CONNECTION:
+ my_snprintf(last_slave_error, sizeof(last_slave_error),
+"Slave: query '%s' partially completed on the master \
+and was aborted. There is a chance that your master is inconsistent at this
\ +point. If you are sure that your master is ok, run this query manually on
the\+ slave and then restart the slave with SET SQL_SLAVE_SKIP_COUNTER=1;\
+ SLAVE START;", thd-query);
+ last_slave_errno = expected_error;
+ sql_print_error(last_slave_error);
+ return 1;
+default:
+ return 0;
+}
+}
static int exec_event(THD* thd, NET* net, MASTER_INFO* mi, int event_len)
{
@@ -883,22 +906,38 @@
thd-net.last_errno = 0;
thd-net.last_error[0] = 0;
thd-slave_proxy_id = qev-thread_id; // for temp tables
- mysql_parse(thd, thd-query, q_len);
- if ((expected_error = qev-error_code) !=
- (actual_error = thd-net.last_errno) expected_error)
- {
- const char* errmsg = "Slave: did not get the expected error\
+
+ // sanity check to make sure the master did not get a really bad
+ // error on the query
+
repost: replication bug
I checked the known bugs in replication, and I've found a situation where it definitely does not propogate changes. I'd just like to know if this is normal or not. Say you have two databases (call them data1 and data2) on your master server. Only data1 is being replicated. Within the mysql client, connecting to the master machine: use data2 /* this database is NOT being replicated */; update data1.table set somefield=1 where somevalue=1; The data changes properly in the master, but does not get replicated to any slaves. simply doing this: use data1 update table set somefield=1 where somevalue=1; works as expected. Any comments?
Re: Re: Replication Bug in 3.23.33
Does this problem exist in 3.23.32?? Thanks for the bug report. The problem is a bug in the code that skips events when it sees a log entry with the same server id - something that can only happen in the bi-directional replicaiton setup. Fix: --- 1.85/sql/slave.cc Sat Jan 27 15:33:30 2001 +++ edited/slave.cc Wed Feb 14 12:35:34 2001 @@ -849,7 +849,8 @@ mi-inc_pos(event_len); flush_master_info(mi); - --slave_skip_counter; + if(slave_skip_counter) +--slave_skip_counter; delete ev; return 0;// avoid infinite update loops } --- Tenha uma conta de email Grátis no ACBusca! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Replication Bug in 3.23.33
On Thursday 15 February 2001 18:50, Rodolfo Sikora wrote: Does this problem exist in 3.23.32?? Thanks for the bug report. The problem is a bug in the code that skips events when it sees a log entry with the same server id - something that can only happen in the bi-directional replicaiton setup. Fix: --- 1.85/sql/slave.cc Sat Jan 27 15:33:30 2001 +++ edited/slave.ccWed Feb 14 12:35:34 2001 @@ -849,7 +849,8 @@ mi-inc_pos(event_len); flush_master_info(mi); - --slave_skip_counter; + if(slave_skip_counter) + --slave_skip_counter; delete ev; return 0;// avoid infinite update loops } This one does not, but there is a bigger one - restarting the slave does not work. -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication Bug in 3.23.33
On Wednesday 14 February 2001 09:19, Matt Hahnfeld wrote: After downgrading to 3.23.30, replication worked fine without the problem posted below. This appears to be a bug in the newest version (3.23.33) only. The failed tests were run under mysql-3.23.33-pc-linux-gnu-i686 (binary distribution). The same tests succeeded under mysql-3.23.30-gamma-pc-linux-gnu-i686 (binary distribution) with no problems. -- Forwarded message -- Date: Tue, 13 Feb 2001 14:33:47 -0500 (EST) From: Matt Hahnfeld [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Strange Replication Problem in 3.23.33 (bug?) I set up two MySQL servers to run in a failover configuration. Because queries will only ever be submitted to one server at a time, I decided to use a makeshift two-way replication scheme under MySQL as descibed in the MySQL manual. First server (wallace) has this: server-id=1 log-bin master-host=gromit master-user=repl master-password=password log-slave-updates Second server (gromit) has this: server-id=2 log-bin master-host=wallace master-user=repl master-password=password log-slave-updates I started by mirroring both data directories. Then I started both servers and all looked fine. Logs indicate no errors. When I inserted some data on wallace, gromit replicated them just fine. But when I tried to insert data on gromit, wallace never got the changes. The weird thing is, no real errors appeared in the logs. Then I did a "SHOW SLAVE STATUS" on wallace and saw "Skip_counter" was set to 4294967295!!! Strange, I thought, so I ran "STOP SLAVE", "SET SQL_SLAVE_SKIP_COUNTER=0", and "START SLAVE" on wallace. Suddenly changes made on gromit were reflected on wallace. But then I tried to insert data on wallace again and the same thing happened. This time gromit never got the changes. When I ran "SHOW SLAVE STATUS" on gromit, it indicated 4294967293. To get it to work, I had to run "SET SQL_SLAVE_SKIP_COUNTER=0" on gromit. I just don't get it... Why are the skip counters being reset to thse crazy high numbers? Thanks for the bug report. The problem is a bug in the code that skips events when it sees a log entry with the same server id - something that can only happen in the bi-directional replicaiton setup. Fix: --- 1.85/sql/slave.cc Sat Jan 27 15:33:30 2001 +++ edited/slave.cc Wed Feb 14 12:35:34 2001 @@ -849,7 +849,8 @@ mi-inc_pos(event_len); flush_master_info(mi); - --slave_skip_counter; + if(slave_skip_counter) +--slave_skip_counter; delete ev; return 0;// avoid infinite update loops } -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication Bug in 3.23.33
On Wednesday 14 February 2001 12:58, Matt Hahnfeld wrote: Wow, that was fast! Thanks!! We mean what we say - the better the bug report, the quicker the fix :-) --Matt On Wed, 14 Feb 2001, Sasha Pachev wrote: On Wednesday 14 February 2001 09:19, Matt Hahnfeld wrote: After downgrading to 3.23.30, replication worked fine without the problem posted below. This appears to be a bug in the newest version (3.23.33) only. The failed tests were run under mysql-3.23.33-pc-linux-gnu-i686 (binary distribution). The same tests succeeded under mysql-3.23.30-gamma-pc-linux-gnu-i686 (binary distribution) with no problems. -- Forwarded message -- Date: Tue, 13 Feb 2001 14:33:47 -0500 (EST) From: Matt Hahnfeld [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Strange Replication Problem in 3.23.33 (bug?) I set up two MySQL servers to run in a failover configuration. Because queries will only ever be submitted to one server at a time, I decided to use a makeshift two-way replication scheme under MySQL as descibed in the MySQL manual. First server (wallace) has this: server-id=1 log-bin master-host=gromit master-user=repl master-password=password log-slave-updates Second server (gromit) has this: server-id=2 log-bin master-host=wallace master-user=repl master-password=password log-slave-updates I started by mirroring both data directories. Then I started both servers and all looked fine. Logs indicate no errors. When I inserted some data on wallace, gromit replicated them just fine. But when I tried to insert data on gromit, wallace never got the changes. The weird thing is, no real errors appeared in the logs. Then I did a "SHOW SLAVE STATUS" on wallace and saw "Skip_counter" was set to 4294967295!!! Strange, I thought, so I ran "STOP SLAVE", "SET SQL_SLAVE_SKIP_COUNTER=0", and "START SLAVE" on wallace. Suddenly changes made on gromit were reflected on wallace. But then I tried to insert data on wallace again and the same thing happened. This time gromit never got the changes. When I ran "SHOW SLAVE STATUS" on gromit, it indicated 4294967293. To get it to work, I had to run "SET SQL_SLAVE_SKIP_COUNTER=0" on gromit. I just don't get it... Why are the skip counters being reset to thse crazy high numbers? Thanks for the bug report. The problem is a bug in the code that skips events when it sees a log entry with the same server id - something that can only happen in the bi-directional replicaiton setup. Fix: --- 1.85/sql/slave.cc Sat Jan 27 15:33:30 2001 +++ edited/slave.cc Wed Feb 14 12:35:34 2001 @@ -849,7 +849,8 @@ mi-inc_pos(event_len); flush_master_info(mi); - --slave_skip_counter; + if(slave_skip_counter) +--slave_skip_counter; delete ev; return 0;// avoid infinite update loops } -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
