Database connection specifications

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

• Set a maximum idle time 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 1 s and configure a reconnection mechanism with a retry interval.

• Make ensure 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 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 value to 30s, set the connection detection or idle timeout, and set the connection recycling mechanism (maximum value: 3600s).

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

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

• Use the Connection.setReadOnly(xx) API 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 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 execute close connection. In OceanBase Database, the entire lifecycle of a single transaction must be completed in 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 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.

For connection examples of common resource pools, see Examples of configuring database connection pools in Best practices for database design. For more information about connecting common development frameworks to OceanBase Database, see Examples of connecting development frameworks to OceanBase Database in Best practices for database design.