List:General Discussion« Previous MessageNext Message »
From:Matthew Ward Date:July 16 2013 11:21am
Subject:User-defined variables not working ONLY on first query in chained replication
View as plain text  
I've noticed a weird issue in our chained replication environment where when setting
user-defined variables, the first time the variable is used in a session the value is
NULL, but all uses thereafter work correctly.

The environment is such: we have a master (master1), which has a slave which is also a
master (master3), which itself has slaves (master3-slave1), i.e.:

master1 -> master3 -> master3-slave1

I can replicate my issue with a very simple setup. I simply create a test table with one
TEXT column, and I set a user-defined variable:

SET @mynewvariable = "testvalue"
And then insert the variable into the test table:

INSERT INTO test_table VALUES (NULL, @mynewvariable);
The first time I run this insert, the value is correctly inserted in to master1 and its
slave, master3 (as you'd expect). However, a NULL value is inserted into master3-slave1.
However, if I run the INSERT a second time (just the insert, no re-declaration of the
user-defined variable), the value is correctly inserted in to all three servers, so that
the contents of test_table on the three servers looks as follows:

master1   master3   master3-slave1
--------- --------- --------------
testvalue testvalue NULL
testvalue testvalue testvalue
I don't believe this is related to replication delay, because even if I leave a while
between setting the variable and running the first INSERT, the result is always the same.
The problem is agnostic of table format or how complex the table is, we can reproduce it
exactly like this all of the time.

Is this a known issue in MySQL with chained replication like this, or have I discovered a

Server version information:

master1: Percona Server 5.5.28-29.1
master3: Percona Server 5.5.28-29.3
master3-slave1: Percona Server 5.5.20-55


Global Personals is a limited company registered in England and Wales. 
Registered number: 04880697. 
Registered office: Minton Place, Victoria Street, Windsor, Berkshire, SL4 
1EG, United Kingdom.

User-defined variables not working ONLY on first query in chained replicationMatthew Ward16 Jul
  • Re: User-defined variables not working ONLY on first query in chainedreplicationJesper Wisborg Krogh16 Jul