codegen errors on mysql

[Darren S]

Does anybody get the follow error when running the codegen on MySQL

SELECT command denied to user 'dstack'@'localhost' for table 'proc'

I've included the full stack trace below.  In the end it doesn't cause any problems, but it just makes it seems like the generation failed.  I do have the config

            <excludes>.*proc</excludes>

But that doesn't seem to matter.  I stepped through the code once and it seemed to be with the procs that the error happened before it ever processed the include/exclude logic.  (Ignore the fact that you see Drizzle in the stack trace.  I'm using the Drizzle JDBC driver w/ MySQL, its still MySQL 5.5.  Same issue happens with the Connector/J MySQL JDBC driver)

Thanks,
Darren

14:43:08.543 [org.jooq.util.GenerationTool.main()] ERROR org.jooq.util.AbstractDatabase - Error while fetching functions
org.jooq.exception.DataAccessException: SQL [select `mysql`.`proc`.`db`, `mysql`.`proc`.`name`, `mysql`.`proc`.`comment`, `mysql`.`proc`.`param_list`, `mysql`.`proc`.`returns`, `mysql`.`proc`.`type` from `mysql`.`proc` where `mysql`.`proc`.`db` in (?) order by `mysql`.`proc`.`db` asc, `mysql`.`proc`.`name` asc]; SELECT command denied to user 'dstack'@'localhost' for table 'proc'
    at org.jooq.impl.Utils.translate(Utils.java:1254) ~[jooq-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:495) ~[jooq-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:326) ~[jooq-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:330) ~[jooq-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2256) ~[jooq-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.util.mysql.MySQLDatabase.getRoutines0(MySQLDatabase.java:326) ~[jooq-meta-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.util.AbstractDatabase.getRoutines(AbstractDatabase.java:702) ~[jooq-meta-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:279) [jooq-codegen-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:212) [jooq-codegen-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.util.GenerationTool.run(GenerationTool.java:347) [jooq-codegen-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.util.GenerationTool.main(GenerationTool.java:172) [jooq-codegen-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.util.GenerationTool.main(GenerationTool.java:159) [jooq-codegen-3.3.0-SNAPSHOT.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_45]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_45]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_45]
    at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_45]
    at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:297) [exec-maven-plugin-1.2.1.jar:na]
    at java.lang.Thread.run(Thread.java:744) [na:1.7.0_45]
Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'dstack'@'localhost' for table 'proc'
    at org.drizzle.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:99) ~[drizzle-jdbc-1.2.jar:na]
    at org.drizzle.jdbc.DrizzlePreparedStatement.execute(DrizzlePreparedStatement.java:167) ~[drizzle-jdbc-1.2.jar:na]
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194) ~[jooq-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:261) ~[jooq-3.3.0-SNAPSHOT.jar:na]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:322) ~[jooq-3.3.0-SNAPSHOT.jar:na]
    ... 15 common frames omitted
Caused by: org.drizzle.jdbc.internal.common.QueryException: SELECT command denied to user 'dstack'@'localhost' for table 'proc'
    at org.drizzle.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:464) ~[drizzle-jdbc-1.2.jar:na]
    at org.drizzle.jdbc.DrizzlePreparedStatement.execute(DrizzlePreparedStatement.java:164) ~[drizzle-jdbc-1.2.jar:na]
    ... 18 common frames omitted

[Lukas Eder]

Hi Darren,

That is curious. jOOQ needs to query both the `information_schema` and the `mysql` databases/schemas to reverse-engineer stuff. Obviously, the database user you're running the code generator with will need proper grants to both meta-schemas. Can you display those grants through

SHOW GRANTS FOR dbstack

http://dev.mysql.com/doc/refman/5.7/en/show-grants.html

2014/1/16 Darren S <darren.s...@gmail.com>

--
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+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Darren Shepherd]

GRANT USAGE ON *.* TO 'dstack'@'%' IDENTIFIED BY PASSWORD '...'
GRANT ALL PRIVILEGES ON `dstack`.* TO 'dstack'@'%'

Well I guess that is the problem. So information_schema is
automatically allowed by all users, but mysql is not. I'd rather not
grant access to mysql as I don't know what the security implication
are of that. Is mysql schema only needed for procs? Is it possible
to disable the introspection of procs?

Darren

> You received this message because you are subscribed to a topic in the

> Google Groups "jOOQ User Group" group.

> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/jooq-user/WpUnYQSDIlY/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

[Lukas Eder]

Alright, that's what I thought. In the jOOQ integration tests, the MySQL "root" user is usually used for code generation, which is why this stack trace has not yet popped up.

Unfortunately, the information_schema does not provide all the required information to reverse-engineer stored procedures - at least not in older versions of MySQL. It would make sense to check for the availability of this table before querying it. This is the same as the various checks to see if any given Oracle dictionary view is available to distinguish between 10g, 11gR1, 11gR2, 12c.

I'll see what I can do:

https://github.com/jOOQ/jOOQ/issues/2958

Cheers

Lukas

2014/1/17 Darren Shepherd <darren.s...@gmail.com>

[Lukas Eder]

This is now fixed on GitHub master and will be released in jOOQ 3.3 and 3.2.3

2014/1/18 Lukas Eder <lukas...@gmail.com>

[Darren Shepherd]

Thanks, just tried in 3.3 and it worked, got the below message

08:59:53.846 [org.jooq.util.GenerationTool.main()] WARN
org.jooq.util.mysql.MySQLDatabase - Table unavailable : The
`mysql`.`proc` table is unavailable. Stored procedures cannot be
loaded. Check if you have sufficient grants

Darren