Bug #1331 Unexistent user variable is not replicated
Submitted: 18 Sep 2003 5:39 Modified: 4 Oct 2003 6:44
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.1 OS:Any (all)
Assigned to: Guilhem Bichot

[18 Sep 2003 5:39] Guilhem Bichot
Description:
Since 4.1 user variables are replicated.
In MySQL, when a user variable is used without having been set before, its value is NULL.

Your MySQL connection id is 4 to server version: 4.1.1-alpha-debug-log
MASTER> set @a=1;
MASTER> insert into v values(@a);
MASTER> Bye
Your MySQL connection id is 5 to server version: 4.1.1-alpha-debug-log
MASTER> insert into v values(@a);
MASTER> select * from v;
+------+
| a    |
+------+
|    1 |
| NULL |
+------+
MASTER> Bye

But in slave:

SLAVE> select * from v;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

This is because the master creates a User_var event in the binlog only for variables which exist (which have been set).
Binlog in master:

MASTER> show binlog events;
+---------------------+-----+------------+-----------+--------------+--------------------------------------------------+
| Log_name            | Pos | Event_type | Server_id | Orig_log_pos | Info                                             |
+---------------------+-----+------------+-----------+--------------+--------------------------------------------------+
| gbichot2-bin.000001 |   4 | Start      |         1 |            4 | Server ver: 4.1.1-alpha-debug-log, Binlog ver: 3 |
| gbichot2-bin.000001 |  79 | Query      |         1 |           79 | use `test`; drop table if exists v               |
| gbichot2-bin.000001 | 136 | Query      |         1 |          136 | use `test`; create table v(a int)                |
| gbichot2-bin.000001 | 192 | User var   |         1 |          192 | @a=1                                             |
| gbichot2-bin.000001 | 234 | Query      |         1 |          234 | use `test`; insert into v values(@a)             |
| gbichot2-bin.000001 | 293 | Query      |         1 |          293 | use `test`; insert into v values(@a)             |
+---------------------+-----+------------+-----------+--------------+--------------------------------------------------+

Only the 1st thread, the one which explicitely sets the variable, has written a User_var event. So in the slave, when it plays the query of the second thread, it still uses the value of @a from the first thread. There should be a User_var which does @a=NULL before the 2nd INSERT.

How to repeat:
see description

Suggested fix:
I will fix it for the next 4.1 release.
[4 Oct 2003 6:44] Guilhem Bichot
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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

ChangeSet@1.1593.3.1 and ChangeSet@1.1593.3.2