MySQL Bugs: #66708: CREATE / DROP SERVER statements are not written to the binary log

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

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;

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.

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.

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.

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.