MySQL Bugs: #45848: Semisynchronous replication internals are visible in SHOW PROCESSLIST and logs

Bug #45848	Semisynchronous replication internals are visible in SHOW PROCESSLIST and logs
Submitted:	30 Jun 2009 9:52	Modified:	12 Nov 2009 12:25
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.4	OS:	Any
Assigned to:	Zhenxing He	CPU Architecture:	Any

Description:
The internal workings of semisynchronous replication have certain externally visible manifestations, which may fill up query logs or be confusing or inconvenient to the user:

A. Internal semisynchronous replication messages are dumped into the master query log, with lines such as:

SET SESSION rpl_semi_sync_master_reply_log_file_pos='1162453:master-bin.000001'

this is going to cause the query log to grow at up to twice the normal speed, since each query effectively produces two entries in the log.

B. SHOW PROCESSLIST shows a new connection, which is what executes the SET SESSION rpl_semi_sync_master_reply_log_file_pos commands. Examination with netstat shows that this is a connection coming from the slave.  Unlike the other master/slave connections, however, which are at least partially recognizable as belonging to replication (due to status messages such as "Slave has read all relay log; waiting for the slave I/O thread to update it"), this connection is entirely anonymous.

How to repeat:
Use semisynchronous replication and run a few queries. Examine SHOW PROCESSLIST and the query log.

On Tue, Jul 14, 2009 at 04:38:27PM +0800, He Zhenxing wrote:
> I think one way to fix this is to use the old method, which used the
> connection that slave IO thread used to retrieve binlog events to
> send reply to the semi-sync master.  This method was rejected
> because of the fear that it would make the semi-sync plugin
> dependent on the net_flush/my_net_write/my_net_read interfaces that
> are not public APIs (originally semi-sync was designed to only use
> the semi-sync replication interfaces and the public APIs), but since
> now the plug-ins are integrated with the server code, there should
> be no problem to uses these internal interfaces in the semi-sync
> plugins.

I'm ok with reverting to the old interface as suggested by Zhenxing.
If no-one has better suggestion, then go ahead and implement that.

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

2827 He Zhenxing	2009-07-30
      BUG#45848 Semisynchronous replication internals are visible in SHOW PROCESSLIST and logs
      
      Semi-sync uses an extra connection from slave to master to send
      replies, this is a normal client connection, and used a normal
      SET query to set the reply information on master, which is visible
      to user and may cause some confusion and complaining.
      
      This problem is fixed by reverting to the old method of sending
      reply by using the same connection that is used to send binlog.

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

2831 He Zhenxing	2009-08-17
      BUG#45848 Semisynchronous replication internals are visible in SHOW PROCESSLIST and logs
      
      Semi-sync uses an extra connection from slave to master to send
      replies, this is a normal client connection, and used a normal
      SET query to set the reply information on master, which is visible
      to user and may cause some confusion and complaining.
      
      This problem is fixed by reverting to the old method of sending
      reply by using the same connection that is used to send binlog.

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

2831 He Zhenxing	2009-08-28
      BUG#45848 Semisynchronous replication internals are visible in SHOW PROCESSLIST and logs
      
      Semi-sync uses an extra connection from slave to master to send
      replies, this is a normal client connection, and used a normal
      SET query to set the reply information on master, which is visible
      to user and may cause some confusion and complaining.
      
      This problem is fixed by reverting to the old method of sending
      reply by using the same connection that is used to send binlog.
     @ plugin/semisync/semisync.h
        Add consts for reply packet layout
     @ plugin/semisync/semisync_master.cc
        Add function to read slave reply via net
        Remove function to read slave reply via session variable
     @ plugin/semisync/semisync_master.h
        Add function to read slave reply via net
        Remove function to read slave reply via session variable
     @ plugin/semisync/semisync_master_plugin.cc
        Remove session variable for slave reply
        Read slave reply after sending event via net
     @ plugin/semisync/semisync_slave.cc
        Send slave reply via net instead of using session variable
     @ plugin/semisync/semisync_slave.h
        Send slave reply via net instead of using session variable
     @ plugin/semisync/semisync_slave_plugin.cc
        Send slave reply via net instead of using session variable

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

2831 He Zhenxing	2009-09-05
      BUG#45848 Semisynchronous replication internals are visible in SHOW PROCESSLIST and logs
      
      Semi-sync uses an extra connection from slave to master to send
      replies, this is a normal client connection, and used a normal
      SET query to set the reply information on master, which is visible
      to user and may cause some confusion and complaining.
      
      This problem is fixed by reverting to the old method of sending
      reply by using the same connection that is used to send binlog.
     @ mysql-test/suite/rpl/r/rpl_semi_sync.result
        use 'a' as column name for table t1 in all cases
        add test case for BUG#45848, check that no extra connection on master for semi-sync slave
     @ mysql-test/suite/rpl/t/rpl_semi_sync.test
        Update result files
     @ plugin/semisync/semisync.h
        Add consts for reply packet layout
     @ plugin/semisync/semisync_master.cc
        Add function to read slave reply via net
        Remove function to read slave reply via session variable
     @ plugin/semisync/semisync_master.h
        Add function to read slave reply via net
        Remove function to read slave reply via session variable
     @ plugin/semisync/semisync_master_plugin.cc
        Remove session variable for slave reply
        Read slave reply after sending event via net
        Assume dump request from semi-snc slave as a reply for the log name and  position it requests
     @ plugin/semisync/semisync_slave.cc
        Send slave reply via net instead of using session variable
     @ plugin/semisync/semisync_slave.h
        Send slave reply via net instead of using session variable
     @ plugin/semisync/semisync_slave_plugin.cc
        Remove code for creating the extra connection for reply
        Send slave reply via net instead of using session variable

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

2831 He Zhenxing	2009-09-19
      BUG#45848 Semisynchronous replication internals are visible in SHOW PROCESSLIST and logs
      
      Semi-sync uses an extra connection from slave to master to send
      replies, this is a normal client connection, and used a normal
      SET query to set the reply information on master, which is visible
      to user and may cause some confusion and complaining.
      
      This problem is fixed by using the method of sending reply by
      using the same connection that is used by master dump thread to
      send binlog to slave. Since now the semi-sync plugins are integrated
      with the server code, it is not a problem to use the internal net
      interfaces to do this.
      
      The master dump thread will mark the event requires a reply and
      wait for the reply when the event just sent is the last event
      of a transaction and semi-sync status is ON; And the slave will
      send a reply to master when it received such an event that requires
      a reply.
     @ mysql-test/suite/rpl/r/rpl_semi_sync.result
        update result file
     @ mysql-test/suite/rpl/t/rpl_semi_sync.test
        use 'a' as column name for table t1 in all cases add test case for BUG#45848, check that no extra connection on master for semi-sync slave
        Add some comments before some blocks to explain what they test
     @ plugin/semisync/semisync.h
        Add consts for reply packet layout
     @ plugin/semisync/semisync_master.cc
        Add function to read slave reply via net
        Remove function to read slave reply via session variable
     @ plugin/semisync/semisync_master.h
        Add function to read slave reply via net
        Remove function to read slave reply via session variable
     @ plugin/semisync/semisync_master_plugin.cc
        Remove session variable for slave reply
        Read slave reply after sending event via net
        Assume dump request from semi-snc slave as a reply for the log name and  position it requests
     @ plugin/semisync/semisync_slave.cc
        Send slave reply via net instead of using session variable
     @ plugin/semisync/semisync_slave.h
        Send slave reply via net instead of using session variable
     @ plugin/semisync/semisync_slave_plugin.cc
        Remove code for creating the extra connection for reply
        Send slave reply via net instead of using session variable

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

2836 He Zhenxing	2009-09-26 [merge]
      Merge BUG#45848 to 6.0-codebase-bugfixing

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

2836 He Zhenxing	2009-09-26 [merge]
      Merge BUG#45848 to 6.0-codebase-bugfixing

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

3626 He Zhenxing	2009-09-30
      BUG#45848 Semisynchronous replication internals are visible in SHOW PROCESSLIST and logs
      
      Semi-sync uses an extra connection from slave to master to send
      replies, this is a normal client connection, and used a normal
      SET query to set the reply information on master, which is visible
      to user and may cause some confusion and complaining.
      
      This problem is fixed by using the method of sending reply by
      using the same connection that is used by master dump thread to
      send binlog to slave. Since now the semi-sync plugins are integrated
      with the server code, it is not a problem to use the internal net
      interfaces to do this.
      
      The master dump thread will mark the event requires a reply and
      wait for the reply when the event just sent is the last event
      of a transaction and semi-sync status is ON; And the slave will
      send a reply to master when it received such an event that requires
      a reply.
     @ mysql-test/suite/rpl/r/rpl_semi_sync.result
        update result file
     @ mysql-test/suite/rpl/t/rpl_semi_sync.test
        use 'a' as column name for table t1 in all cases add test case for BUG#45848, check that no extra connection on master for semi-sync slave
        Add some comments before some blocks to explain what they test
     @ plugin/semisync/semisync.h
        Add consts for reply packet layout
     @ plugin/semisync/semisync_master.cc
        Add function to read slave reply via net
        Remove function to read slave reply via session variable
     @ plugin/semisync/semisync_master.h
        Add function to read slave reply via net
        Remove function to read slave reply via session variable
     @ plugin/semisync/semisync_master_plugin.cc
        Remove session variable for slave reply
        Read slave reply after sending event via net
        Assume dump request from semi-snc slave as a reply for the log name and  position it requests
     @ plugin/semisync/semisync_slave.cc
        Send slave reply via net instead of using session variable
     @ plugin/semisync/semisync_slave.h
        Send slave reply via net instead of using session variable
     @ plugin/semisync/semisync_slave_plugin.cc
        Remove code for creating the extra connection for reply
        Send slave reply via net instead of using session variable

Pushed into 6.0.14-alpha (revid:alik@ibmvm-20091009083208-0o0f0i9w1sq3c1kn) (version source revid:zhenxing.he@sun.com-20090930035516-dcvgi16xnkoqhf85) (merge vers: 6.0.14-alpha) (pib:12)

Documented bugfix in the 6.0.14 changelog as follows:

        Semi-synchronous replication used an extra connection from slave
        to master to send replies. This was a regular client connection,
        and used a normal SET statement to set the
        reply information on master. This was visible to users and had
        the potential to add undesired extra output to the query log,
        the output of SHOW PROCESSLIST, or both.

        We have fixed this issue by reverting to the previous method of
        sending replies to the master via the same connection that is
        used to send binary logging information.

Set status = NDI, waiting for 5.4 push.

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

3114 He Zhenxing	2009-10-12
      Backport BUG#45848 Semisynchronous replication internals are visible in SHOW PROCESSLIST and logs
      
      Semi-sync uses an extra connection from slave to master to send
      replies, this is a normal client connection, and used a normal
      SET query to set the reply information on master, which is visible
      to user and may cause some confusion and complaining.
      
      This problem is fixed by using the method of sending reply by
      using the same connection that is used by master dump thread to
      send binlog to slave. Since now the semi-sync plugins are integrated
      with the server code, it is not a problem to use the internal net
      interfaces to do this.
      
      The master dump thread will mark the event requires a reply and
      wait for the reply when the event just sent is the last event
      of a transaction and semi-sync status is ON; And the slave will
      send a reply to master when it received such an event that requires
      a reply.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091027094604-9p7kplu1vd2cvcju) (version source revid:zhenxing.he@sun.com-20091026140226-uhnqejkyqx1aeilc) (merge vers: 6.0.14-alpha) (pib:13)

Already documented in the 6.0.14 changelog; re-closing.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091027095744-rf45u3x3q5d1f5y0) (merge vers: 5.5.0-beta) (pib:13)

Also documented bugfix in the 5.5.0 changelog; closed.

Note: This patch also removed the rpl_semi_sync_master_reply_log_file_pos system variable.

Removed 5.5.0 changelog entry. In 5.5, semisync replication first appears in 5.5.0, so this bug affects no 5.5.x releases.