MySQL Bugs: #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements

Bug #12826	Possible to get inconsistent slave using SQL syntax Prepared Statements
Submitted:	26 Aug 2005 11:58	Modified:	10 Jun 2007 20:23
Reporter:	Sergey Petrunia
Status:	Closed
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any
Assigned to:	Ramil Kalimullin	Target Version:

Description:
It is possible to get slave out of sync using SQL syntax Prepared Statements if one
modifies session variable in PS and uses it as PS parameter at the same time.

How to repeat:
Run this test:
---t/rpl_a.test---
source include/master-slave.inc;
connection master;

create table t1(a int, b int);
set @x=1;
prepare s1 from 'insert into t1 values (@x:=@x+1, ?)';
execute s1 using @x;

select 'master:' A;
select * from t1;

sync_slave_with_master;
connection slave;
select 'slave:' A;
select * from t1;
---eof---

and in the result file get:

master:
select * from t1;
a	b
2	1
select 'slave:' A;
A
slave:
select * from t1;
a	b
2	2

Tested w/5.0 from bk

However, note that the manual warns against using and setting in the same statement:
http://dev.mysql.com/doc/refman/5.1/en/user-variables.html
"The general rule is to never assign a value to a user variable in one part of a statement
and use the same variable in some other part the same statement. You might get the results
you expect, but this is not guaranteed."

Ramil,

I'm concerned that this bug may fall into the group of bugs which we won't fix, because
they both set and use the value of a user variable in the same statement.  See bug #19792
or bug #1739 for other examples.

It may be that changing how we replicate these prepared statements (where a user variable
is passed as the value of a placeholder) will make MySQL behave *more* correctly. 
However, the particular example of "insert into t1 values (@x:=@x+1, ?)" should not be
expected to work consistently.

I'm reluctant to add this example as a test case in our test suite, even if it happens to
work "as expected" once this bug (or the related bug #26842) is fixed.

Regards,

Timothy

Shoot, I forgot an important point in my previous comment.  Since I think we can't ensure
that the results of such a statement will be identical on the master and on the slave,
then we should warn (and throw an error, depending on sql mode) when such a statement is
issued with binary logging on.

That is the real bug here which must be addressed, in my opinion.

Pushed into 5.1.20-beta

Pushed into 5.0.44

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 fix in 5.0.44 and 5.1.20 changelogs.