All geek questions in one place

Checking connections in c3p0 pool

I am developing a Java SE application using Hibernate 4 and c3p0 to communicate with the MariaDB database. This is a long application waiting for signals from outside, so sometimes the database closes my connections after 8 hours of inactivity. I tried to configure c3p0 checks, but it does not work. could you help me?

Error log (thrown during a named query):

2014-10-27 08:10:19.062 ERROR [trans] com.example.runnable.T1 - Exception thrown during event processing, rollbacking transaction: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91) at org.hibernate.loader.Loader.getResultSet(Loader.java:2065) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) at org.hibernate.loader.Loader.doQuery(Loader.java:909) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) at org.hibernate.loader.Loader.doList(Loader.java:2553) at org.hibernate.loader.Loader.doList(Loader.java:2539) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369) at org.hibernate.loader.Loader.list(Loader.java:2364) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:496) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:231) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103) at com.example.runnable.T1.find(EventsTransmitter.java:140) at com.example.runnable.T1.run(EventsTransmitter.java:86) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 108,132,692 milliseconds ago. The last packet sent successfully to the server was 108,132,692 milliseconds ago. 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 sun.reflect.GeneratedConstructorAccessor30.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:408) at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3661) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2030) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ... 17 more Caused by: java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) at java.net.SocketOutputStream.write(SocketOutputStream.java:153) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3643) ... 23 more

pom.xml:

 <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.33</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>4.3.6.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-validator</artifactId> <version>5.1.2.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>4.3.1.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-ehcache</artifactId> <version>4.3.6.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-c3p0</artifactId> <version>4.3.6.Final</version> </dependency>

Src / home / resources / hibernate.cfg.xml:

 <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/MyBase?zeroDateTimeBehavior=convertToNull&amp;autoReconnect=true</property> <property name="hibernate.connection.username">user</property> <property name="hibernate.connection.password">pass</property> <property name="hibernate.current_session_context_class">thread</property> <property name="show_sql">true</property> <property name="use_sql_comments">true</property> <property name="hibernate.cache.use_second_level_cache">true</property> <property name="hibernate.cache.use_query_cache">true</property> <property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</property> <property name="hibernate.c3p0.min_size">5</property> <property name="hibernate.c3p0.max_size">10</property> <property name="hibernate.c3p0.timeout">300</property> <property name="hibernate.c3p0.max_statements">30</property> <mapping class="com.example.domain.E" /> </session-factory> </hibernate-configuration>

Src / main / resources / C3P0-config.xml:

 <?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <property name="preferredTestQuery">SELECT 1 FROM DUAL</property> <property name="testConnectionOnCheckin">true</property> <property name="idleConnectionTestPeriod">1800</property> <!-- 30 minutes --> </default-config> </c3p0-config>

Edited

In the logs on startup, I get the c3p0 configuration (executed by Maven from Netbeans):

 ... INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@81187ff9 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@e1820e10 [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kflt95n558v5xddgsj|2cf3d63b, idleConnectionTestPeriod -> 1800, initialPoolSize -> 5, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 300, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 30, maxStatementsPerConnection -> 0, minPoolSize -> 5, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@2f0d7eae [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> z8kflt95n558v5xddgsj|1e6a3214, jdbcUrl -> jdbc:mysql://localhost:3306/MyBase?zeroDateTimeBehavior=convertToNull&autoReconnect=true, properties -> {user=******, password=******} ], preferredTestQuery -> SELECT 1 FROM DUAL, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> true, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> z8kflt95n558v5xddgsj|7161d8d1, numHelperThreads -> 3 ] ...

Edit 2

When executing the jar created by maven-shade-plugin, I get:

paź 27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator instantiateC3p0Provider WARN: HHH000022: c3p0 properties were encountered, but provider class c3p0 was not found on the classpath; these properties will be ignored. paź 27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure WARN: HHH000402: use the built-in Hibernate connection pool (not for production!) paź 27, 2014 10:56:22 PM org .hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator

or when I tried to specify org.hibernate.connection.C3P0ConnectionProvider in the properties:

INFO: HHH000130: Creating an explicit connection provider: org.hibernate.connection.C3P0ConnectionProvider Initial SessionFactory creating failed.org.hibernate.service.spi.ServiceException: unable to create the requested service [Org.hibernate.engine.jdbc.connections.spi.ConnectionProv ] An exception in the thread "main" java.lang.ExceptionInInitializerError at com.example.util.HibernateUtil. (HibernateUtil.java:27) at com.example.App.run (App.java:31) at com.example.App.main (App.java:25)
Called: org.hibernate.service.spi.ServiceException: unable to create the requested service [org.hibernate.engine.jdbc.connections.spi.ConnectionProvider]

  at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:261) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:225) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206) at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260) at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206) at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1885) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1843) at com.example.util.HibernateUtil.<clinit>(HibernateUtil.java:24) ... 2 more Caused by: org.hibernate.HibernateException: Could not instantiate

connection provider [org.hibernate.connection.C3P0ConnectionProvider]

  at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.instantiateExplicitConnectionProvider(ConnectionProviderInitiator.java:197) at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.initiateService(ConnectionProviderInitiator.java:120) at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.initiateService(ConnectionProviderInitiator.java:55) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:105) at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:251) ... 12 more Caused by: org.hibernate.boot.registry.selector.spi.StrategySelectionException:

Cannot resolve name [org.hibernate.connection.C3P0ConnectionProvider] as strategy [Org.hibernate.engine.jdbc.connections.spi.ConnectionProvider] on org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplement 128) on org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.instantiateExplicitConnectionProvider (ConnectionProviderInitiator.java:194) ... 16 more

But all is well, if you start with Maven. Unfortunately, I need this to work from one bank. Any ideas why org.hibernate.connection.C3P0ConnectionProvider cannot be found in the shaded jar?

+5
source share
3 answers

Ok, I managed to fix all the problems. Here is the soul.

First of all, as Steve Waldman suggested, c3p0 was not actually initialized, but in Hibernate 4.3 hibernate.connection.provider_class parameter should be: org.hibernate.c3p0.internal.C3P0ConnectionProvider . In the documentation you can read:

Connection provider using C3P0 connection pool. Hibernate will use this by default if the hibernate.c3p0 properties are set. *.

But, in my opinion, it is useful to set this parameter yourself, and this is necessary if you use the c3p0 configuration file, and not hibernate.c3p0.* .

The second problem was getting the session by calling SessionFactory.openSession() at the beginning of the stream and then always using the same session object. I assume that after the connection to the database was broken and a new one appeared, recreated in the pool, using the old session caused by using the old, broken connection. Thus, possible solutions received a new session SessionFactory.openSession() after detecting a connection error or using SessionFactory.getCurrentSession() at the beginning of each message. I decided to use the second option (in this case my application is waiting for an external signal, sometimes for several hours, so I get the current session after each signal).

My final configuration:

hibernate.cfg.xml:

 <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/MyBase?zeroDateTimeBehavior=convertToNull&amp;autoReconnect=true</property> <property name="hibernate.connection.username">user</property> <property name="hibernate.connection.password">pass</property> <property name="hibernate.connection.provider_class">org.hibernate.c3p0.internal.C3P0ConnectionProvider</property> <property name="hibernate.current_session_context_class">thread</property> <property name="show_sql">true</property> <property name="use_sql_comments">true</property> <property name="hibernate.cache.use_second_level_cache">true</property> <property name="hibernate.cache.use_query_cache">true</property> <property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</property> </session-factory> </hibernate-configuration>

c3p0-config.xml:

 <?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <property name="initialPoolSize">5</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">10</property> <property name="checkoutTimeout">3000</property> <property name="maxStatementsPerConnection">30</property> <property name="preferredTestQuery">SELECT 1 FROM DUAL</property> <property name="testConnectionOnCheckin">true</property> <property name="testConnectionOnCheckout">false</property> <property name="idleConnectionTestPeriod">300</property> <!-- 5 minutes --> </default-config> </c3p0-config>

With this c3p0 configuration:

  • It will be tested every 5 minutes after performing any queries during the connection, so it will never be canceled by the database (in the standard configuration, MySQL will terminate the connection after 8 hours of inactivity),
  • If the DB is restarted or the connection (s) is killed manually, we have two options: a) after a maximum of 5 minutes, the connections will be restored, b) if the application tries to fulfill the request before automatically reconnecting the HibernateException connection, the connection will be restored, and the next request will be successful.

Optionally, testConnectionOnCheckout can be set to true to prevent exceptions, but this will cause performance problems (see documentation ).

+4
source

Try setting testConnectionOnCheckout to true in c3p0-config.

0
source

Are you sure that c3p0 is actually initialized and that it has the configuration that you expect from it?

in your logs, at the INFO level, you should see a c3p0 DataSource configuration dump when initializing the pool. make sure it is there and that this is the expected configuration.

if not, consider adding the following line to your hibernate.cfg.xml:

 <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" />
0
source

Source: https://habr.com/ru/post/1205582/

More articles:


All Articles