Grails and MySQL Connection Errors

I ran into a few configuration problems when I tried to push a Grails webapp into production and make it talk to a MySQL database instead of the standard HSQL database. These notes might be useful to someone who is pulling their hair out trying to get Grails talking to MySQL database.

My production machine is a CentOS 5 box running MySQL 5, Sun JDK 5, and Tomcat 5.5 (incidentally, my webapp's version happened to be 0.5 which rounds out the '5' theme nicely).

I started by pulling down the MySQL JDBC driver and dropping it into my lib directory. I then updated the production section in my DataSource.groovy file:

...
 production {
       dataSource {
           pooling = true
           driverClassName = "com.mysql.jdbc.Driver"
           dbCreate = "create"
           url = "jdbc:mysql://localhost:3306/grails_webapp"
           username = "grailsuser"
           password = "XXXX"
       }
   }
...

I then grails wared things up on my local machine and copied it over to the production server.

Over on the production server, I connected to MySQL with the mysql command line utility to create my database and user:

create database grails_webapp;
grant all privileges on grails_webapp.* to 'grailsuser'@'localhost' identified by 'XXXX';
flush privileges;

I verified things worked by connecting to the new database as the newly created user and creating a table.

I dropped the war into my tomcat/webapps directory and watched the logs. The webapp tries to start but quickly dies, leaving behind a log full of Communications link failure and Connection refused messages. I could see that the MySQL JDBC driver was found, but for whatever reason it couldn't connect to the database server.

After double checking my DataSource.groovy file to make sure my URL, username, and password were all spelled correctly and connecting again locally from the command line to ensure that the database is indeed running, I started Googling.

Google revealed that one common problem is to forget to enable networking in MySQL. I double checked my my.cnf and confirmed that I had enabled networking by specifying a port and an address to bind to. This is where I noticed that the bound address wasn't localhost or 127.0.0.1, but the actual IP address of the machine.

Normally you would expect MySQL to only need to listen locally. In my case, however, the production MySQL server replicates to a slave server for backup, so it needs to listen on the external interface. The firewall locked down to prevent all connections to the MySQL port except from the slave server.

On a hunch, I went back and changed my database URL to jdbc:mysql://the.ip.addr.ess:3306/grails_webapp and tried re-deploying. Things still failed, but with access denied errors instead of connection refused errors. Progress.

The final piece of the puzzle was to go and grant my grailsuser access on all addresses instead of just localhost:

grant all privileges on grails_webapp.* to 'grailsuser'@'%' identified by 'XXXX';
flush privileges;

Once this was done, I re-deployed again and was greeted with an error free log. Huzzah!