MySQL Bugs: #31168: @@hostname does not replicate

Bug #31168	@@hostname does not replicate
Submitted:	24 Sep 2007 8:17	Modified:	26 Mar 2008 18:08
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.38, 5.0.44, 5.1	OS:	Any
Assigned to:	Sven Sandberg	CPU Architecture:	Any

Description:
In 5.0.38, a new variable @@hostname has been introduced.

This is written to the binlog:

# at 447
#070924 10:12:02 server id 3350  end_log_pos 552        Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1190621522/*!*/;
insert into t  values ( NULL, @@hostname )/*!*/;

Of course, on the slave, @@hostname is being evaluated to the slaves hostname. Table contents will differ.

How to repeat:
root@localhost [(none)]> create database kris;
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)]> use kris
Database changed
root@localhost [kris]> insert into t  values ( NULL, @@hostname );
Query OK, 1 row affected (0.00 sec)

Now check the binlog.

Suggested fix:
Work around the problem as with insert_id and timestamp?

Thank you for a bug report. Verified just as described.

Correct behaviour is that a row inserted on the master using @@hostname should get the same value on the slave.

Wen using statement based replication we will replicate the whole statement "INSERT INTO VALUES(@@hostname, ...)" and thus the variable will be evaluated also on the slave and get a different value. But that is the same as almost all other @@variables when using statement based replication(there is a few that does replicate). See
http://dev.mysql.com/doc/refman/5.0/en/replication-features-variables.html
I does not look like we would fix that in 5.0

With row based logging it should be possible to get the value of @@hostname to be evaluated only on the master. We need to check if that is the case or not.

This bug exists is more cases than @@hostname. The problem exists with all variables except user variables and variables related to auto_increment, last_insert_id, and random seeds (because these are replicated over explicitly).

I will fix the bug in 5.1 (not in 5.0) by tagging statements that depend on variables as "unsafe". Here, "unsafe" means that row-based logging is used instead of statement-based logging in mixed mode.

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/41819

ChangeSet@1.2683, 2008-02-06 20:57:45+01:00, sven@riska.(none) +3 -0
  BUG#31168: @@hostname does not replicate
  Problem: In mixed and statement mode, a query that refers to a system
  variable will use the slave's value when replayed on slave. So if the
  value of a system variable is inserted in a table, the slave will differ
  from the master.
  Fix: mark statements that refer to a system variable as "unsafe", meaning
  they will be replicated by row in mixed mode.

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/42203

ChangeSet@1.2548, 2008-02-13 12:31:35+01:00, sven@riska.(none) +3 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode.

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/42204

ChangeSet@1.2548, 2008-02-13 12:33:33+01:00, sven@riska.(none) +3 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode.

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/42342

ChangeSet@1.2548, 2008-02-15 11:16:48+00:00, sven@riska.(none) +5 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode and produce a warning
  in statement mode.

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/42482

ChangeSet@1.2548, 2008-02-18 13:13:03+01:00, sven@riska.(none) +5 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode and produce a warning
  in statement mode.

OK, I will tag all system variables as unsafe, *except* the following:

@@session.pseudo_thread_id
@@session.foreign_key_checks
@@session.sql_auto_is_null
@@session.unique_checks
@@session.auto_increment_increment
@@session.auto_increment_offset
@@session.character_set_client
@@session.collation_connection, character_set_connection
@@session.collation_server, character_set_server
@@session.time_zone
@@session.lc_time_names
@@session.collation_database, character_set_database
@@session.RAND_SEED1
@@session.RAND_SEED2
@@session.timestamp
@@session.last_insert_id

All these are already written to the binlog in various ways, and replicated
to the slave correctly. Not all are currently printed by mysqlbinlog if they differ from their default values, but my patch will fix that (that's BUG#34732).

You may think that the following variables are replicated, but they are actually not:

@@session.sql_mode - all bits except NO_DIR_IN_CREATE are replicated. So a query using the value of this variable may see another value on the slave.

@@insert_id - mysqlbinlog will set the value of this explicitly, even if it has no value on the master. So statements that refer explicitly to this are unsafe to log. (However, statements that use it *implicitly* by inserting NULL in an auto_increment column are safe. Such statements do not give a warning either.)

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/43061

ChangeSet@1.2535, 2008-02-27 15:44:25+01:00, sven@riska.(none) +25 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode and produce a warning
  in statement mode.

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/43105

ChangeSet@1.2535, 2008-02-27 20:36:32+01:00, sven@riska.(none) +25 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode and produce a warning
  in statement mode. There are some exceptions: some variables are actually
  replicated. Those should *not* be marked as unsafe.
  BUG#34732: mysqlbinlog does not print default values for auto_increment variables
  Problem: mysqlbinlog does not print default values for some variables,
  including auto_increment_increment and others. So if a client executing
  the output of mysqlbinlog has different default values, replication will
  be wrong.
  Fix: Always print default values for all variables that are replicated.
  I need to fix the two bugs at the same time, because the test cases would
  fail if I only fixed one of them.

The last patch is just like the one before, except there was a small bug in mysql-test/include/diff_tables.inc which has been fixed. Also, commit comments are better.

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/43408

ChangeSet@1.2548, 2008-03-04 18:19:08+01:00, sven@riska.(none) +25 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode and produce a warning
  in statement mode. There are some exceptions: some variables are actually
  replicated. Those should *not* be marked as unsafe.
  BUG#34732: mysqlbinlog does not print default values for auto_increment variables
  Problem: mysqlbinlog does not print default values for some variables,
  including auto_increment_increment and others. So if a client executing
  the output of mysqlbinlog has different default values, replication will
  be wrong.
  Fix: Always print default values for all variables that are replicated.
  I need to fix the two bugs at the same time, because the test cases would
  fail if I only fixed one of them.

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/43477

ChangeSet@1.2559, 2008-03-05 17:46:40+01:00, sven@riska.(none) +25 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode and produce a warning
  in statement mode. There are some exceptions: some variables are actually
  replicated. Those should *not* be marked as unsafe.
  BUG#34732: mysqlbinlog does not print default values for auto_increment variables
  Problem: mysqlbinlog does not print default values for some variables,
  including auto_increment_increment and others. So if a client executing
  the output of mysqlbinlog has different default values, replication will
  be wrong.
  Fix: Always print default values for all variables that are replicated.
  I need to fix the two bugs at the same time, because the test cases would
  fail if I only fixed one of them.

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/43581

ChangeSet@1.2562, 2008-03-07 13:59:36+01:00, sven@riska.(none) +27 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode and produce a warning
  in statement mode. There are some exceptions: some variables are actually
  replicated. Those should *not* be marked as unsafe.
  BUG#34732: mysqlbinlog does not print default values for auto_increment variables
  Problem: mysqlbinlog does not print default values for some variables,
  including auto_increment_increment and others. So if a client executing
  the output of mysqlbinlog has different default values, replication will
  be wrong.
  Fix: Always print default values for all variables that are replicated.
  I need to fix the two bugs at the same time, because the test cases would
  fail if I only fixed one of them.

Pushed into 5.1.24-rc

Documented bugfix in 5.1.24 changelog as follows:
      
        When running the server with
        --binlog-format=MIXED or
        --binlog-format=STATEMENT, a query that
        referred to a system variable used the slave&apos;s value when
        replayed on the slave. This meant that, if the value of a system
        variable was inserted into a table, the slave differed from the
        master. Now, statements that refer to a system variable are
        marked as unsafe, which means that:
        
              * When the server is using
              --binlog-format=MIXED, the row-based
              format is used automatically to replicate these
              statements.
            
              * When the server is using
              --binlog-format=STATEMENT, these
              statements produce a warning.

Also noted change in binary-log-mixed.

Pushed into 6.0.5-alpha

Also documented in the 5.1.23-ndb-6.3.11 and 6.0.5 changelogs.