You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Sanjeet Joshi <sa...@gmail.com> on 2005/04/11 14:46:13 UTC
MySQL connection problem using DBCP
Hi,
I am facing a problem with Connections to the database.
Here is the development environment in short:
Struts, Jsp, Servlets and MySQL 4.1
I am using DBCP connection pooling. The datasource configuration is
in struts-config.xml file. I am using container managed security for
authentication.
I get EOFException when I try to login (or access the db for any other
purpose). This happens only after a long period of db inactivity.
Otherwise everything works fine.
When I searched for a solution, I found out that MySQL closes all
inactive connections after 8 hours. I also found out that setting a
MySQL attribute autoReconnect=true solves this. I tried setting it
along with my datasource config in struts-config.xml as follows:
<data-sources>
<data-source key="xyz" type="org.apache.commons.dbcp.BasicDataSource">
<set-property property="driverClassName" value="com.mysql.jdbc.Driver" />
<set-property property="url" value="jdbc:mysql://localhost:3306/xyz" />
<set-property property="autoReconnect" value="true" />
<set-property property="username" value="***" />
<set-property property="password" value="***" />
</data-source>
</data-sources>
Is this the correct way of setting autoReconnect to true? Some
solutions suggested setting it along with the url i.e. like
<set-property property="url"
value="jdbc:mysql://localhost:3306/xyz?autoReconnect=true" />
Which of these is correct?
To test this solution I tried to reduce the wait_timeout and
interactive_timeout (both MySQL variables) from 8 hours to one minute.
The connections were closed after a minutes inactivity. But my webapp
worked absolutely fine (without autoReconnect="true") So I could not
get EOFException on my development machine.
Is there anyone who has faced this problem and solved it? Any Pointers
to any reading material about this?
Any help is appreciated.
Thanks in advance.
Sanjeet
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: MySQL connection problem using DBCP
Posted by Sanjeet Joshi <sa...@gmail.com>.
Hi Dirk,
Thanks for your help and the corrections suggested.
After getting you mail I changed the DB url to inclue
autoReconnect=true in following files.
(name of my webapp is PV)
1. tomcat5/conf/Catalina/localhost/PV.xml ---> in this file I have
configured JDBCRealm
for Container managed security
and
2. struts-config.xml --> all my datasources configured here
After the change I restarted tomcat. But after 8 hours I got the same problem
>> java.sql.SQLException: Communication link failure:
java.io.EOFException, underlying cause: null
Where exactly should I set autoReconnect? and does it really work?
Should I configure this in server.xml file?
Your help will be really appreciated....
Regards,
Sanjeet
On 4/13/05, Dirk Verbeeck <di...@pandora.be> wrote:
> The first config doesn't work, that should be something like:
> <set-property property="connectionProperties"
> value="autoReconnect=true" />
>
> but most people use your second solution (the url version)
>
> -- Dirk
>
> Sanjeet Joshi wrote:
>
> > Hi,
> >
> > I am facing a problem with Connections to the database.
> >
> > Here is the development environment in short:
> >
> > Struts, Jsp, Servlets and MySQL 4.1
> > I am using DBCP connection pooling. The datasource configuration is
> > in struts-config.xml file. I am using container managed security for
> > authentication.
> >
> > I get EOFException when I try to login (or access the db for any other
> > purpose). This happens only after a long period of db inactivity.
> > Otherwise everything works fine.
> >
> > When I searched for a solution, I found out that MySQL closes all
> > inactive connections after 8 hours. I also found out that setting a
> > MySQL attribute autoReconnect=true solves this. I tried setting it
> > along with my datasource config in struts-config.xml as follows:
> >
> > <data-sources>
> > <data-source key="xyz" type="org.apache.commons.dbcp.BasicDataSource">
> > <set-property property="driverClassName" value="com.mysql.jdbc.Driver" />
> > <set-property property="url" value="jdbc:mysql://localhost:3306/xyz" />
> > <set-property property="autoReconnect" value="true" />
> > <set-property property="username" value="***" />
> > <set-property property="password" value="***" />
> > </data-source>
> > </data-sources>
> >
> > Is this the correct way of setting autoReconnect to true? Some
> > solutions suggested setting it along with the url i.e. like
> >
> > <set-property property="url"
> > value="jdbc:mysql://localhost:3306/xyz?autoReconnect=true" />
> >
> > Which of these is correct?
> >
> > To test this solution I tried to reduce the wait_timeout and
> > interactive_timeout (both MySQL variables) from 8 hours to one minute.
> > The connections were closed after a minutes inactivity. But my webapp
> > worked absolutely fine (without autoReconnect="true") So I could not
> > get EOFException on my development machine.
> >
> > Is there anyone who has faced this problem and solved it? Any Pointers
> > to any reading material about this?
> >
> > Any help is appreciated.
> >
> > Thanks in advance.
> > Sanjeet
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: commons-user-help@jakarta.apache.org
> >
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: MySQL connection problem using DBCP
Posted by Sanjeet Joshi <sa...@gmail.com>.
Hi Dirk,
I made the changes according to your suggestions. But I am still
getting the exception after 8 hours of inactive time.
My db connection URL looks like this:
jdbc:mysql://localhost:3306/datavase?autoReconnect=true&autoReconnectForPools=true
Is it correct?
On 4/13/05, Dirk Verbeeck <di...@pandora.be> wrote:
> The first config doesn't work, that should be something like:
> <set-property property="connectionProperties"
> value="autoReconnect=true" />
>
> but most people use your second solution (the url version)
>
> -- Dirk
>
> Sanjeet Joshi wrote:
>
> > Hi,
> >
> > I am facing a problem with Connections to the database.
> >
> > Here is the development environment in short:
> >
> > Struts, Jsp, Servlets and MySQL 4.1
> > I am using DBCP connection pooling. The datasource configuration is
> > in struts-config.xml file. I am using container managed security for
> > authentication.
> >
> > I get EOFException when I try to login (or access the db for any other
> > purpose). This happens only after a long period of db inactivity.
> > Otherwise everything works fine.
> >
> > When I searched for a solution, I found out that MySQL closes all
> > inactive connections after 8 hours. I also found out that setting a
> > MySQL attribute autoReconnect=true solves this. I tried setting it
> > along with my datasource config in struts-config.xml as follows:
> >
> > <data-sources>
> > <data-source key="xyz" type="org.apache.commons.dbcp.BasicDataSource">
> > <set-property property="driverClassName" value="com.mysql.jdbc.Driver" />
> > <set-property property="url" value="jdbc:mysql://localhost:3306/xyz" />
> > <set-property property="autoReconnect" value="true" />
> > <set-property property="username" value="***" />
> > <set-property property="password" value="***" />
> > </data-source>
> > </data-sources>
> >
> > Is this the correct way of setting autoReconnect to true? Some
> > solutions suggested setting it along with the url i.e. like
> >
> > <set-property property="url"
> > value="jdbc:mysql://localhost:3306/xyz?autoReconnect=true" />
> >
> > Which of these is correct?
> >
> > To test this solution I tried to reduce the wait_timeout and
> > interactive_timeout (both MySQL variables) from 8 hours to one minute.
> > The connections were closed after a minutes inactivity. But my webapp
> > worked absolutely fine (without autoReconnect="true") So I could not
> > get EOFException on my development machine.
> >
> > Is there anyone who has faced this problem and solved it? Any Pointers
> > to any reading material about this?
> >
> > Any help is appreciated.
> >
> > Thanks in advance.
> > Sanjeet
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: commons-user-help@jakarta.apache.org
> >
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: MySQL connection problem using DBCP
Posted by Dirk Verbeeck <di...@pandora.be>.
The first config doesn't work, that should be something like:
<set-property property="connectionProperties"
value="autoReconnect=true" />
but most people use your second solution (the url version)
-- Dirk
Sanjeet Joshi wrote:
> Hi,
>
> I am facing a problem with Connections to the database.
>
> Here is the development environment in short:
>
> Struts, Jsp, Servlets and MySQL 4.1
> I am using DBCP connection pooling. The datasource configuration is
> in struts-config.xml file. I am using container managed security for
> authentication.
>
> I get EOFException when I try to login (or access the db for any other
> purpose). This happens only after a long period of db inactivity.
> Otherwise everything works fine.
>
> When I searched for a solution, I found out that MySQL closes all
> inactive connections after 8 hours. I also found out that setting a
> MySQL attribute autoReconnect=true solves this. I tried setting it
> along with my datasource config in struts-config.xml as follows:
>
> <data-sources>
> <data-source key="xyz" type="org.apache.commons.dbcp.BasicDataSource">
> <set-property property="driverClassName" value="com.mysql.jdbc.Driver" />
> <set-property property="url" value="jdbc:mysql://localhost:3306/xyz" />
> <set-property property="autoReconnect" value="true" />
> <set-property property="username" value="***" />
> <set-property property="password" value="***" />
> </data-source>
> </data-sources>
>
> Is this the correct way of setting autoReconnect to true? Some
> solutions suggested setting it along with the url i.e. like
>
> <set-property property="url"
> value="jdbc:mysql://localhost:3306/xyz?autoReconnect=true" />
>
> Which of these is correct?
>
> To test this solution I tried to reduce the wait_timeout and
> interactive_timeout (both MySQL variables) from 8 hours to one minute.
> The connections were closed after a minutes inactivity. But my webapp
> worked absolutely fine (without autoReconnect="true") So I could not
> get EOFException on my development machine.
>
> Is there anyone who has faced this problem and solved it? Any Pointers
> to any reading material about this?
>
> Any help is appreciated.
>
> Thanks in advance.
> Sanjeet
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org