grails: table not found when using tomcat and jndi

Question

Using the dataSource.groovy works. When I switch to using JNDI on tomcat with a war, I get:

2014-04-14 12:16:19,244 [localhost-startStop-1] ERROR util.JDBCExceptionReporter  - Table "REPORT" not found; 
SQL statement: select [...] from report this_ where this_.name=? [42102-173]

This looks like its connecting to the DB ok, but cant see the table, which is bazaar.

Working config in DataSource.groovy (when I run without tomcat):

  development2 {
    dataSource {
        url = 'jdbc:mysql://localhost/rep'
        username = "root"
        password = ""
        dialect = org.hibernate.dialect.MySQL5InnoDBDialect
        pooled = true
        jmxExport = true
    dbCreate = "update"
        driverClassName = "com.mysql.jdbc.Driver"
        properties {
            maxActive = -1
            minEvictableIdleTimeMillis=1800000
            timeBetweenEvictionRunsMillis=1800000
            numTestsPerEvictionRun=3
            testOnBorrow=true
            testWhileIdle=true
            testOnReturn=true
            validationQuery="SELECT 1"
        }
    }

Not working jndi config in DataSource.groovy:

localtomcat {
    dataSouce {
        dbCreate = "update"
        jndiName = "java:comp/env/repDB"
    }

context.xml in tomcat 7:

<Resource name="repDB" auth="Container" type="javax.sql.DataSource"
    maxActive="100" maxIdle="5" maxWait="10000"
    username="root" password="" driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/rep"/>

I also tried this:

localtomcat {
    dataSouce {
        dialect = org.hibernate.dialect.MySQL5InnoDBDialect
        driverClassName = "com.mysql.jdbc.Driver"
        dbCreate = "update"
        jndiName = "java:comp/env/repDB"
    }

Any ideas why it can see the DB, but not the tables (which exist, have data, and don't need updating)

Some posts say you need something in web.xml, some say you dont. I dont have anything in there.

The root user has global permissions on everything.

im building the war thus:

grails -Dgrails.env=localtomcat war

UPDATE: I should add that I actually have two datasources for each environment, the one grails uses, and another one I just query (i.e. a reporting server). This works fine outside of tomcat. the main datasource is called "datasource" and is defined below. The other one is called datasource_reporting. So my DataSource.groovy looks like this:

dataSource {
     pooled = true
     jmxExport = true
}

dataSource_reporting {
dialect = org.hibernate.dialect.MySQL5InnoDBDialect
pooled = true
jmxExport = true
driverClassName = "com.mysql.jdbc.Driver"
properties {
    maxActive = -1
    minEvictableIdleTimeMillis=1800000
    timeBetweenEvictionRunsMillis=1800000
    numTestsPerEvictionRun=3
    testOnBorrow=true
    testWhileIdle=true
    testOnReturn=true
    validationQuery="SELECT 1"
  }
}

environments { production{ dataSouce { dialect = org.hibernate.dialect.MySQL5InnoDBDialect driverClassName = "com.mysql.jdbc.Driver" dbCreate = "update" jndiName = "java:comp/env/rep" }

    dataSource_reporting {
        dialect = org.hibernate.dialect.MySQL5InnoDBDialect
        driverClassName = "com.mysql.jdbc.Driver"
            jndiName = "java:comp/env/reporting"
    }
}

And then I have two corresponding entires in context.xml on tomcat:

<Resource name="reporting" auth="Container" type="javax.sql.DataSource"
    maxActive="20" maxIdle="5" maxWait="20000"
    username="someuser" password="somepass" driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://xx.xx.xx.xx:3306/somemaindb"/>

<Resource name="rep" auth="Container" type="javax.sql.DataSource"
    maxActive="100" maxIdle="5" maxWait="10000"
    username="root" password="" driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost/rep"/>

Perhaps grails + tomcat cant handle two DB connections?

the rep DB has the report table which its complaining is missing, and the permissions of the root user are god. Perhaps grails is trying to open the reprot table on the wrong DB?

I have some more info. If I shut down my database, I still get the same error. Now I am REALLY confused. Perhaps grails has the environments mixed up, and its trying to use the in memory development version incorrectly? — John Little
OK, I tried commenting out dev and production datasoures, and commented out every occurance of the in memory db url, and get the same table not found error. Looks like my app can never go live. — John Little
OK, if I try changing the username to an invalid one for the secondary db, it complains that it could not connect. if I change the details on the first db, it does not complain. That means is only opening one of the two defined databsources, and its trying to read the grails domain objects from the wrong db. — John Little

V H V H · Accepted Answer · 2014-04-14T10:43:43

we have these in our server.xml try looking for this block and putting it within GlobalNamingResources

GlobalNamingResources>
    <!-- Editable user database that can also be used by
         UserDatabaseRealm to authenticate users
        -->
<Resource name="repDB" auth="Container" type="javax.sql.DataSource"
    maxActive="100" maxIdle="5" maxWait="10000"
    username="root" password="" driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/rep"/>

    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
              description="User database that can be updated and saved"
              factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
              pathname="conf/tomcat-users.xml" />
  </GlobalNamingResources>

grails: table not found when using tomcat and jndi

2 Answers