Database connection specifications

This topic describes the specifications for establishing database connections in OceanBase Database.

• Set a maximum idle timeout period allowed for a connection in the connection pool.

• For a frontend application connected to the database or database proxy, set the JDBC connection timeout period to 1s and configure a reconnection mechanism with a retry interval.

• Make sure that application logs record the standard OceanBase error codes and information about the connected database, such as the IP address, port, and database username, for the convenience of troubleshooting by the database administrator (DBA).

• For frontend applications with a connection pool, set the initial, minimum, and maximum numbers of connections according to the business requirements. We recommend that you set the timeout period to 30s, set the connection detection or idle timeout period, and set the connection recycling mechanism (maximum value: 3600s).

• Make sure that the .so and .jar library packages used by the application for database connections and the database client version are compatible with the version of the online database server.

• Use Connection.setCatalog(dbname) instead of executing use <dbname> for database switchovers.

• Use Connection.setReadOnly(xx) instead of executing set session transaction readonly to set the read-only attribute.

• Before you execute a single transaction statement (either a single-SQL transaction statement or a multi-SQL transaction statement), you must execute getConnection again and then execute close connection after the execution. Do not run multiple transactions in one process. Run each transaction in the following procedure: execute getConnection, execute a single transaction statement, and then close the connection. In OceanBase Database, the entire lifecycle of a single transaction must be completed on a single server. To be specific, the transaction must be started, executed, and ended on the same server. It is not guaranteed that a transaction initiated on Server A is also valid on Server B. That is, in a Java connector, when the current connection is in a transaction, the next SQL statement can be routed only to the previous server, but not to the optimal server. When the current connection is not in the transaction, the SQL statement can be routed to the optimal server.