JDBC (Java Database Connectivity) is a Java API for managing connections to databases, issuing queries and commands, and processing result sets obtained from databases. JDBC, released in 1997 as part of JDK 1.1, was one of the first components developed for the Java persistence layer.

JDBC problem basics

JDBC problem refers to a relatively broad, reflected in the JDBC level of the problem, causing JDBC problems are mainly caused by three aspects: 1, application and application framework problems; 2. JDBC business function problems; 3. Database kernel problems. Problem performance can be divided into three major aspects: 1. Execution error, JDBC exception thrown; 2. Low execution efficiency and abnormal time consumption; 3, features do not support, JDBC has not implemented JDK interface.

JDBC Problem Classification

Methods for locating JDBC problems and solutions

Failed to establish database link

Check that the hostname and port are correct and that the postmaster is positive TCP/IP connections. The client pings the IP address of the server to check whether the network is normal. If the network is abnormal, rectify the network fault first and then check whether the port is correct. If the port is incorrect, change it to a correct port.

FATAL: Invalid username/password,login denied. Check whether the user name and password are correct. Change the password to the correct user name and password.

3, Keyword: No suitable driver found for XXXX check the URL format is correct, measure: To be changed to the correct format gsjdbc4. Jar corresponding JDBC: postgresql: / / host: port/database, gsjdbc200. Corresponding JDBC jar: gaussdb: / / host: port/database.

FATAL: no pg_hba.conf entry for host Check whether remote access permission is configured for the linked CN. Add host all all 0.0.0.0/0 SHA256 to the pg_hba.conf file in the CN file.

Conflit The JDBC JAR package conflicts with the application. Replace gsjdbC4

An exception was thrown. Procedure

1. Keyword: receiveErrorResponse If the error message contains the keyword of minor value, the JDBC error message is received abnormal packets and abnormal information from the kernel. Measures: Collect error messages and log information and send them to the r&d center for analysis.

2. Keyword: Broken pipe, connection reset by peer Possible cause: Network fault, database connection timeout, measure: check the network status, recover the network fault, factors affecting database connection timeout, database parameter session_timeout and LVS.

The column index is out of range possible cause: The result set obtained by The application is inconsistent with The expected value, or The number of columns is inconsistent. Check the database table definition and query SQL to make a correct expectation of the return result set. If the result set has only 3 columns, the index passed in is at most 3.

Performance issues

1. Set loglevel=3 to enable JDBC logging. If the main time is in the processResult stage, it can be divided into two situations: 1) THE JDBC side has been waiting for the packet returned by the kernel, and the waiting time is long; Check method: Check the interval between FE=> Syncr logs and <=BE ParseComplete logs. If the interval is long, the kernel execution is slow. Measures: Kernel personnel need to analyze the cause of slow SQL execution; 2) The result set is too large to be loaded at one time, consuming a lot of time; If <=BE DataRow occurs many times or select count(*) from GSQL, the result set is too large. Action: Set the fetchSize parameter to a small value so that the data is returned in batches and the client responds quickly.

2, If the main time is in the modifyJdbcCall (verify whether the incoming SQL complies with the specification) and createParameterizedQuery (parse the incoming SQL into preparedQuery to obtain the subqueries consisting of SimpleQueries) stages, Check whether the incoming SQL is too long. The application side needs to check whether the incoming SQL can be optimized because JDBC itself cannot optimize the elapsed time.

Functional problems

The implementation of JDBC interface is not yet implemented and implemented. The business is adjusted to use the interface provided by other interfaces.

The JDK does not provide a standard interface. Measures: In theory, the JDK does not provide an interface, JDBC does not support, in practice, you can use the JDBC class public method to obtain part of the process data, in most cases clearly not supported.

A common case

Failed to load the driver

When using JDBC, customers usually use the application framework instead of the Java program to load drivers to establish links. After the configuration is complete, the application framework automatically establishes links. However, the link establishment may fail due to configuration problems, for example: Jar is used, but the Driver is still org.postgresql.Driver uses the url format of JDBC :postgresql. The Driver is loaded by the application framework, so sometimes the Driver path and URL format are fixed. Only basic information such as IP address, port, user, and password can be configured. In addition, if an error occurs, only the log information about the failure to establish a link can be seen, but not the stack.

Solution :(1) check whether the JDBC driver is gsjdbc4.jar or gsjdbc200.jar. If gsjdbc200.jar is used, replace it with gsjdbc4. (2) eliminate JDBC suspicion, write a Java test case, not through the application program architecture, directly load the driver through the Java program, establish links, after eliminating JDBC suspicion to carefully study the application framework, in order to solve the problem.

JDBC JAR package conflict

Jar and postgresql.jar, both of which have the same class name. Gsjdbc4. Jar introduces some other tools, such as Fastjson, due to iam, which conflicts with fastJSON in the application.

Solutions: Jar, which uses a different URL format and driver path than the open source driver. Driver name by the org. Postgresql. Driver is modified to com. Huawei. Gauss200). The JDBC Driver, Url format by the org: postgresql: / / host: port/database instead of JDBC: gaussdb: / / host: port/database, solved the conflict and open-source jar package; We resolved conflicts between JDBC introduced Jars and application introduced Jars by modifying the path of classes in the JAR via Maven shade.


Click to follow, the first time to learn about Huawei cloud fresh technology ~