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.
