Cause of the problem

show global variables like '%wait_timeout';    -- You can check the database idle wait time, default 8 hours, maximum 2147483, close to 24 days
Copy the code

That is, a program is connected to the database. If the time is exceeded, the connection will be disconnected and the following information may occur:

1.com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.

2.java.net.SocketException: Software caused connection abort: recv failed

3. Other errors

The solution

  1. If you are older than MySQL5, you need to change the URL in the connection pool configuration and add autoReconnect=true

  2. For versions later than MySQL5, modify the my.cnf(or my.ini) file and add it after [mysqld]

wait_timeout = 1814400
interactive_timeout = 1814400 -- Show GLOBAL VARIABLEs like 'wait_timeout' to check wait_timeout
Copy the code
  1. Using SQL:set global wait_timeout=1814400To modify the global variable, if only to modify the idle wait time of the current session, you can use session instead of global

None of the above methods is the best solution, because the database is still reporting errors after the maximum time set, so the recommended use of C3P0 connection pool, can be very good

  1. C3p0 configuration (in seconds) :
<! Mysql: show processList number of connection records = minimum number of connections in the connection pool +3(no connection) ->
<property name="hibernate.conne ction.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<! -- The minimum number of connections in the pool. Default: 3-->
<property name="hibernate.c3p0.min_size">4</property>
<! -- Maximum number of connections in the pool. Default: 15-->
<property name="hibernate.c3p0.max_size">30</property>
<! -- Connection timeout time -->
<property name="hibernate.c3p0.timeout">1800</property>
<! -- How many seconds does it take to check whether a connection is working properly? This value must be smaller than MySQL's wait_timeout (default: 8 hours), which defaults to 0.
<property name="hibernate.c3p0.idle_test_period">20</property>
<! When the pool runs out of connections, the number of connections to be added at once. Default is 3-->
<property name="hibernate.c3p0.acquire_increment">1</property>
<! Check the connection -->
<property name="hibernate.c3p0.validate">false</property>
<! -- Maximum idle time. If not used within 60 seconds, the connection will be discarded. If it is 0, it is never discarded. Default value: 0 -->
<property name="maxIdleTime">60</property>
<! -- Initializes the number of connections in the pool. The value should be between the minimum and maximum connections, default is 3-->
<property name="initialPoolSize">2</property>
Copy the code

Test to see if the problem is resolved

  1. Instead of using c3P0, once the program is running, type show PROCESSLIST in the database; Display current connections: Number of connection records =3(non-connected)+1(current connected) (normal)
  • Set Global WAIT_TIMEOUT =10 to disconnect the database after 10 seconds
  • Run the test and soon find the above exception (usually we encounter the situation, but we usually do not appear after 8 hours, here set to 10 seconds)
  1. Using c3P0, configure the above properties:<property name="hibernate.c3p0.idle_test_period">20</property>
  • To start the program, type show PROCESSLIST in the database; If the current connection is displayed: The number of connection records =3(non-connection)+4(current connection), the configuration is successful
  • The program runs about 10 seconds before the above problem occurs (unsurprisingly), at which point type show PROCESSLIST; Current connection: Number of connection records =3(non-connection)+0(current connection), c3P0 is disconnected
  • After about 20 seconds, type show PROCESSLIST; C3p0 is connected. If the number of connection records =3(no connection)+4(current connection), c3P0 is connected. Return to…..
  1. Configure the above properties:<property name="hibernate.c3p0.idle_test_period">8</property>The program ran and there have been no problems since then. Hey hey

advice

It is recommended to set the database wait_timeout to 24 hours (86400), and then use C3P0 to set the timeout to less than 86400, depending on your situation.