Bug #25705 Federated: CREATE SERVER logging

Description:
The CREATE SERVER statement should appear in binlog
if I start the remote server with --log-bin.

How to repeat:
I made sure that the statement appears on neither
the local nor the remote server, but you can verify
with just one server and no federated use.

Start server with --log-bin so there will be a binlog.

On client, say:
mysql> create server ww foreign data wrapper mysql options (user 'Remote', Host '192.168.1.103', database 'test');
Query OK, 1 row affected (0.00 sec)

Use mysqlbinlog to check if CREATE SERVER statement appears.

Thank you for the bug report.

Hi, 
  while dealing with BUG#13684 I have found that the create server statement is actually binlogged when using *row* format, but not on *mixed* nor *statement*. You can verify this by doing the following on the latest 5.1 tree (I used revno: 2710,revision-id: joerg@mysql.com-20090115181125-29qdo615v9vkla0t):

mysql> SET SESSION BINLOG_FORMAT='statement';
Query OK, 0 rows affected (0.00 sec)

mysql> create server s_statement foreign data wrapper mysql options (user 'Remote', Host '192.168.1.103', database 'test');
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                        |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         106 | Server ver: 5.1.32-debug-log, Binlog ver: 4 | 
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> SET SESSION BINLOG_FORMAT='mixed';
Query OK, 0 rows affected (0.00 sec)

mysql> create server s_mixed foreign data wrapper mysql options (user 'Remote', Host '192.168.1.103', database 'test');
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                        |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         106 | Server ver: 5.1.32-debug-log, Binlog ver: 4 | 
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION BINLOG_FORMAT='row';
Query OK, 0 rows affected (0.00 sec)

mysql> create server s_row foreign data wrapper mysql options (user 'Remote', Host '192.168.1.103', database 'test');
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                        |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         106 | Server ver: 5.1.32-debug-log, Binlog ver: 4 | 
| mysql-bin.000001 | 106 | Query       |         1 |         170 | BEGIN                                       | 
| mysql-bin.000001 | 170 | Table_map   |         1 |         242 | table_id: 12 (mysql.servers)                | 
| mysql-bin.000001 | 242 | Write_rows  |         1 |         319 | table_id: 12 flags: STMT_END_F              | 
| mysql-bin.000001 | 319 | Query       |         1 |         384 | COMMIT                                      | 
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
5 rows in set (0.00 sec)

Using mysqlbinlog on the resulting binlog file I can check that the event is the actual insert in the mysql.servers table:

../bin/mysqlbinlog -vv mysql-bin.000001

(...)

# at 242
#090119 16:11:23 server id 1  end_log_pos 242 	Table_map: `mysql`.`servers` mapped to number 12
#090119 16:11:23 server id 1  end_log_pos 319 	Write_rows: table id 12 flags: STMT_END_F

BINLOG '
G5h0SRMBAAAASAAAAPIAAAAAAAwAAAAAAAAABW15c3FsAAdzZXJ2ZXJzAAn+/v7+/gP+/v4Q/sD+
wP7A/sD+wP7A/sD+wAAA
G5h0SRcBAAAATQAAAD8BAAAQAAwAAAAAAAEACf//AP4Fc19yb3cNMTkyLjE2OC4xLjEwMwR0ZXN0
BlJlbW90ZQAAAAAAAAVteXNxbAA=
'/*!*/;
### INSERT INTO mysql.servers
### SET
###   @1='s_row' /* STRING(192) meta=65216 nullable=0 is_null=0 */
###   @2='192.168.1.103' /* STRING(192) meta=65216 nullable=0 is_null=0 */
###   @3='test' /* STRING(192) meta=65216 nullable=0 is_null=0 */
###   @4='Remote' /* STRING(192) meta=65216 nullable=0 is_null=0 */
###   @5='' /* STRING(192) meta=65216 nullable=0 is_null=0 */
###   @6=0 /* INT meta=0 nullable=0 is_null=0 */
###   @7='' /* STRING(192) meta=65216 nullable=0 is_null=0 */
###   @8='mysql' /* STRING(192) meta=65216 nullable=0 is_null=0 */
###   @9='' /* STRING(192) meta=65216 nullable=0 is_null=0 */
# at 319
#090119 16:11:23 server id 1  end_log_pos 384 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1232377883/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Regards,
Luís

BUG#13684 is (somewhat) related to this one.

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

2768 Luis Soares	2009-02-09
      BUG#25705: Federated: CREATE SERVER logging
      BUG#13684: SP: DROP PROCEDURE|FUNCTION IF EXISTS not binlogged if routine 
      does not exist
      
      From BUG#25705, one finds that the drop/create server is not binlogged 
      in mixed or statement mode replication. Instead it gets logged only in row
      based replication as rows inserted/deleted into/from mysql.servers. From 
      BUG#13684, one finds that the drop server if exists is not being binlogged 
      also.
      
      This patch addresses these issues by binlogging CREATE/DROP server even if
      the "IF EXISTS" clause is present. Furthermore, it will always log create/drop 
      server as statements as these are referred in the manual to be "DDL":
       * http://dev.mysql.com/doc/refman/5.1/en/drop-server.html
       * http://dev.mysql.com/doc/refman/5.1/en/create-server.html

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

2917 Luis Soares	2009-06-05
      BUG#25705: Federated: CREATE SERVER logging
            
      From BUG#25705, one finds that the CREATE/ALTER/DROP SERVER is not
      binlogged in mixed or statement mode replication. Instead it gets
      logged only in row based replication when rows are
      inserted/updated/deleted into/from mysql.servers. From BUG 13684, one
      finds that the drop server if exists is not being binlogged also.
            
      This patch addresses these issues by binlogging CREATE/ALTER/DROP
      SERVER even if the server does not exist but "IF EXISTS" clause was
      issued. Furthermore, it will always log CREATE/ALTER/DROP server as
      statements.
     @ mysql-test/suite/binlog/t/binlog_create_server.test
        Added test case that performs several checks involving 
        CREATE/ALTER/DROP SERVER binlogging.
     @ sql/sql_parse.cc
        Changed error handling in sql_parse for drop server as now the
        drop_server call may return also errors related to binlogging.
        Also, extends error reporting to handle previous unhandled errors
        returned by drop server.
     @ sql/sql_servers.cc
        Deployed calls to THD::binlog_query in create_server, alter_server
        drop_server functions.

Push to Betony or Celosia unless Staale objects.