Previously posted on blog.labrat.info on November 11, 2010
When trying to get Grails to work on one of my machines I kept getting the following error when I tried to deploy the production WAR file.
nested exception is org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Access denied for user 'test_user'@'127.0.0.1' (using password: YES))
This had me really confused because I had changed nothing except where the database was. In the dev and testing environment I’m using a remote database while in production environment the database is on the same machine as the code. I took the same WAR file and moved it over to a Linux machine and everything seems to work with no problems there. This got me even more confused.
After staring at the error for a little while I narrowed the problem down to how I was setting up the user’s permissions in MySQL. They way I created the user’s permissions on all machines was this:
GRANT ALL PRIVILEGES ON appdb.* to 'appuser'@'localhost' IDENTIFIED BY 'super-secret-pass' ;
Turns out MySQL on FreeBSD really doesn’t like this when you have the DB setup in Grails as “localhost”. For some reason I still don’t quite understand it uses the IP and not the DNS alias when authenticating. I can see how this might be faster. No need to do name resolution, but still, just makes thing harder.
The fix was to just create the user with the host set to 127.0.0.1.
GRANT ALL PRIVILEGES ON appdb.* to 'appuser'@'127.0.0.1' IDENTIFIED BY 'super-secret-pass' ;