Bug #48369 Replication is broken between mysql 4.0 and mysql 5.1
Submitted: 28 Oct 2009 10:31 Modified: 8 Dec 2009 19:19
Reporter: Guillaume Lefranc Email Updates:
Status: Unsupported Impact on me:
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.38+ OS:Linux (ubuntu 8.04 LTS)
Assigned to: Daogang Qu CPU Architecture:Any
Tags: regression, replication, slave stops, time zone

[28 Oct 2009 10:31] Guillaume Lefranc

The following commit : http://lists.mysql.com/commits/77612?f=plain breaks replication between a MySQL 4.0 master and a MySQL 5.1 slave.
Basically, this check : query_re= mysql_real_query(mysql, STRING_WITH_LEN("SELECT @@GLOBAL.TIME_ZONE")); will always fail on 4.0 masters since :
1. it's impossible to use SELECT @@global.whatever in MySQL 4.0 to query static system variables,
2. The variable is named 'timezone' in MySQL 4.0, not 'time_zone' and it cannot be set at runtime.

How to repeat:
Setup replication with a 4.0.X master and mysql 5.1.39. Boom, it will break with the following error :

The slave I/O thread stops because a fatal error is encountered when it try to get the value of TIME_ZONE global variable from master. Error: Unknown system variable 'TIME_ZONE'

Suggested fix:
Rewrite the check to read the correct value in mysql 4.0, if it's possible. Or add the option to disable the check (it's very annoying if you intend to migrate a PF to 5.1)
[1 Nov 2009 17:27] Susanne Ebrecht
Thank you for taking the time to report a problem.  

Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

MySQL 4.0 is really old.
MySQL 5.0 will run out of support in December 2009.

Please first upgrade your MySQL 4.0 master to an actual version. Recommended here is to MySQL 5.1.
[5 Dec 2009 12:18] Domas Mituzas
Susanne, is 'unsupported' your personal opinion, or did it involve any thinking?
[5 Dec 2009 13:37] Valeriy Kravchuk
I think that some option to disable/change this and other similar checks so that 5.1.x slave is able to replicate from 4.0.x master would be nice and really useful to have. So, this is at least a reasonable feature request.
[5 Dec 2009 15:35] Domas Mituzas
This is more of a regression/bug, than feature request, because it used to work before 5.1.42, and caused broken 5.1.x systems after upgrade. The 4.0 ones work just fine :)
[6 Dec 2009 4:52] James Day
This particular bug report looks like a pure accidental breakage that should have failed code review, because of this code:

This check is only necessary for 4.x masters (and < 5.0.4 masters but
     those were alpha).
-  if ((*mysql->server_version == '4') &&
-      !mysql_real_query(mysql, STRING_WITH_LEN("SELECT @@GLOBAL.TIME_ZONE")) &&
-      (master_res= mysql_store_result(mysql)))
-  {
-    if ((master_row= mysql_fetch_row(master_res)) &&
-        strcmp(master_row[0],
-               global_system_variables.time_zone->get_name()->ptr()))
+  if (*mysql->server_version == '4')
+  {
+    query_re= mysql_real_query(mysql, STRING_WITH_LEN("SELECT @@GLOBAL.TIME_ZONE"));
+    if (query_re)

That is: the code that is there to be compatible with 4.x servers sends a command that is not compatible with 4.x. So easy to confirm that it's a regression bug that needs to be fixed within the same major version in which it was introduced.
[6 Dec 2009 5:24] James Day
Also see bug #49474 that looks likely to be a duplicate of this one or have this one as the main breakage cause.
[6 Dec 2009 8:32] Valeriy Kravchuk
Bug #49474 was marked as a duplicate of this one. 5.1.42 will break working replication in many environments that worked before.
[7 Dec 2009 13:46] Valeriy Kravchuk
As this problem was a result of patch for bug #45214, this is a regression since 5.1.38 comparing to older versions.
[8 Dec 2009 19:24] Omer Barnir
This might have worked with earlier versions of 5.1 but this is not a supported replication configuration. 

Please see:

 "MySQL supports replication from one major version to the next higher major version. For example, you can replicate from a master running MySQL 4.1 to a slave running MySQL 5.0, from a master running MySQL 5.0 to a slave running MySQL 5.1, and so on. "
[8 Dec 2009 21:45] Domas Mituzas
Omer, I know how to read, and I even know how to read more than one paragraph. Say, second paragraph on very same page says:

"In some cases, it is also possible to replicate between a master and a slave that is more than one major version newer than the master. However, there are known issues with trying to replicate from a master running MySQL 4.1 or earlier to a slave running MySQL 5.1 or later. "

See, the very same manual suggested that I can try replicating, and I did. 
If by "there are known issues" you mean developer stubbornness to fix regression, feel free to elaborate on that in the manual.
[9 Dec 2009 11:06] Daogang Qu
The root cause is that the variable is named 'timezone' in MySQL 4.0, 
and its value just can be got by command 'show variables like "timezone"' 
as following:
show variables like "timezone";
Variable_name   Value
timezone        MET

It works fine on MySQL 4.1, because MySQL 4.1 support the 

The code logic is right in the patch of bug45214, and the patch 
is displaying the truth:
Unknown system variable 'TIME_ZONE' when master is a MySQL 4.0 master.
[10 Dec 2009 1:41] James Day
Domas, at times end users have taken the approach of supplying patches to incorporate functionality that Sun doesn't include itself.
[10 Dec 2009 1:55] Mark Callaghan
What happens to those patches?
[13 Dec 2009 17:37] Domas Mituzas
My patch is at http://bazaar.launchpad.net/~wikimedia/sakila-server/mysql-5.1-wm/revision/3191 - I hereby release it into public domain with 'do whatever you want with it' license ;-)
[14 Dec 2009 10:32] Domas Mituzas
I should also note that this bug disables ability to replicate from software that implements binlog-dump protocol, and now requires COM_QUERY support. 

Just thought, you may want to know!