MySQL Bugs: #48369: Replication is broken between mysql 4.0 and mysql 5.1

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:	None
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

Description:
Hello,

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)

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.

Susanne, is 'unsupported' your personal opinion, or did it involve any thinking?

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.

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 :)

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.

Also see bug #49474 that looks likely to be a duplicate of this one or have this one as the main breakage cause.

Bug #49474 was marked as a duplicate of this one. 5.1.42 will break working replication in many environments that worked before.

As this problem was a result of patch for bug #45214, this is a regression since 5.1.38 comparing to older versions.

This might have worked with earlier versions of 5.1 but this is not a supported replication configuration. 

Please see:
http://dev.mysql.com/doc/refman/5.1/en/replication-compatibility.html

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

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.

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 
command 'SELECT @@GLOBAL.TIME_ZONE'.

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.

Domas, at times end users have taken the approach of supplying patches to incorporate functionality that Sun doesn't include itself.

What happens to those patches?

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 ;-)

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!