The org.glassfish.api.jdbc.validation.MySQLConnectionValidation do the same "select 1" query. But how can I timed it before connection timeout?
Do you have info about Glassfish connection pool implementation?
How can I set parameters for MySQL?
(this isn't DBCP neither C3P0)
2012.09.12. 13:19 keltezéssel, Lachezar Dobrev wrote:
> Hmmm...
> Maybe you need to lower the minimum pool size...
> Since connections can not be held indefinitely, the minimum pool
> size should be «zero» to avoid holding to connections longer than the
> server allows.
>
> I do however understand, that the validation should have dropped the
> stale connections though. Maybe you can try a table validation? It
> might be possible, that the consecutive execution of the «SELECT '1'»
> query that MySQL Validator uses is cached, and does not hit the
> server, although I seriously doubt it...
>
> 2012/9/12<papp.zoltan_at_mondoka.hu>:
>> mysql> SHOW VARIABLES LIKE 'wait_timeout';
>> +---------------+-------+
>> | Variable_name | Value |
>> +---------------+-------+
>> | wait_timeout | 28800 |
>> +---------------+-------+
>> 1 row in set (0.00 sec)
>>
>> mysql> SHOW VARIABLES LIKE 'connect_timeout';
>> +-----------------+-------+
>> | Variable_name | Value |
>> +-----------------+-------+
>> | connect_timeout | 30 |
>> +-----------------+-------+
>> General Settings
>>
>> Datasource Classname:
>> com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
>> Vendor-specific classname that implements the DataSource and/or
>> XADataSource APIs
>> Pool Settings
>>
>> Initial and Minimum Pool Size: 8 Connections
>> Minimum and initial number of connections maintained in the pool
>>
>> Maximum Pool Size: 320 Connections
>> Maximum number of connections that can be created to satisfy client
>> requests
>>
>> Pool Resize Quantity: 2 Connections
>> Number of connections to be removed when pool idle timeout expires
>>
>> Idle Timeout: 300 Seconds
>> Maximum time that connection can remain idle in the pool
>>
>> Max Wait Time: 30000 Milliseconds
>> Amount of time caller waits before connection timeout is sent
>> JDBC Connection Pool Advanced Attributes
>>
>> Pooling: (true) Enabled
>> When set to false, disables connection pooling for the pool
>> Connection Validation
>>
>> Connection Validation: (true) Required
>> Validate connections, allow server to reconnect in case of failure
>>
>> Validation Method: custom-validation
>>
>> Validation Class Name:
>> org.glassfish.api.jdbc.validation.MySQLConnectionValidation
>> If custom-validation is selected, specify validation classname.
>> JDBC Connection Pool Properties
>>
>> AutoReconnectForPools=true
>> AutoReconnect=true
>> testWhileIdle=true
>>
>> [#|2012-09-02T23:15:16.209+0200|INFO|glassfish3.1.2|org.hibernate.event
>> .internal
>> .DefaultLoadEventListener|_ThreadID=94;_ThreadName=Thread-2;|HHH000327:
>> Error performing load command :
>> org.hibernate.exception.JDBCConnectionException:
>> The last packet successfully received from the server was67763 seconds
>> ago.
>> The last packet sent successfully to the server was 67763 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.|#]
>>
>> Glassfish 3.1.2.2
>> MySQL 5.1.63
>> hibernate-core-4.1.6
>> mysql-connector-java-5.1.6-bin.jar
>> hibernate-core-4.1.6.Final.jar
>> jdk1.7.0_05
>>
>> I used DBCP with Tomcat 7 before Glassfish. In MATA-INF/context.xml I
>> defined a datasource:
>>
>> <Resource name="jdbc/demand"
>> auth="Container"
>> type="javax.sql.DataSource"
>> maxActive="100"
>> maxIdle="30"
>> maxWait="10000"
>> validationQuery="select 1"
>> testWhileIdle="true"
>> timeBetweenEvictionRunsMillis="36000"
>> minEvictableIdleTimeMillis="25800"
>> username="xxx" password="xxxxxxx"
>> driverClassName="com.mysql.jdbc.Driver"
>> url="jdbc:mysql://localhost:3306/demand?useUnicode=true&autoReconne
>> ct=true&characterEncoding=UTF-8">
>> </Resource>
>>
>>
>> I tried use this properties in Glassfish without success.
>> I tried table validation, too.
>> I googled over the internet, but nothing.
>>
>> How can I solve this issue in Glassfish DAS?
>>
>> Installation in my homepage here.
>> (it's in hungarian but with pictures from DAS)