You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Martin Dubuc <ma...@yahoo.com> on 2005/04/05 15:11:47 UTC
Re: Using JDBC with 5.5.x
I have found the cause of my problem. I am posting it
in case someone would run into the same problem.
The main issue was that I was using a DataSourceRealm
that was pointing to a Resource (under
GlobalNamingResources) that was using a username and
password that was valid but didn't have the right
privilege to access my users database. I found out the
problem after I enabled MySQL logs. The access denied
log showed up in the hostname.log file.
It is unfortunate that the Tomcat logs don't
explicitly state that access to users database was
denied. It took me a while to figure this one out.
The right config to use MySQL to perform
authentication in 5.5.x is:
server.xml:
<GlobalNamingResources>
...
<Resource name="jdbc/auth"
auth="Container"
type="javax.sql.DataSource"
maxActive="4" maxIdle="30"
maxWait="10000"
username="test" password="test"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/auth?autoReconnect=true"/>
</GlobalNamingResources>
...
<Service name="Catalina">
...
<Engine name="Catalina" defaultHost="localhost">
...
<Realm
className="org.apache.catalina.realm.DataSourceRealm"
dataSourceName="jdbc/auth"
userTable="users"
userNameCol="user_name"
userCredCol="user_pass"
userRoleTable="user_roles"
roleNameCol="role_name"/>
...
</Engine>
</Service>
where the users databse is defined as:
CREATE TABLE users (
user_name varchar(12) default NULL,
user_pass varchar(12) default NULL,
PRIMARY KEY (user_name)
) TYPE=MyISAM;
CREATE TABLE roles (
role_name varchar(12) default NULL,
PRIMARY KEY (role_name)
) TYPE=MyISAM;
CREATE TABLE user_roles (
user_name varchar(12) default NULL,
role_name varchar(12) default NULL,
PRIMARY KEY (user_name, role_name)
) TYPE=MyISAM;
Martin
--- Martin Dubuc <ma...@yahoo.com> wrote:
> I am having some problems using JDBC with 5.5.x
> releases. I can use JDBC with 5.0.28, but as soon as
> I
> switch to 5.5.x, I get the following errors trying
> to
> connect to database:
>
> Mar 29, 2005 11:26:37 AM
> org.apache.catalina.realm.DataSourceRealm open
> SEVERE: Exception performing authentication
> org.apache.tomcat.dbcp.dbcp.SQLNestedException:
> Cannot
> create PoolableConnectionFactory (Server connection
> failure during transaction. Due to underlying
> exception: 'com.mysql.jdbc.CommunicationsException:
> Communications link failure due to underlying
> exception:
>
> ** BEGIN NESTED EXCEPTION **
>
> java.io.EOFException
>
> STACKTRACE:
>
> java.io.EOFException
> at
> com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1842)
> at
>
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288)
> at
>
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784)
> at
>
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:750)
> at
>
com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3573)
> at
>
com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1151)
> at
>
com.mysql.jdbc.Connection.createNewIO(Connection.java:1889)
> at
>
com.mysql.jdbc.Connection.<init>(Connection.java:430)
> at
>
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:268)
> at
>
org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
> at
>
org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
> at
>
org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
> at
>
org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)
> at
>
org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
> at
>
org.apache.catalina.realm.DataSourceRealm.open(DataSourceRealm.java:402)
> at
>
org.apache.catalina.realm.DataSourceRealm.authenticate(DataSourceRealm.java:282)
> at
>
org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:256)
> at
>
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:391)
> at
>
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
> at
>
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
> at
>
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:526)
> at
>
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
> at
>
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
> at
>
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
> at
>
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
> at
>
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
> at
>
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
> at
>
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
> at java.lang.Thread.run(Thread.java:595)
>
>
> ** END NESTED EXCEPTION **
>
> '.
>
> ** BEGIN NESTED EXCEPTION **
>
> com.mysql.jdbc.CommunicationsException
> MESSAGE: Communications link failure due to
> underlying
> exception:
>
> ** BEGIN NESTED EXCEPTION **
>
> java.io.EOFException
>
> STACKTRACE:
>
> java.io.EOFException
> at
> com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1842)
> at
>
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288)
> at
>
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784)
> at
>
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:750)
> at
>
com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3573)
> at
>
com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1151)
> at
>
com.mysql.jdbc.Connection.createNewIO(Connection.java:1889)
> at
>
com.mysql.jdbc.Connection.<init>(Connection.java:430)
> at
>
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java
> :268)
> at
>
org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(
> DriverConnectionFactory.java:37)
> at
>
org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(Pool
> ableConnectionFactory.java:290)
> at
>
org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory
> (BasicDataSource.java:877)
> at
>
org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDat
> aSource.java:851)
> at
>
org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
> at
>
org.apache.catalina.realm.DataSourceRealm.open(DataSourceRealm.java:402)
> at
>
org.apache.catalina.realm.DataSourceRealm.authenticate(DataSourceRealm.java:282)
> at
>
org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:256)
> at
>
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:391)
> at
>
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
> at
>
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
> at
>
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:526)
> at
>
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
> at
>
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
> at
>
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
> at
>
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
> at
>
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
> at
>
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
> at
>
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
> at java.lang.Thread.run(Thread.java:595)
>
>
> ** END NESTED EXCEPTION **
>
> In server.xml, I define the resource in
> GlobalNamingResources as follows:
> <Resource
> name="db"
> type="javax.sql.DataSource"
> password="test"
> driverClassName="com.mysql.jdbc.Driver"
> maxIdle="2"
> maxWait="5000"
> username="user"
>
>
url="jdbc:mysql://localhost:3306/myapp?autoReconnect=true"
> maxActive="4"/>
>
> and the realm:
> <Realm
>
>
className="org.apache.catalina.realm.DataSourceRealm"
> dataSourceName="users"
> userTable="users"
> userNameCol="user_name"
> userCredCol="user_pass"
> userRoleTable="user_roles"
> roleNameCol="role_name"/>
>
> Martin
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Small Business - Try our new resources site!
> http://smallbusiness.yahoo.com/resources/
>
=== message truncated ===
__________________________________
Do you Yahoo!?
Yahoo! Personals - Better first dates. More second dates.
http://personals.yahoo.com
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Using JDBC with 5.5.x
Posted by Parsons Technical Services <pa...@earthlink.net>.
I, for one, appreciate the follow up post as I have found several people
with the same issue as I had, only to have no resolution in the post.
As for the issue not showing up in the log, I "think" it is more a function
of the driver sending back the information. Along with, what are you doing
with the exception when you catch it? If it doesn't show up in the
exception, then it is an issue of the driver not sending back enough
information when it throws the exception. So to sum it up, you need to look
at your own code first. If all is handled there, then turn to the drivers.
Tomcat is only the messenger in this case. Some one correct me if I am
wrong.
Don't feel bad, I did the same thing before, twice!
Doug
----- Original Message -----
From: "Martin Dubuc" <ma...@yahoo.com>
To: "Tomcat Users List" <to...@jakarta.apache.org>
Sent: Tuesday, April 05, 2005 9:11 AM
Subject: Re: Using JDBC with 5.5.x
>I have found the cause of my problem. I am posting it
> in case someone would run into the same problem.
>
> The main issue was that I was using a DataSourceRealm
> that was pointing to a Resource (under
> GlobalNamingResources) that was using a username and
> password that was valid but didn't have the right
> privilege to access my users database. I found out the
> problem after I enabled MySQL logs. The access denied
> log showed up in the hostname.log file.
>
> It is unfortunate that the Tomcat logs don't
> explicitly state that access to users database was
> denied. It took me a while to figure this one out.
>
> The right config to use MySQL to perform
> authentication in 5.5.x is:
>
> server.xml:
>
> <GlobalNamingResources>
> ...
> <Resource name="jdbc/auth"
> auth="Container"
> type="javax.sql.DataSource"
> maxActive="4" maxIdle="30"
> maxWait="10000"
> username="test" password="test"
> driverClassName="com.mysql.jdbc.Driver"
>
> url="jdbc:mysql://localhost:3306/auth?autoReconnect=true"/>
> </GlobalNamingResources>
> ...
> <Service name="Catalina">
> ...
> <Engine name="Catalina" defaultHost="localhost">
> ...
> <Realm
> className="org.apache.catalina.realm.DataSourceRealm"
> dataSourceName="jdbc/auth"
> userTable="users"
> userNameCol="user_name"
> userCredCol="user_pass"
> userRoleTable="user_roles"
> roleNameCol="role_name"/>
> ...
> </Engine>
> </Service>
>
> where the users databse is defined as:
>
> CREATE TABLE users (
> user_name varchar(12) default NULL,
> user_pass varchar(12) default NULL,
> PRIMARY KEY (user_name)
> ) TYPE=MyISAM;
>
> CREATE TABLE roles (
> role_name varchar(12) default NULL,
> PRIMARY KEY (role_name)
> ) TYPE=MyISAM;
>
> CREATE TABLE user_roles (
> user_name varchar(12) default NULL,
> role_name varchar(12) default NULL,
> PRIMARY KEY (user_name, role_name)
> ) TYPE=MyISAM;
>
> Martin
>
> --- Martin Dubuc <ma...@yahoo.com> wrote:
>
>> I am having some problems using JDBC with 5.5.x
>> releases. I can use JDBC with 5.0.28, but as soon as
>> I
>> switch to 5.5.x, I get the following errors trying
>> to
>> connect to database:
>>
>> Mar 29, 2005 11:26:37 AM
>> org.apache.catalina.realm.DataSourceRealm open
>> SEVERE: Exception performing authentication
>> org.apache.tomcat.dbcp.dbcp.SQLNestedException:
>> Cannot
>> create PoolableConnectionFactory (Server connection
>> failure during transaction. Due to underlying
>> exception: 'com.mysql.jdbc.CommunicationsException:
>> Communications link failure due to underlying
>> exception:
>>
>> ** BEGIN NESTED EXCEPTION **
>>
>> java.io.EOFException
>>
>> STACKTRACE:
>>
>> java.io.EOFException
>> at
>> com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1842)
>> at
>>
> com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288)
>> at
>>
> com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784)
>> at
>>
> com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:750)
>> at
>>
> com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3573)
>> at
>>
> com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1151)
>> at
>>
> com.mysql.jdbc.Connection.createNewIO(Connection.java:1889)
>> at
>>
> com.mysql.jdbc.Connection.<init>(Connection.java:430)
>> at
>>
> com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:268)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
>> at
>>
> org.apache.catalina.realm.DataSourceRealm.open(DataSourceRealm.java:402)
>> at
>>
> org.apache.catalina.realm.DataSourceRealm.authenticate(DataSourceRealm.java:282)
>> at
>>
> org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:256)
>> at
>>
> org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:391)
>> at
>>
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
>> at
>>
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
>> at
>>
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:526)
>> at
>>
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
>> at
>>
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
>> at
>>
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
>> at
>>
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
>> at
>>
> org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
>> at
>>
> org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
>> at
>>
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
>> at java.lang.Thread.run(Thread.java:595)
>>
>>
>> ** END NESTED EXCEPTION **
>>
>> '.
>>
>> ** BEGIN NESTED EXCEPTION **
>>
>> com.mysql.jdbc.CommunicationsException
>> MESSAGE: Communications link failure due to
>> underlying
>> exception:
>>
>> ** BEGIN NESTED EXCEPTION **
>>
>> java.io.EOFException
>>
>> STACKTRACE:
>>
>> java.io.EOFException
>> at
>> com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1842)
>> at
>>
> com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288)
>> at
>>
> com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784)
>> at
>>
> com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:750)
>> at
>>
> com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3573)
>> at
>>
> com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1151)
>> at
>>
> com.mysql.jdbc.Connection.createNewIO(Connection.java:1889)
>> at
>>
> com.mysql.jdbc.Connection.<init>(Connection.java:430)
>> at
>>
> com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java
>> :268)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(
>> DriverConnectionFactory.java:37)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(Pool
>> ableConnectionFactory.java:290)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory
>> (BasicDataSource.java:877)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDat
>> aSource.java:851)
>> at
>>
> org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
>> at
>>
> org.apache.catalina.realm.DataSourceRealm.open(DataSourceRealm.java:402)
>> at
>>
> org.apache.catalina.realm.DataSourceRealm.authenticate(DataSourceRealm.java:282)
>> at
>>
> org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:256)
>> at
>>
> org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:391)
>> at
>>
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
>> at
>>
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
>> at
>>
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:526)
>> at
>>
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
>> at
>>
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
>> at
>>
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
>> at
>>
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
>> at
>>
> org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
>> at
>>
> org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
>> at
>>
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
>> at java.lang.Thread.run(Thread.java:595)
>>
>>
>> ** END NESTED EXCEPTION **
>>
>> In server.xml, I define the resource in
>> GlobalNamingResources as follows:
>> <Resource
>> name="db"
>> type="javax.sql.DataSource"
>> password="test"
>> driverClassName="com.mysql.jdbc.Driver"
>> maxIdle="2"
>> maxWait="5000"
>> username="user"
>>
>>
> url="jdbc:mysql://localhost:3306/myapp?autoReconnect=true"
>> maxActive="4"/>
>>
>> and the realm:
>> <Realm
>>
>>
> className="org.apache.catalina.realm.DataSourceRealm"
>> dataSourceName="users"
>> userTable="users"
>> userNameCol="user_name"
>> userCredCol="user_pass"
>> userRoleTable="user_roles"
>> roleNameCol="role_name"/>
>>
>> Martin
>>
>>
>>
>> __________________________________
>> Do you Yahoo!?
>> Yahoo! Small Business - Try our new resources site!
>> http://smallbusiness.yahoo.com/resources/
>>
> === message truncated ===
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Personals - Better first dates. More second dates.
> http://personals.yahoo.com
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org