Re: mysql pool problem (again)
Dennis Bekkering skrev: A validation query doesn't help in the MySQL scenario: The driver will automatically reconnect and successfully execute the query, but the Connection still has a chance of remaining in an inconsistent state. This won't happen on every reconnect, just on some. You are perfectly right that the MySQL JDBC-driver's auto re-connect feature conflicts with the Java connection pool settings, creating this inconsistency. However, I don't agree that a validation query does not help - you should be able to turn off the MySQL-specific feature in the JDBC driver and let the OJB or DBPC connection pool implementations handle re-connect via validation query (and validation query only). To turn off the MySQL re-connect feature: 1. make sure the JDBC URL does *not* include ?autoReconnect=true (the default value is false = off) 2. make sure that the OJB connection pool settings does *not* include the JDBC connection property: attribute attribute-name=jdbc.autoReconnect attribute-value=true/ To make sure that MySQL auto re-connect is off, you can add an explicit false setting in the OJB connection-pool configuration (but do make sure to clean the JDBC URL according to #1 above): connection-pool ... testOnBorrow=true ... validationQuery=... ... attribute attribute-name=jdbc.autoReconnect attribute-value=false/ ... /connection-pool (Although the default setting has been false since MySQL JDBC v1.1) Wouldn't this help in your scenario? See also: *) MySQL documentation: Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html *) OJB documentation: Repository File, connection-pool, Custom attributes, jdbc.* http://db.apache.org/ojb/docu/guides/repository.html#jdbc.* Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: mysql pool problem (again)
Martin, Thanks for the help. As usual i was to fast with conclusions, my problems at the time originated from a connection leak. Thanks, Dennis 2006/8/8, Martin Kalén [EMAIL PROTECTED]: Dennis Bekkering skrev: A validation query doesn't help in the MySQL scenario: The driver will automatically reconnect and successfully execute the query, but the Connection still has a chance of remaining in an inconsistent state. This won't happen on every reconnect, just on some. You are perfectly right that the MySQL JDBC-driver's auto re-connect feature conflicts with the Java connection pool settings, creating this inconsistency. However, I don't agree that a validation query does not help - you should be able to turn off the MySQL-specific feature in the JDBC driver and let the OJB or DBPC connection pool implementations handle re-connect via validation query (and validation query only). To turn off the MySQL re-connect feature: 1. make sure the JDBC URL does *not* include ?autoReconnect=true (the default value is false = off) 2. make sure that the OJB connection pool settings does *not* include the JDBC connection property: attribute attribute-name=jdbc.autoReconnect attribute-value=true/ To make sure that MySQL auto re-connect is off, you can add an explicit false setting in the OJB connection-pool configuration (but do make sure to clean the JDBC URL according to #1 above): connection-pool ... testOnBorrow=true ... validationQuery=... ... attribute attribute-name=jdbc.autoReconnect attribute-value=false/ ... /connection-pool (Although the default setting has been false since MySQL JDBC v1.1) Wouldn't this help in your scenario? See also: *) MySQL documentation: Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html *) OJB documentation: Repository File, connection-pool, Custom attributes, jdbc.* http://db.apache.org/ojb/docu/guides/repository.html#jdbc.* Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- mvg, Dennis
Re: mysql pool problem (again)
Hi Dennis, Dennis Bekkering wrote: Hello Tom, Hmm, I'm not too sure why you need this behavior, but I think you should definitely ask the DBCP guys over at the commons-user mailing list. I checked OJB.properties and I am using ConnectionFactoryPooledImpl.class . I kill the connections with the mysql administrator tool, simulating timed out connections. I discovered that the validation query is not called on non transactional requests. Is there a special reason for that? Is it maybe wise to check always when a new broker is called by PersistenceBrokerFactory.defaultPersistenceBroker()? I'm confused. How does this happen. OJB always use ConnectionFactory#lookupConnection to lookup connections and doesn't differ between transactional and non-transactional connections, thus the validation query is always performed. The only difference is the lifetime of the connection associated with the used PB instance. With tx the connection will be closed (returned to pool) on PersistenceBroker.commitTransaction() or PersistenceBroker.abortTransaction(). Without running tx the connection only be closed (returned to pool) on PB.close() call. http://db.apache.org/ojb/docu/guides/connection.html#When+does+OJB+open%2Fclose+a+connection So if you don't close the PB instance after use, the associated connection can be time out. Could this be the reason of your issue? regards, Armin Actually the code where the problem occured needed a transaction so i am happy that i discovered it this way ;-) Cheers, Dennis - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: mysql pool problem (again)
Hello Armin, I'm confused. How does this happen. OJB always use ConnectionFactory#lookupConnection to lookup connections and doesn't differ between transactional and non-transactional connections, thus the validation query is always performed. The only difference is the lifetime of the connection associated with the used PB instance. With tx the connection will be closed (returned to pool) on PersistenceBroker.commitTransaction() or PersistenceBroker.abortTransaction(). Without running tx the connection only be closed (returned to pool) on PB.close() call. http://db.apache.org/ojb/docu/guides/connection.html#When+does+OJB+open%2Fclose+a+connection You are right i didn't close the broker. I got lazy by the broker per view pattern. But this code was in a scheduler thread and not in an http request so i have to close it after every performed scheduled task. Thank you so much for clearing that out. Cheers, Dennis
Re: mysql pool problem (again)
Hello Tom, Hmm, I'm not too sure why you need this behavior, but I think you should definitely ask the DBCP guys over at the commons-user mailing list. I checked OJB.properties and I am using ConnectionFactoryPooledImpl.class . I kill the connections with the mysql administrator tool, simulating timed out connections. I discovered that the validation query is not called on non transactional requests. Is there a special reason for that? Is it maybe wise to check always when a new broker is called by PersistenceBrokerFactory.defaultPersistenceBroker()? Actually the code where the problem occured needed a transaction so i am happy that i discovered it this way ;-) Cheers, Dennis
Re: mysql pool problem (again)
On 7/24/06, Dennis Bekkering [EMAIL PROTECTED] wrote: I checked OJB.properties and I am using ConnectionFactoryPooledImpl.class . I kill the connections with the mysql administrator tool, simulating timed out connections. I discovered that the validation query is not called on non transactional requests. Is there a special reason for that? Is it maybe wise to check always when a new broker is called by PersistenceBrokerFactory.defaultPersistenceBroker()? Hmm, I'm not an expert in this area, though it sounds like a bug to me. Can you please create a issue in JIRA ? Tom - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: mysql pool problem (again)
On 7/21/06, Dennis Bekkering [EMAIL PROTECTED] wrote: I am experiecing problems with mysql regarding connection time outs again. This problem cannot be resolved by the pool settings provided or jdbc driver settings such as autoReconnect (wich should not be used anyhow). Here is a more detailed explaination about the problem (from tss). I could set wait_timeout on mysql much longer but that is not a solution to the problem. - A new feature that I'd really like to see in DBCP 1.3 is a time-to-live for Connections. In particular with MySQL, such a feature is very helpful, because MySQL Connections can start showing strange behavior when the underlying connection has been terminated and implicitly reconnected. Note that such a setting should not regard whether the connection has been active or idle: After the specified amount of time, it should be closed and removed from the pool in any case. A validation query doesn't help in the MySQL scenario: The driver will automatically reconnect and successfully execute the query, but the Connection still has a chance of remaining in an inconsistent state. This won't happen on every reconnect, just on some. Two connection pools that provide such a time-to-live are Resin's (max-pool-time) and Proxool (maximum-connection-lifetime). We're using the former now for MySQL. Unfortunately, Proxool doesn't provide a bean-style DataSource yet... (but will do so in the upcoming 0.9 release). - Hmm, I'm not too sure why you need this behavior, but I think you should definitely ask the DBCP guys over at the commons-user mailing list. Tom - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
