Friday, April 10, 2009

On MySQL Being Confused

So I've been using MySQL Query Browser and Administration Tools for years now and I've never been able to figure this one out. I have a user ('openmrs') that was created within the Administration tool and granted all privileges to the 'openmrs' schema on all possible hosts ('openmrs'@'%', 'openmrs'@'localhost', 'openmrs'@'127.0.0.1'). I have verified that the 'openmrs' user can connect to the 'openmrs' schema via the MySQL command-line client and the Query Browser. Yet, the 'openmrs' user used to connect to the database via JDBC within the OpenMRS webapp cannot connect. What gives? I keep getting the following error:


Attempted reconnect 3 times. Giving up.
Could not connect to database using url 'jdbc:mysql://localhost:3306/openmrs?autoReconnect=true&sessionVariables=storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8', username 'openmrs', and pw '*******'. Connection properties can be set with runtime property: 'connection.username', 'connection.password', and 'connection.url'


I'm guessing there might be a mismatch between the mysql.db and mysql.user table that is causing the confusion. I have a workaround, but I really want to know what's going on inside MySQL's brain. For those interested, here's the simple workaround.


grant all on openmrs.* to openmrs;

1 comment:

Adam Monsen said...

Does JDBC require that the user have a password?

The "GRANT" syntax is really finicky. What were the original GRANT statements you tried?