`Connection reset` is reported for a time-consuming statement

Symptom

An error is returned when ODC executes a time-consuming SQL statement

For an SLB connection between ODC and OceanBase Database (such as F5 and SLB), an error is reported when ODC executes a time-consuming SQL statement at a fixed interval (close to the persistent connection time for SLB, which is generally 15 minutes).

The following error message is returned:

Connection reset

An error is returned for OBClient

• The following error message is returned:

  Lost connection to MySQL server during query
  

• Here is an example:

  

Possible causes

Most SLB products, including various hardware and software products, such as F5 and Alibaba Cloud SLB, provide the persistent connection configuration that prevents the internal connection resources of SLB components from being released due to unexpected disconnection of applications.

The ODC server periodically checks the connection availability by using select 1 from dual at a 5 minute interval. If the persistent connection time of the SLB is set to a period shorter than 5 minutes or the execution time of an SQL statement exceeds the persistent connection time, the SLB component disconnects the session.

Solutions

1. Modify the persistent connection configuration of the ODP to ensure that:

   • The persistent connection feature is enabled: client_sock_option_flag_out=2

   • The value of client_tcp_user_timeout is set. We recommend that you set this parameter to 0, or at least a value greater than that of client_tcp_keepintvl.

     Notice

     • To modify the ODP configuration, you must use the root@proxysys account. Otherwise, the modification becomes invalid.
     • You can modify the configuration of an ODP in an ODP cluster managed by OceanBase Cloud Platform (OCP) in OCP.
     • An SLB node determines whether a connection is valid based on the network packets going through the connection. If a connection does not have any network packets within a period (such as 15 minutes), the node determines that the connection is invalid and actively disconnects the connection. If you enable persistent connection for the ODP, it sends a keepalive packet every 5 seconds. In this way, the SLB node considers that the connection is active and will not disconnect the connection.

   The following table describes the parameters for configuring TCP connection keepalive for the ODP.

   Parameter	Description
   client_sock_option_flag_out	The client-side socket option. Valid values: bit 1: NO_DELAY; bit 2: KEEP_ALIVE.
   client_tcp_keepidle	The keepalive duration.
   client_tcp_keepintvl	The interval for sending a keepalive packet.
   client_tcp_keepcnt	The threshold of the unreceived number of keepalive packets for determining a connection as disconnected.
   client_tcp_user_timeout	The timeout period for waiting for the TCP-layer ACK message. If this period expires, ODP disconnects the connection. Notice The value of this parameter must be greater than the value of client_tcp_keepintvl. We recommend that you set this parameter to 0 for ODP. Otherwise, the connection may be interrupted by ODP in poor network conditions (such as those with packet loss).

2. Use the CLI tool OBClient to view the current configuration and connect to the ODP by using the root@proxysys account.

   -- View the current configuration.
   show proxyconfig like 'client_sock_option_flag_out';
   show proxyconfig like 'client_tcp%';
   

3. We recommend that you use the following configuration:

   alter proxyconfig set client_sock_option_flag_out=2
   alter proxyconfig set client_tcp_keepidle=5
   alter proxyconfig set client_tcp_keepintvl=5
   alter proxyconfig set client_tcp_keepcnt=5
   alter proxyconfig set client_tcp_user_timeout=0