I'm running program at apache tomcat server, that should be on permanently, but every morning(the client part isn't accessible at night) i receive error message (in apache tomcat console) that MySQL server is off. So is there any way to prevent this? Thanks in advance!
-
What are you using to connect to your database? Are you using a connection pool that's leaving db connections open all night (and isn't smart enough to close them when done, or at least reopen connections that go away)?Seth– Seth2010-04-07 17:51:25 +00:00Commented Apr 7, 2010 at 17:51
-
Ops, i missed that i`m using Hibernate API to access databaseNikolayGS– NikolayGS2010-04-07 18:20:13 +00:00Commented Apr 7, 2010 at 18:20
-
Can you please paste the exact error messages? How do you fix your system after this? Do you restart your tomcat and everything is fine afterwards?Michael Konietzka– Michael Konietzka2010-04-07 19:04:30 +00:00Commented Apr 7, 2010 at 19:04
-
Well, i fix the problem with restart mysqld.exe and tomcat. But as i was suggested i add jdbc:mysql://localhost/mydb?autoreconnect=true in hibernate cfg file this morning connection was lost again, but when i restart tomcat(without mysqld) everything was OK.NikolayGS– NikolayGS2010-04-08 07:42:10 +00:00Commented Apr 8, 2010 at 7:42
Add a comment
|
3 Answers
Yes, I have had the same problem. Try to add a validationQuery to your DataSource.
Here is how I have defined mine:
<Resource auth="Container"
type="javax.sql.DataSource"
name="jdbc/gporder"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/mydb"
maxActive="10"
maxIdle="5"
validationQuery="SELECT 1"
testOnBorrow="true"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="10000"
minEvictableIdleTimeMillis="60000"
username="..." password="..."/>
Comments
the mysql server times out a connection after a while, by default it's 28800 seconds, it's likely this timeout you're hitting.
You can just instruct the mysql driver to reconnect in case of a lost connection by adding the autoreconnect=true parameter to the jdbc url, e.g. jdbc:mysql://localhost/mydb?autoreconnect=true
2 Comments
NikolayGS
I tried this(i have to put url in hibernata cfg file right?) and connection was lost again this morning, but as i mentioned when i restart tomcat everything was OK(earlier had to restart first mysqld.exe first)
Steve Chambers
Worth noting this method isn't recommended by the MySQL Connector/J documentation "because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly".
I suppose you open the connections directly in your web apps code. You could try to introduce a connection pool using a timeout the connection should be reestablished.
If this doesn't help, ask the network admin whether he has stateful filters which detect timeouts (while your client is idle) and close the connection.
If you're using a database pool, like c3p0 or dbcp, look in its documentation, there should be settings to configure idle timeouts or periodic checking of the connection (which will keep the connection open, and not time it out on the server side)
1 Comment
NikolayGS
In this case i`m the admin, but i am not so advanced and familiar with connection pooling, but i will read your article and try to search solution
