Bug #12826 Possible to get inconsistent slave using SQL syntax Prepared Statements
Submitted: 26 Aug 2005 9:58 Modified: 10 Jun 2007 18:23
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any

[26 Aug 2005 9:58] Sergey Petrunya
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
[26 Aug 2005 18:25] Jorge del Conde
Tested w/5.0 from bk
[9 Aug 2006 19:45] Guilhem Bichot
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."
[17 Apr 2007 22:51] Timothy Smith
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
[17 Apr 2007 22:55] Timothy Smith
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.
[6 Jun 2007 16:54] Bugs System
Pushed into 5.1.20-beta
[6 Jun 2007 16:58] Bugs System
Pushed into 5.0.44
[10 Jun 2007 18:23] 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 fix in 5.0.44 and 5.1.20 changelogs.