replication block when network down about go-mysql

[iyidan]

I tested in my local machine， mysql server does not close the binlog dump connection even if the underlying tcp connection is lost(or in close_wait state).
until there is a new event to be send, mysql will check and clean the bad connections
Seemingly no good solution for this
Add tcp keepalive can avoid unplug the network cable situation but has no effect on the removal of bad connections

[siddontang]

Hi @3manuek

The HEARTBEAT_EVENT is sent by the master periodically. We can add MASTER_HEARTBEAT_PERIOD configuration.
If set (>0, maybe should better > the time configured in MySQL), we will set read timeout for the connection, and we will retry after the connection is closed.

[siddontang]

Hi @iampastor

I think we can add a read timeout here. But if the connection is good and we don't receive any binlog from MySQL after the timeout, the connection will be closed too and go-mysql will reconnect it.
Another way is to update the TCP heartbeat interval parameters with sysctrl.

[iampastor]

@siddontang ye, this is what I did, adding a read timeout, but I think this is not a very good solution, Does mysql replication have any heart beat message or other keep alive mechanism so that we can check whether the connection is bad?

[siddontang]

@iampastor
MySQL replication has no heartbeat message, so I can't figure out other ways.

[iampastor]

@siddontang
ok, I see. thanks a lot. I will use time out to check the connection.

[siddontang]

Thanks @iampastor
You can send me a PR.

[3manuek]

@iampastor You can keep the coordinates, retry the connection after timeout and set a limit to the loop.

@siddontang I see that you can enable heartbeat by using semi-sync through Slave_heartbeat_period, don't know how the underlying protocol works tho :( .

[siddontang]

Hi @3manuek , I don't find any protocol document on how to support Slave_heartbeat_period 😭

[3manuek]

Yeah, the only entry on the docs is this one. So, I dug into the Binlog_sender::send_heartbeat_event at sql/rpl_binlog_sender.cc and I see that it is just an "empty" event (it only has the checksum if enabled).

But mostly it just sets the header and nothing more:

 /* Timestamp field */
  int4store(header, 0);
  header[EVENT_TYPE_OFFSET] = binary_log::HEARTBEAT_LOG_EVENT;
  int4store(header + SERVER_ID_OFFSET, server_id);
  int4store(header + EVENT_LEN_OFFSET, event_len);
  int4store(header + LOG_POS_OFFSET, static_cast<uint32>(log_pos));
  int2store(header + FLAGS_OFFSET, 0);

So dealing with it shouldn't be more issue as it is a generic event \o/

The tricky part I guess will be how to deal with the timeout tho. The MASTER_HEARTBEAT_PERIOD can be set at replication.BinlogSyncerConfig, but a timer should be set at the beginning of the streamer and reset on each heartbeat event. Seems like it can be handled manually outside the library by just providing the necessary interface to handle the event (it can be up to the user on how to deal with it).

[picago]

just use "SELECT 'x'" to keep the coordinates?

[lkzz]

if client.conn is closed periodically，connection maybe not close on master.

issue like this: alibaba/canal#139

[siddontang]

Hi @lkpjj

Can mysql keepalive not close the tombstone connection? Seem this is the problem for all client connections.

[iyidan]

How about TCP keepAlive ?

Like this:

Add a extra function with a tcp-keep-alive paramter in package client/conn.go

func ConnectWithKeepAlive(keepd time.Duration) {
    // ...
    // ... same as Connect() method

    conn, err := net.DialTimeout(proto, addr, 10*time.Second)
    if err != nil {
        return nil, errors.Trace(err)
    }

    if tcpConn, ok :=conn.(*net.TCPConn); ok && keepd > 0 {
        err = tcpConn.SetKeepAlive(true)
        if err != nil {
            return nil, errors.Trace(err)
        }
        err = tcpConn.SetKeepAlivePeriod(keepd)
        if err != nil {
            return nil, errors.Trace(err)
        }
    }

    c.Conn = packet.NewConn(conn)

    // ... same as Connect() method
    // ...
}

And then use it in package replication/binlogsyncer.go?
(maybe add a option into the BinlogSyncerConfig struct)

[siddontang]

@iyidan

We can't do it directly, in some cases, MySQL will not send any events but we can't close the connection.

#82 (comment) may work but I have no time to verify it now yet.

[iyidan]

@siddontang
I mean, use TCP‘s KeepAlive feature (layer 4) to deal with the unplug the network cable situation.
Even if mysql no event to send, tcp will keep the heartbeat between client and server.

So, the connection will not closed until the server crashed or tcp loss the FIN segment.

[siddontang]

Hi @iyidan

There may be another problem if we close the connection explicitly, see alibaba/canal#139

[svaroqui]

Hi Thanks for the siddon galaxy tools,

The replication protocol as been fully describe here
https://mariadb.com/kb/en/library/replication-protocol/

/Svar