Configure JDBC (dbcp/c3p0) Data source for Very Low-Throughput System

A ton of posts discuss the high-throughput and high concurrency system, and perhaps you are building a low-throughput system, and you may think it is simple to configure the datasource. Actually if you use dbcp or c3p0 datasource, and settings of datasource is default value, your application will run into exception occasionally, especially after run idle for hours or days.

Problem

If the connection is inactive, the MySQL and Oracle may close the connection. And then if your application receive request from users, and then pick up connection from connection pool, the connection maybe broken connection, and then your application throws IOException.

You may see the trace listed below:

A.       Oracle

Cause: java.sql.SQLRecoverableException: No more data to read from socket

; SQL []; No more data to read from socket; nested exception is java.sql.SQLRecoverableException: No more data to read from socket

B.       Oracle

Cause: java.sql.SQLRecoverableException: IO Error: Broken pipe

; SQL []; IO Error: Broken pipe; nested exception is java.sql.SQLRecoverableException: IO Error: Broken pipe

C.       MySQL

Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was43203 seconds ago.The last packet sent successfully to the server was 43203 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

; SQL []; The last packet successfully received from the server was43203 seconds ago.The last packet sent successfully to the server was 43203 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was43203 seconds ago.The last packet sent successfully to the server was 43203 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

                at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)

…………

Caused by: java.net.SocketException: Broken pipe

                at java.net.SocketOutputStream.socketWrite0(Native Method)

Solution

A:

I suggest you to use c3p0 datasource.  And set maxIdleTime=300.

                <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"

                                destroy-method="close">

                                <property name="driverClass">

                                                <value>${prop.jdbc.driverClassName}</value>

                                </property>

                                <property name="jdbcUrl">

                                                <value>${prop.jdbc.url}</value>

                                </property>

                                 <property name="user"> 

                                                <value>${prop.jdbc.username}</value>

                                 </property>

                                 <property name="password"> 

                                                <value>${prop.jdbc.password}</value>

                                 </property>

                                 <property name="maxIdleTime"> 

                                                <value>300</value>

                                 </property>

                </bean>

B:

If you are using Apache DBCP, I suggest you to upgrade to latest version, and the recommended setting similar to

                <bean id="dataSource_x" class="org.apache.commons.dbcp.BasicDataSource"

                                destroy-method="close">

                                <property name="driverClassName">

                                                <value>${prop.jdbc.driverClassName}</value>

                                </property>

                                <property name="url">

                                                <value>${prop.jdbc.url}</value>

                                </property>

                                 <property name="username"> 

                                                <value>${prop.jdbc.username}</value>

                                 </property>

                                 <property name="password"> 

                                                <value>${prop.jdbc.password}</value>

                                 </property>

                                 <property name="testWhileIdle" value="true"/>

                                 <property name="validationQuery" value="select 1 from dual"/>

                                 <property name="removeAbandoned" value="true"/>

                                 <property name="removeAbandonedTimeout" value="600"/>

                                 <property name="timeBetweenEvictionRunsMillis" value="600000"/>

                </bean>

About Author

Williams Voon, experienced programmer, devote to building distributed system monitoring tool EasyHA.

The differences between checked exception and unchecked exception In Java

If your java program runs into exception, how should you do? You can catch and handle it, or catch and throw it up, or just do not catch it. The way handle exception is depended on the type of exception.

There are two types of java exception: checked exception and unchecked exception.

Unchecked exceptions:

1.        represent defects in the program (bugs) - often invalid arguments passed to a non-private method.
2.        are subclasses of RuntimeException, and are usually implemented using IllegalArgumentException, NullPointerException, or IllegalStateException
3.        a method is not obliged to establish a policy for the unchecked exceptions thrown by its implementation (and they almost always do not do so)
4. 
   

Checked exceptions:

1.        represent invalid conditions in areas outside the immediate control of the program (invalid user input, database problems, network outages, absent files)
2.        a method is obliged to establish a policy for all checked exceptions thrown by its implementation (either pass the checked exception further up the stack, or handle it somehow)

Tips for Java Programmer

If your program runs into RuntimeException, that always indicates that you need to fix the bug. To quote from The Java Programming Language, by Gosling, Arnold, and Holmes: "Unchecked runtime exceptions represent conditions that, generally speaking, reflect errors in your program's logic and cannot be reasonably recovered from at run time."

So if you catch the RuntimeException, please fix the bug ASAP. Perhaps you want to know your programs throw RuntimeException by monitoring tools, you can try the EasyHA.

New Feature of EasyHA Monitoring Tool

The new version of EasyHA will analyze log file of your java program, and detect the unique RuntimeException. And the head of Ops team can check number of bugs by figures and diagrams.