Bug #30244 row_count/found_rows does not replicate well
Submitted: 5 Aug 2007 17:52 Modified: 14 Sep 2007 15:10
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.48 5.1.21, 4.1 OS:Any
Assigned to: Georgi Kodinov
Tags: found_rows, qc, replication, row_count

[5 Aug 2007 17:52] Martin Friebe
Description:
using the function row_count in replication (bin log) does not work.

http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
contains a list of functions that fo not do well in replication, but does not mention row_count.

insert into table select row_count();

on the master will keep track of what the last statement did. A slave may have executed insert/updated from a different session, and will write a different result.

the testcase below will give the following results

t2 on the master:select * from t2;
a
1
3

t2 on the slave
a
5
3

How to repeat:
source include/master-slave.inc;

connection master;

create table t1 (a int); 
create table t2 (a int); 

insert into t1 select 1;

connection master1;
insert into t1 values (2),(3),(4),(5),(6);

connection master;
insert into t2 select row_count();

insert into t1 values (2),(3),(4);
insert into t2 select row_count();

select * from t2;

save_master_pos;
connection slave;
sync_with_master;

select * from t2;

Suggested fix:
-
[5 Aug 2007 18:03] Martin Friebe
in a similioar fashion: found_rows

the master has 8 (correct)
the slave has 4

source include/master-slave.inc;

connection master;

create table t1 (a int); 
create table t2 (a int); 

insert into t1 select 1;
insert into t1 select a*2 from t1;
insert into t1 select a*2 from t1;
insert into t1 select a*2 from t1;

select SQL_CALC_FOUND_ROWS a from t1 limit 1;
insert into t2 select found_rows();

select * from t2;

save_master_pos;
connection slave;
sync_with_master;

select * from t2;
[6 Aug 2007 8:32] Sveta Smirnova
Thank you for the report.

Verified as described using last BK sources.
[21 Aug 2007 14:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32816

ChangeSet@1.2571, 2007-08-21 17:44:22+03:00, gkodinov@magare.gmz +3 -0
  Bug #30244: row_count/found_rows does not replicate well
  
   The functions ROW_COUNT/FOUND_ROWS are indeed not safe to be used in 
   statement based replication.
   Added code to declare them as such and switch the statement they're in
   to row based logging for mixed mode.
[21 Aug 2007 14:47] Georgi Kodinov
Note that the fix is added only in 5.1 (and applies only to mixed mode logging). For the rest of the cases (statement mode binlog or 4.x and 5.x) the documentation needs to be updated to reflect the fact that ROW_COUNT()/FOUND_ROWS() are not replication safe.
[29 Aug 2007 11:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33299

ChangeSet@1.2571, 2007-08-29 14:54:32+03:00, gkodinov@magare.gmz +3 -0
  Bug #30244: row_count/found_rows does not replicate well
  
   The functions ROW_COUNT/FOUND_ROWS are indeed not safe to be used in 
   statement based replication.
   Added code to declare them as such and switch the statement they're in
   to row based logging for mixed mode.
[14 Sep 2007 7:44] Bugs System
Pushed into 5.1.23-beta
[14 Sep 2007 15:10] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented feature change in 5.1.23 changelog. Updated function descriptions and replication-features sections of 4.1/5.0/5.1 Manual to reflect information given in this bug report.
[27 Nov 2007 10:50] Bugs System
Pushed into 5.1.23-rc
[27 Nov 2007 10:53] Bugs System
Pushed into 6.0.4-alpha