org.jooq.exception.DataAccessException (...) SELECT command denied to user

[dan.a...@gmail.com]

Hey guys,

I'm looking into migrating some vanilla Java code (PreparedStatements etc.) to JOOQ. Everything works great on my development machine, but when I try to run the code on the production machine, using the exact same permissions for the user, I run into a permissions issue.

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [select `mydb`.`flags`.`field`, `mydb`.`flags`.`value` from `mydb`.`flags`]; SELECT command denied to user 'java'@'[IP_goes_here]' for table 'flags'

Code used to create the user:

CREATE USER 'java'@'%' IDENTIFIED BY '[pass_goes_here]';

GRANT ALL PRIVILEGES ON MyDB.* TO 'java'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES; 

The non-JOOQ code works fine, I am able to login with that username just fine using the command line, but JOOQ just throws that error. I'm sure I'm missing some permission, but I can't figure out what.

The code that throws that exception:

DSL.using(staticConnection, SQLDialect.MYSQL)

.select(Tables.FLAGS.FIELD, Tables.FLAGS.VALUE)

.from(Tables.FLAGS)

.fetch();

What I tried so far:

- Upgraded from Percona 5.6 to 5.7

- Downgrade from MySQL 6 to 5 and back again

- Deleting and re-creating the user.

What do you think's going on?

[Lukas Eder]

Hello,

Just guessing, this looks like a case sensitivity issue that mimght arise on Windows installations only. Your query uses lower case `mydb` (because that's what the code generator reverse engineered, so that's the proper case of your database name), whereas your grant uses upper case MyDB.* 

Is that it?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[dan.a...@gmail.com]

Thanks to your comment, I got to the bottom of it. Here's more information:

- The database name contained uppercase characters, both on the dev machine (Windows) and in production (Linux).

- The code generator was given the proper uppercase name, both in the <url> and the <includes> tags, but for some reason, it still used the lowercase DB name in the generated queries.

What I ended up doing was renaming the database to be all lower case, re-granting premissions, and re-generating the code. Everything is fine now.

Thanks!

Hello,

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

I knew it had to do with case-sensitivity! That's such a sore spot in MySQL, crazy to think that there's some OS dependent behaviour encoded in case sensitive database names... Just recently, I've reported another bug to MySQL about the database reporting the wrong case in its INFORMATION_SCHEMA views, which is related to yours:

https://bugs.mysql.com/bug.php?id=86022

Another option would be to specify the RenderNameStyle.AS_IS in your Settings (in your Configuration). More info here:

https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-name-style

Hope this helps,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.