[Hibernate] Connection Pooling / Connection Validation - best practices?
Howdy, I've been having trouble getting Connection Pooling working with either C3P0 or DBCP and using MySQL... the connection dies after some number of hours and the next time the webapp is used, it hangs, and an ioException is logged with a connection failure. I have been told there is some type of 'connection validation' that is configurable and now i have learned that it is required in order to make pooled connections work and not fail when the db/driver times out. Can someone please explain *exactly* which hibernate.cfg.xml parameters need to be set (and to what values) to get pooling working and exactly how one sets up this 'Connection Validation' that i have heard about?? FYI: I'm using Hibernate 2 and MySQL 4 and (trying) C3P0. I'm sorry if this is an old/tired question to some, but combing the mailing lists, i see a variety of answers that do not seem clear to me and i can find no information on the website regarding Connection Validation. Can someone please give a redux on how to do all this 'the right way'??? many thanks, -dave --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel
Re: [Hibernate] Connection Pooling / Connection Validation - best practices?
Ok, So it seems to me that to use C3P0, you must set the following four paramaters. hibernate.c3p0.max_size =maximum connection pool size hibernate.c3p0.min_size = minimum connection pool size hibernate.c3p0.timeout = maximum idle time hibernate.c3p0.max_statements =size of statement cache But when people say you must "enable connection validation", does that mean setting C3P0_VALIDATE_CONNECTION to some very simple query? ie... is C3P0_VALIDATE_CONNECTION the same as DBCP_VALIDATION_QUERY ??? Are there any other params that need to be set to get C3P0 connection pooling working properly and validating connections? Alternatively, if one chooses to use DBCP and the MySQL autoReconnect=true JDBC URL option, do you still have to set hibernate.dbcp.testOnReturn and hibernate.dbcp.testOnBorrow to true??? Actually, if someone could list exactly what the minimal set of DBCP params are that need to be set (and what are reasonable values for some of these), that would be a big help!!! Right now, i'm using: "hibernate.dbcp.maxActive" "hibernate.dbcp.maxIdle" "hibernate.dbcp.maxWait" "hibernate.dbcp.testOnBorrow" "hibernate.dbcp.testOnReturn" "hibernate.dbcp.validationQuery" Do i need to set any others thanks, -dave Gavin King wrote: Someone has claimed that this parameter doesn't work if autocommit is disabled (Hibernate always disables autocommit). I don't know if this is true or not. You can pass autoReconnect=true in the JDBC URL which makes the MySQL-driver attempt to reconnect if the connection dies. See: http://www.mysql.com/documentation/connector-j/index.html#id2800782 /Ludde --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel
[Hibernate] DBCP Connection Pooling
Ok, I am trying to get DBCP connection pooling working reliably (and not dropping connections)...so far i am not having much success. After a period of several hours of non-use, Hibernate will generate the familiar ioExcpetion when the connections are timed out. With that in mind, can someone give me a definitive list of DBCP params that are required to be set to get this working, along with some reasonable values??? Right now, i'm using: "hibernate.dbcp.maxActive" "hibernate.dbcp.maxIdle" "hibernate.dbcp.maxWait" "hibernate.dbcp.testOnBorrow" -> true "hibernate.dbcp.testOnReturn" -> true "hibernate.dbcp.validationQuery" Do i need to set any others Are there any other tricks to getting DBCP working with Hibernate that i need to know? I have tried using C3P0 and also fooled around with this autoreconnect=true URL, but i have given up on both of these options and would like to get DBCP configured "the right way". I would *really* appreciate some guidance with this -- i will be happy to summarize what i learn and submit it for a FAQ or something if that will help so others will have this well-documented online somewhere. Many thanks, -dave --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel
Re: [Hibernate] DBCP Connection Pooling
Connections only time out late at night or over the weekend when the web app in question is not being used. Normally, the web app is used heavily during the day until about 9pm and then no more until the next day. I want to take advantage of pooling with Hibernate when the app is being used heavily, but i do not want it to drop the connection afterwards when it is idle. I simply would like some good docs for how to properly set up DBCP with Hibernate and the validation query. Such docs do not seem to exist on the Hibernate site and various mailing list postings seem to contain contradictory information about how to do this. thanks, -dave Juozas Baliuka wrote: Why do you need pooling if connections are time out. Close connection after transaction and open a new one before transaction without any kind of pooling. It must be very trivial to implement this kind of DataSource, a few lines of code. Ok, I am trying to get DBCP connection pooling working reliably (and not dropping connections)...so far i am not having much success. After a period of several hours of non-use, Hibernate will generate the familiar ioExcpetion when the connections are timed out. With that in mind, can someone give me a definitive list of DBCP params that are required to be set to get this working, along with some reasonable values??? Right now, i'm using: "hibernate.dbcp.maxActive" "hibernate.dbcp.maxIdle" "hibernate.dbcp.maxWait" "hibernate.dbcp.testOnBorrow" -> true "hibernate.dbcp.testOnReturn" -> true "hibernate.dbcp.validationQuery" Do i need to set any others Are there any other tricks to getting DBCP working with Hibernate that i need to know? I have tried using C3P0 and also fooled around with this autoreconnect=true URL, but i have given up on both of these options and would like to get DBCP configured "the right way". I would *really* appreciate some guidance with this -- i will be happy to summarize what i learn and submit it for a FAQ or something if that will help so others will have this well-documented online somewhere. Many thanks, -dave --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel
Re: [Hibernate] DBCP Connection Pooling
Mark Woon, Mucho Gracias! I will give this a try! Thank you muchly!! Gavin - thanks for your help and comments. I will ask the DBCP folks for more details per your suggestion, since their online doc is pretty thin. I guess I was under the impression that lots of Hibernate users make use of DBCP -- i see now maybe that is not so true! Later on, i will get to use an appserver datasource for the web app in question, but for now, we're not using one quite yet. thanks again, -dave Mark Woon wrote: Dave Tilley wrote: I would *really* appreciate some guidance with this -- i will be happy to summarize what i learn and submit it for a FAQ or something if that will help so others will have this well-documented online somewhere. Dave, Here's a list of all the extra params I have to configure DBCP. It's not formatted for hibernate.cfg.xml, but you'll get the idea: maxActive 5 maxIdle 3 maxWait 500 defaulAutoCommit false validationQuery select 1 from dual removeAbandoned true removeAbandonedTimeout 60 logAbandoned true Hope that helps, -Mark --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel
