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.
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.