Automatic routing to the primary/standby tenants

Background information

In OceanBase Database V2.x and V3.x, disaster recovery is implemented based on the cluster-level primary/standby mode. A primary/standby cluster pair is identified by cluster_name. By default, you log in to the primary cluster (cluster_id = 0). If the login connection string carries cluster_id, it indicates that you are logging in to the primary/standby clusters. The primary/standby relationship is maintained in OceanBase Cloud Platform (OCP).

In OceanBase Database V4.1.0 and later, disaster recovery is implemented based on the tenant-level primary/standby mode. PRIMARY and STANDBY roles are assigned to tenants rather than clusters. Clusters are used only for tenant management and a cluster is uniquely identified by the cluster name. Two different tenants can be in a primary/standby relationship. The primary/standby relationships of tenants are no longer maintained in databases, and the primary and standby tenants in databases do not record the identities of each other. The primary/standby relationships of tenants are maintained in OCP instead. To enable automatic routing to primary/standby tenants, OceanBase Database introduces the concept of service. A service can serve multiple tenants in the same cluster or across clusters. When you use OceanBase Database Proxy (ODP) to connect to a database, you can specify the service name to route the connection to the primary tenant.

Technical mechanism

The following figure shows the basic framework for the feature of automatic routing to the primary/standby tenants in OceanBase Database. If you use a specific connection string to log in to ODP, ODP determines that you are logging in to the primary/standby tenants, obtains the tenant information from OCP, automatically routes SQL requests to the primary tenant, and forwards the SQL requests to the corresponding OBServer node. The OBServer node checks the tenant information. If the check is passed, ODP establishes a connection with the OBServer node.

Feature description

Service name

1. Create a service name.

   When you create a primary/standby tenant pair on the GUI of the OCP console, you can create a service name to uniquely identify the entry to the primary/standby tenants. For more information about the service name, see Manage service names.

2. View information about the tenants associated with the service name.

   Log in to the sys tenant and execute the following statement to view all tenants in the cluster that are associated with the service name:

   SELECT t.tenant_id, t.tenant_name 
       FROM oceanbase.CDB_OB_SERVICES AS s JOIN DBA_OB_TENANTS AS t 
       ON s.tenant_id = t.tenant_id WHERE s.service_name = "xxxx";
   

   If OCP manages multiple OceanBase clusters, you need to run this command for each cluster.

3. Log in to the primary/standby tenants by specifying the service name.

   Verify that the same service name is specified for the primary/standby tenants and that the clusters to which the primary/standby tenants belong are managed by OCP and associated with the same ODP cluster. Then, when you connect to the database through ODP, you can specify the service name for automatic routing to the primary/standby tenants. A sample connection string is as follows:

   obclient -h<IP> -P<PORT> -u<USER_NAME>@SERVICE:<SERVICE_NAME> -p
   

   In the connection string, SERVICE: is a keyword and must be set to the service name of the tenant to log in to. <IP> and <PORT> must be replaced with the IP address and port number of ODP.

   Notice

   If you use the username SERVICE (case-insensitive) to log in to an OceanBase cluster from ODP, ODP determines that you are logging in to the primary/standby tenants, and uses the cluster name specified by cluster_name as the service name of the tenant to log in to.

   Note

   ODP V4.3.1 BP3 and later V4.3.1.x versions as well as V4.3.3 and later allow you to specify service names in the <USER_NAME>@SERVICE#<SERVICE_NAME> or <SERVICE_NAME>-SERVICE-<USER_NAME> format.

   Assume that the IP address and port number of ODP are 10.10.10.1 and 2883 respectively, and that the service name is test. The connection string is as follows:

   obclient -h10.10.10.1 -P2883 -uroot@SERVICE:test
   # or
   obclient -h10.10.10.1 -P2883 -uroot@SERVICE#test
   # or
   obclient -h10.10.10.1 -P2883 -utest-SERVICE-root
   

   After you log in by using the service name, ODP automatically routes the request to the primary tenant associated with the service name. You can associate one service name with multiple tenants. When the database is being upgraded or migrated, service name-based routing ensures seamless application connection. The service name remains unchanged even if the backend database changes.

ODP routing

After you configure a service name for login, ODP can implement automatic routing to the primary tenant. The working mechanism is described as follows:

• Routing retries: When a switchover or failover is performed at the backend, ODP automatically retries to connect to available tenants based on the negotiated error codes. Retries across clusters are supported. If ODP retries to connect to the primary tenant, no error is returned, implementing routing switching imperceptible to the business system.

  For the following types of SQL requests, ODP does not support tenant switching or retries but returns an error code of OceanBase Database:

  • Intra-transaction routing: ODP does not perform tenant switching but still forwards the request to the tenant that starts the transaction.

  • Temporary table-based routing: ODP forwards the request to the node that processes the previous request.

  • Request relying on the execution result of the previous SQL request: A query with functions such as found_rows() and row_count() must be forwarded to the node that processes the previous request.

  • Data import by using LOAD FILE: Tenant switching is not supported.

  • Cursor/Piece-based routing: When the client uses cursors or pieces to obtain or upload data, all requests are forcibly routed to the same node.

• Automatic primary tenant detection: When a routing retry occurs, it means that the cached primary tenant information is incorrect. ODP obtains the tenant role of the corresponding OBServer node based on the tenant list, finds the primary tenant, and updates the cached information.

• Periodic update: ODP updates the routing information of service names at an interval specified by config_server_refresh_interval, which is 60s by default. This ensures that the cached information does not lag too much behind the current situation.

• High availability: After you log in with a service name, ODP writes the cached information to the disk and still provides automatic routing when OCP is unavailable.

O&M

You can configure the service name feature by using the following parameters:

• enable_standby: specifies whether to enable automatic routing to the primary/standby tenants. The default value is True. If you set the parameter to False, ODP does not use the service name for routing.

• config_server_refresh_interval: specifies the time interval for ODP to obtain config server information from OCP. The default value is 60s. Service name information is also obtained based on this time interval.

When an issue related to the primary/standby tenants occurs, you can check the cached information of the tenants associated with the service name or the error code for troubleshooting.

• For login with a service name, a cache file named obproxy_service_name_info.json is generated in the etc directory under the installation directory of ODP. You can check this file to verify whether the information obtained by ODP based on the service name is correct.

  Note

  In ODP, service names are stored in lowercase.

• The error code OB_NOT_PRIMARY_TENANT(-4782) indicates that the SQL request has been forwarded to the standby tenant and a switchover may have been performed.

• The error code OB_SERVICE_NAME_NOT_FOUND(-4780) indicates that the service name does not exist in OceanBase Database and the information cached by ODP may have been outdated.