How do I set up a Realm with a MySql Connection Pool in Tomcat 7?

Question

I am trying to set up a Realm using a MySql Connection Pool in Tomcat 7. I have read all the documentation and read through many similar problems people have had, all with no luck.

I have cofigured Tomcat 7 for a connection pool which I successfully tested using a test.jsp (Figure 1).

Figure 2 is my context file for the application. Note that the commented out realm works (obviously when not commented out), but I believe that does not use a connection pool or jndi resource. Instead it creates a new connection each time. The realm that is not commented out, references the JNDI configured connection pool. This is the same JNDI resource the test code in figure 1 uses. However, when the realm attempts to authenticate a user login request, it throws an exception (figure 3) saying the Name jdbc is not bound in this Context. Am I mistaken in my use of a DataSourceRealm here? What am I missing?

Figure 1 - test.jsp (works fine):

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<sql:query var="rs" dataSource="jdbc/TestDB">
select email, password from users
</sql:query>

<html>
<head>
    <title>DB Test</title>
</head>
<body>

    <h2>Results</h2>

    <c:forEach var="row" items="${rs.rows}">
        Email ${row.email}<br/>
        Password ${row.password}<br/>
    </c:forEach>

</body>
</html>

Figure 2 - Context.xml:

<context>

  <Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
    factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" initialSize="10"
    jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState; 
    org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
    jmxEnabled="true" logAbandoned="true" maxActive="100" maxWait="10000"
    minEvictableIdleTimeMillis="30000" minIdle="10" name="jdbc/TestDB"
    password="security" removeAbandoned="true" removeAbandonedTimeout="60"
    testOnBorrow="true" testOnReturn="false" testWhileIdle="true"
    timeBetweenEvictionRunsMillis="30000" type="javax.sql.DataSource"
    url="jdbc:mysql://localhost:3306/estimate" username="security"
    validationInterval="30000" validationQuery="SELECT 1" />

  <!-- <Realm className="org.apache.catalina.realm.JDBCRealm" -->
  <!-- driverName="com.mysql.jdbc.Driver" -->
  <!-- connectionURL="jdbc:mysql://localhost:3306/estimate?user=security&amp;password=security" -->
  <!-- userTable="users" userNameCol="email" userCredCol="password" -->
  <!-- userRoleTable="user_roles" roleNameCol="role" /> -->

  <Realm className="org.apache.catalina.realm.DataSourceRealm"
    dataSourceName="jdbc/TestDB" userTable="users" userNameCol="email"
    userCredCol="password" userRoleTable="user_roles" roleNameCol="role" />

</context>

Figure 3 - the exception I get:

Jul 05, 2013 2:16:03 PM org.apache.catalina.realm.DataSourceRealm open
SEVERE: Exception performing authentication
javax.naming.NameNotFoundException: Name jdbc is not bound in this Context
    at org.apache.naming.NamingContext.lookup(NamingContext.java:803)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:159)
    at org.apache.catalina.realm.DataSourceRealm.open(DataSourceRealm.java:394)
    at org.apache.catalina.realm.DataSourceRealm.authenticate(DataSourceRealm.java:285)
    at org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:282)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:440)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:395)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:250)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

It'll probably be some dumb mistake I'm making but been working on it for days now.

http://stackoverflow.com/questions/13730192/tomcat-jdbc-vs-datasource-realm ? Looks like all you're missing is the same as the OP there - `localDataSource="true"` in the `Realm` — Brian Roach, Jul 05 '13 at 21:53
Thank you! I spent hours looking through forums, documents, and searches but never saw that one. Working great! Post as a answer and I'll mark it. — Ben Zuill-Smith, Jul 05 '13 at 22:08
Nah, all I did was a good google; upvote the answer there - they're the one that deserves it. — Brian Roach, Jul 05 '13 at 22:15

score 1 · Answer 1 · answered Jan 21 '14 at 09:01

Met the same problem with you, solved it by adding localDataSource="true" to the realm.

<Realm className="org.apache.catalina.realm.DataSourceRealm"
    localDataSource="true"
    dataSourceName="jdbc/Tomcat"
    userTable="users" userNameCol="user_name" userCredCol="user_pass"
    userRoleTable="user_roles" roleNameCol="role_name"/>

How do I set up a Realm with a MySql Connection Pool in Tomcat 7?

1 Answers1