MySQL Bugs: #31167: Introduction of @@hostname breaks replication in a ring

Bug #31167	Introduction of @@hostname breaks replication in a ring
Submitted:	24 Sep 2007 8:07	Modified:	25 Oct 2007 1:25
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.38	OS:	Any
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
In 5.0.38 the variable @@hostname has been introduced. This is in itself a compatible change and useful.

The variable is being used in the upgrade script scripts/mysql_system_tables_data.sql.

When running this script on a replication server that is part of a ring, replication breaks because the other server in the ring does not (yet) know about this variable.

How to repeat:
Replicate 

REPLACE INTO tmp_user VALUES (@@hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y
','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);

from a 5.0.38 or higher to any 5.0.36 or lower.

Suggested fix:
It is okay to introduce such variables inside a stable release, but actually using them in upgrade scripts has effects on production such as breaking replication.

- The line in the changelog says "Added the hostname system variable, which the server sets at startup to the server hostname.". No reference to it being used or to the consequences of @@hostname in replication. Does this even replicate?

- Usage of new features in system scripts creates a lot of upgrade issues. It should not be done unless absolutely necessary, and if it is being done, a recommended upgrade procedure and an appropriate heads up should be provided.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34560

ChangeSet@1.2523, 2007-09-26 17:04:41+02:00, msvensson@pilot.(none) +1 -0
  Bug#31167 Introduction of @@hostname breaks replication in a ring
   - Use a local uservariable to avoid @@hostname in binlog

Pushed into 5.1.23-beta

Pushed into 5.0.52

Noted in 5.0.52, 5.1.23 changelogs.

Use of the @@hostname system variable in inserts in
mysql_system_tables_data.sql did not replicate. The workaround is to 
select its value into a user variable (which does replicate) and
insert that.