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 2017-06-14 21:55 GMT+02:00 <[email protected]>: > 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! > > On Wednesday, June 14, 2017 at 10:06:44 PM UTC+3, Lukas Eder wrote: >> >> 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? >> >> 2017-06-14 18:50 GMT+02:00 <[email protected]>: >> >>> 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? >>> >>> -- >>> 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 [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
