Tuesday, April 01, 2008

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!

3 comments:

Shawn Hartsock said...

Once you get your MySQL set up right and can talk to it, you can tell the JDBC driver all kinds of things like what persistence engine to use, what data format to use. I recommend trying to use the InnoDB dialect if you can. You would specify it with:

dialect= org.hibernate.dialect.MySQLInnoDBDialect

in your data source.

I've got more observations here:
http://hartsock.blogspot.com/search/label/mysql

I've been working with LAMP stack applications and Grails so I've hit quite a few issues... maybe I'll blog about them all but check out the magical MySQL URL parameters ... they're a trip.

Josh Reed said...

Thanks for the pointer to your blog, Shawn. Lots of good stuff. I added it to my Google Reader.

Cheers,
Josh

Anonymous said...

Nice article as for me. I'd like to read something more concerning this theme. The only thing it would also be great to see here is a few pics of any gadgets.
Katherine Kripke
Phone jammer