Grails database configuration

Question

I'm trying to make some changes to the database configuration for a Grails application. Grails version is 2.5.3. The goal is to remove hard coded dependencies to MySql to be able to use the application with other database providers. I am also trying to run locally with environment set to prod and with a local MySql database. (To be able to test my changes without deploying, since the "dev" environment uses the H2 database which is set up quite different.) So I'm starting with mvn grails:run-app -Dgrails.env=prod.

I'm not very experienced with Grails and a lot of things seems to happen "magically" which makes troubleshooting a little difficult.

Some configuration files that seems to be involved are:

context.xml. As I understand it this is a tomcat configuration file on the server (outside the application). There is also a myapp/resources/tomcat-conf/context.xml which I believe is used when I'm running locally. context.xml contains a database configuration like this:

<Context>
    ...
    <Resource name="jdbc/TP" auth="Container" type="javax.sql.DataSource"
      maxActive="10" maxIdle="5" maxWait="10000" username="xxx" password="xxx"
      driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://xxx:3306/xxx?autoReconnect=true" validationQuery="select 1"
    />
    ...
</Context>

Then there is a myapp/src/main/resources/myapp-PROD.xml

<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="showSql" value="false" />
            <property name="generateDdl" value="false" />
            <property name="database" value="MYSQL" />
        </bean>
    </property>
</bean>

<bean id="dataSource"
    class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="java:comp/env/jdbc/TP"/>   
    <property name="resourceRef" value="true" />
</bean>

I would like to get rid of the hardcoded "MYSQL" here and preferable be able to get that from context.xml (or a properties file on the server).

myapp-PROD.xml also imports myapp-config.xml which contains:

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

Then there is some configuration in myapp/grails-app/conf/DataSource.groovy: (Including some outcommented configuration)

environments {
    ...
    production {
        dataSource {
            // driverClassName = "com.mysql.jdbc.Driver"
            // username = "xxx"
            // password = "xxx"
            // url = "jdbc:mysql://xxx:3306/xxx"

            jndiName = "java:comp/env/jdbc/TP"
        }
    }
}

And there is also myapp/grails-app/conf/BootStrap.groovy which does some H2 configuration for "dev" and "test" but no database configuration for "prod".

So I have tried to specify my local MySql database in context.xml. It does not work well. In myapp/target/tomcat/logs/myapp.log I can see this:

*2022-05-23 13:40:01,669 [localhost-startStop-1] DEBUG spring.OptimizedAutowireCapableBeanFactory  - Invoking afterPropertiesSet() on bean with name 'dialectDetector'
2022-05-23 13:40:05,703 [localhost-startStop-1] WARN  spring.GrailsWebApplicationContext  - Exception encountered during context initialization - cancelling refresh attempt
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManagerPostProcessor': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory': Cannot resolve reference to bean 'hibernateProperties' while setting bean property 'hibernateProperties'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'hibernateProperties': Cannot resolve reference to bean 'dialectDetector' while setting bean property 'properties' with key [hibernate.dialect]; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dialectDetector': Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (**Could not create connection to database server. Attempted reconnect 3 times. Giving up.**)*

I don't see any connection attempts in my database logs, so obviously it is not trying to connect to the database i specified in context.xml. I also tried to specify the database in DataSource.groovy, but that didn't work either.

So:

• What do I need to do to run locally with my local MySql database?
• Is there any additional helpful logs I can enable?
• How do I get rid of "MYSQL" in myapp-PROD.xml and get dialect from context.xml instead?

Answer 1

What do I need to do to run locally with my local MySql database?

You need to supply a value for the dataSource driver that points to the Mysql driver and you need to supply a JDBC url that the MySql driver recognizes and points to your local MySql.

How do I get rid of "MYSQL" in myapp-PROD.xml and get dialect from context.xml instead?

The dialect is only 1 piece of the puzzle and probably really isn't the key to the problem. If it really is a requirement to get the dialect from context.xml I don't know how to do it, but a much more common way to deal with the situation is to either use JNDI so you don't have to make any mention of dialects and driver names and user names and passwords in your app. Another option is to put the database config in an external config file that is loaded by the app at startup time.