Spring-mvc project can't select from a particular mysql table

Question

I'm building a Spring-mvc project (using JPA and Hibernate for DB access) that is running just great locally, on my dev box, with a local MySQL database.

Now I'm trying to put a snapshot up on a staging server for my client to play with, and I'm having trouble.

Tomcat (after some wrestling) deploys my war file without complaint, and I can get some response from the application over the browser.

When I hit my main page, which is behind Spring Security authentication, it redirects me to the login page, which works perfectly. I have Security configured to query the database for user details, and that works fine. In fact, a change to a password in the database is reflected in the behavior of the login form, so I'm confident it IS reaching the database and querying the user table.

Once authenticated, we go to the first "real" page of the app, and I get a "data access failure" error. The server's console log gets this line (redacted):

ERROR org.hibernate.util.JDBCExceptionReporter - SELECT command denied to user
'myDbUser'@'localhost' for table 'asset'

However, if I go to MySQL from the shell using exactly the same creds, I have no problem at all selecting from the asset table:

[development@tomcat01stg]$ mysql -u myDbUser  -pmyDbPwd dbName

...

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1

Connection id:      199
Current database:   dbName
Current user:       myDbUser@localhost
...
UNIX socket:        /var/lib/mysql/mysql.sock
--------------

mysql> select count(*) from asset;
+----------+
| count(*) |
+----------+
|       19 | 
+----------+
1 row in set (0.00 sec)

I've broken down my MySQL access settings, cleaned out the user and re-run the grant commands, set up a version of the user from 'localhost' and another from '%', making sure to flush permissions.... Nothing is changing the behavior of this thing.

What gives?

Answer 1

Figured it out.

Turns out the database config you put in /src/main/resources/spring/database.properties is used for the database connection, but the schema you give Roo when it does its "database reverse engineer" gets populated through all the Roo-created model classes, and that's used to specify the table (schema.table) in queries.

The staging server's database name is different from the one on my local box. So the table names I was using, filled in with the schema name for my local db, was wrong. MySQL gives a lame error message about having no permission to SELECT, when that happens, but what's really going on is a schema name mismatch.

Quickest thing to do was to recreate my database on the staging server with a name that matched that on my dev box. That solved this.