User-defined variables not working ONLY on first usage in chained replication

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:

CREATE TABLE test_table (result TEXT) ENGINE=InnoDB;
SET @mynewvariable = "testvalue"

And then insert the variable into the test table:

INSERT INTO test_table VALUES (@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 bug?

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

Just bumping this in case any new eyes have a bright idea.


I tested it and it’s works fine for me. I used Percona-Server-5.5.31 for master/slaves
Can you test it with 5.5.28 instead 5.5.20 ? Look likes bug to me.
Remind you setting.user variable in session will lost the variable value if you disconnects from master.

This bug may be relevant here:
however it says it’s been fixed since 5.5.28, so as master3 is 5.5.28 already, that should not happen.

Or maybe you are hitting conditions described in the bug fix around (hidden from us) bug #16541422 which was fixed in 5.5.32? See this for more details:

Turns out this was us hitting the bug that was fixed in 5.5.32:

We’ve upgraded MySQL and dropped the replicate-ignore-table options we had in my.cnf as they’re no longer necessary, and this fixed the issue.