Bug #66708 CREATE / DROP SERVER statements are not written to the binary log
Submitted: 5 Sep 2012 16:17 Modified: 7 Sep 2012 12:13
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.62, 5.5.27, 5.6.6 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[5 Sep 2012 16:17] Elena Stepanova
Description:
I tried to search the manual and google, but couldn't find it being intentional or already filed.

In 5.5 and 5.6, CREATE / DROP SERVER statements are not written to the binary log at all, and neither are the changes to mysql.servers table. In 5.1, in ROW binlog format, mysql.servers table changes are logged (which is not good, and was apparently fixed in 5.5+, but the statement replication, usual for DDL, wasn't enabled).

Test output from 5.5:

SET binlog_format = STATEMENT;
CREATE SERVER s1 FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
DROP SERVER s1;
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       107     Server ver: 5.5.27-log, Binlog ver: 4
SET binlog_format = MIXED;
CREATE SERVER s2 FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
DROP SERVER s2;
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       107     Server ver: 5.5.27-log, Binlog ver: 4
SET binlog_format = ROW;
CREATE SERVER s3 FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
DROP SERVER s3;
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       107     Server ver: 5.5.27-log, Binlog ver: 4

Test output from 5.1:

SET binlog_format = STATEMENT;
CREATE SERVER s1 FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
DROP SERVER s1;
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       106     Server ver: 5.1.65-log, Binlog ver: 4
SET binlog_format = MIXED;
CREATE SERVER s2 FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
DROP SERVER s2;
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       106     Server ver: 5.1.65-log, Binlog ver: 4
SET binlog_format = ROW;
CREATE SERVER s3 FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
DROP SERVER s3;
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       106     Server ver: 5.1.65-log, Binlog ver: 4
master-bin.000001       106     Query   1       174     BEGIN
master-bin.000001       174     Table_map       1       246     table_id: 12 (mysql.servers)
master-bin.000001       246     Write_rows      1       320     table_id: 12 flags: STMT_END_F
master-bin.000001       320     Query   1       389     COMMIT
master-bin.000001       389     Query   1       457     BEGIN
master-bin.000001       457     Table_map       1       529     table_id: 12 (mysql.servers)
master-bin.000001       529     Delete_rows     1       603     table_id: 12 flags: STMT_END_F
master-bin.000001       603     Query   1       672     COMMIT

How to repeat:
--source include/have_log_bin.inc

SET binlog_format = STATEMENT;

CREATE SERVER s1 FOREIGN DATA WRAPPER mysql
  OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
DROP SERVER s1;

SHOW BINLOG EVENTS;

SET binlog_format = MIXED;

CREATE SERVER s2 FOREIGN DATA WRAPPER mysql
  OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
DROP SERVER s2;

SHOW BINLOG EVENTS;

SET binlog_format = ROW;

CREATE SERVER s3 FOREIGN DATA WRAPPER mysql
  OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
DROP SERVER s3;

SHOW BINLOG EVENTS;
[5 Sep 2012 17:09] Sveta Smirnova
Thank you for the report.

This was discussed in bug #25705: it was fixed, but then considered to not push the fix.

But I still verify it as documentation bug, because this decision was not reflected at all. This should be either in federated or replication limitation part.
[5 Sep 2012 17:27] Elena Stepanova
Hi Sveta,

Public comments in the bug #25705 finish with 'Push to Betony or Celosia unless Staale objects' -- apparently there was an objection, but it isn't visible. 
Would it be possible to make the reasoning public, either in that bug, or in this one, or in the docs? My best guess is that it was argued to be useless for replication; however, the binary log is used not only for replication, but also as a backup, and an attempt to restore its contents will fail in absence of the recorded CREATE statement. If there were other considerations, it would be interesting to know them.
[7 Sep 2012 12:13] Jon Stephens
In 5.1, these statements are written to the binlog only when binlog_format=STATEMENT (and not ROW or MIXED). In 5.5+, they are never logged. We do not anticipate this this behaviour will change.

Updated CREATE|ALTER|DROP SERVER Syntax sections of 5.1+ Manual, and added new subsection to Replication Issues with this info.

Closed.
[11 Sep 2012 16:01] Jon Stephens
Preceding should have had, "In 5.1, these statements are written to the binlog only when binlog_format=ROW (and not STATEMENT or MIXED)."

The 5.1 Manual also contained the error and has been corrected.