MySQL Bugs: #3854: User Variables Not Replicated

Bug #3854	User Variables Not Replicated
Submitted:	22 May 2004 0:52	Modified:	22 May 2004 23:25
Reporter:	Bruce Bristol	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Linux (Red Hat Linux)
Assigned to:		CPU Architecture:	Any

Description:
The following executed on the master:

SELECT @max_destkey_id := MAX(destkey_id) FROM r3_destkey;
REPLACE INTO sequence_table (table_name, seq_number)
VALUES ('r3_destkey', @max_destkey_id);

Resulted in NULL for the seq_number column, though the master has a real value such as 12345.

I tested it and found the user variables apparently don't get replicated.

How to repeat:
On a master:

CREATE TABLE `sequence_table` (
  `table_name` varchar(20) NOT NULL default '',
  `seq_number` int(10) unsigned default '0',
  `last_update` timestamp(14) NOT NULL,
  PRIMARY KEY  (`table_name`)
) TYPE=MyISAM;

SELECT @max_destkey_id := MAX(destkey_id) FROM r3_destkey;
REPLACE INTO sequence_table (table_name, seq_number)
VALUES ('r3_destkey', @max_destkey_id);

This select - "SELECT @max_destkey_id := MAX(destkey_id) FROM r3_destkey;" can use a max(any numeric column) from any table for testing.

On a slave:

select * from sequence_table;

Suggested fix:
SQL statements using user variables should be passed to the slave with the actual value of the user variable -OR- the user variable should be created on the slave as it is on the master.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Hello,
you are right, this is a deficiency of MySQL 3.23 and 4.0, listed at
http://dev.mysql.com/doc/mysql/en/Replication_Features.html :
"Update statements that make use of user variables (that is, variables of the form @var_name) are badly replicated in 3.23 and 4.0. This is fixed in 4.1.".
Starting from MySQL 4.1.0 (both master and slave must run MySQL 4.1.0 or newer), we implemented the 2nd fix you suggested ("the user variable should be created
on the slave as it is on the master").
Thank you for your bug report.
Guilhem