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, Apr 14 '14 at 11:01
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, Apr 14 '14 at 11:18
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, Apr 14 '14 at 13:29

V H · Answer 1 · 2014-04-14T10:52:37.070

0

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>

edited Apr 14 '14 at 10:52

answered Apr 14 '14 at 10:43

V H

8,382
2
28
48

Hi, I just checked, and this block is already in exactly like this in server.xml. Are you saying I need to create users in tomcat-users.xml, and if so, how do I pass this user information into tomcat? – John Little Apr 14 '14 at 10:48
I was just saying I have seen this working whilst it has been configured in server.xml, your post mentioned the block was in context.xml – V H Apr 14 '14 at 10:51
Understood. I put the config in server.xml, as you suggest, and removed it from context.xml, now I get a new error: Caused by: javax.naming.NameNotFoundException: Name [repDB] is not bound in this Context. Unable to find [repDB]. – John Little Apr 14 '14 at 11:36
http://stackoverflow.com/questions/16737754/javax-naming-namenotfoundexception-name-is-not-bound-in-this-context-unable-to hi John try this link it looks like it needs to exist in both files – V H Apr 14 '14 at 12:36
Hi, I tried putting it in both places. I dont have the problem that it cant find the datasource, it is finding it, it is connecting to a DB, but it cant select from the table for some reason. – John Little Apr 14 '14 at 12:42
The same db connection details work in the non-tomcat version, and also using mysql workbench, yes. If I edit say the jndiname in the context.xml in tomcat, and star the app, it then says could not find jndi context, so its definitly finding it, and connecting to the DB. No idea why it then cant see the table wihch is correctly there. – John Little Apr 14 '14 at 13:04

score 0 · Answer 2 · answered Apr 14 '14 at 13:41

0

OK. I found the answer. It was very stupid as one might expect.

I spent datasSource dataSouce in the DataSource.groovy file

so table read error = no dataSource defined.

answered Apr 14 '14 at 13:41

John Little

10,707
19
86
158

grails: table not found when using tomcat and jndi

2 Answers2