Replication with 5.0 master and 5.6 slave

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I have a problem with a replica. We have 2 servers in a master-master circular replication and each of them has some slaves. All these are mysql 5.0 servers. We now decided to upgrade to mysql 5.6 to take advantage of it’s improvements but we run into an issue. We upgraded one of the slaves to 5.6 and we have a problem because the 5.6 slave doesn’t seem to be using the indexes when executing the statements (we are not yet using row based replication). Any idea what could be the problem or how to debug this issue?

Thank you

2

What i could add is that some indexes are on varchar fields using latin1 with latin1_general_ci collation and we are using also local variables. Checking with mysqlbinlog i can see those variables appear in the relay log. Here is an example:
SET @trId:=_latin1 0x3065376231633363656562383130333039623235343830623630633061 336564 COLLATE latin1_general_ci/!/;

However the update query using this variable is not using the index. Any ideas?

Thank you

3

It’s a query like:

update mytable set f1=‘abc’ where tr=@trId;

Nobody has any clues what could be the issue? :frowning:

4

Hi Tom,

I just realized that i did not answer your question :frowning:
No we are not using Percona Server but mysql server version 5.0.45 on the master and myqsl server 5.6.10. Both are x64 running on CentOS 6.3 and are installed from RPM.

Thank you,
Pavel

5

Hi Tom,

Do you have any suggestions? I still didn’t figure out what could be the issue :frowning: I really see no logic behind this and i can’t understand why the index is not being used…

Thanks,
Pavel

6

Just in case somebody else has this issue…It seems the issue is actually a bug in MySQL and not somethimg related to replication from 5.0 master.
It has been fixed in 5.6.12: [url]MySQL Bugs: #68811: Replication slave stuck on simple query with user variables.

Any idea when 5.6.12 will be GA?