MySQL Bugs: #30244: row_count/found_rows does not replicate well

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	CPU Architecture:	Any
Tags:	found_rows, qc, replication, row_count

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

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;

Thank you for the report.

Verified as described using last BK sources.

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.

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.

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.

Pushed into 5.1.23-beta

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.

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha