Using maven jooq codegen plugin without the access to mysql database

Question

We are using the jooq codegen plugin to produce dao and pojo classes for one of the MySQL databases. The configuration is as follows:

                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>3.14.3</version>
                <configuration>
                    <jdbc>
                        <driver>com.mysql.jdbc.Driver</driver>
                        <url>MYSQL_HOST</url>
                        <user>MYSQL_USER</user>
                        <password>MYSQL_PASSWORD</password>
                    </jdbc>
                    <generator>
                        <database>
                            <includes>some_db.*</includes>
                        </database>
                        <generate>
                            <daos>true</daos>
                            <pojos>true</pojos>
                        </generate>
                        <target>
                            <packageName>mysql</packageName>
                            <directory>target/generated-sources</directory>
                        </target>
                    </generator>
                </configuration>
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>

When the generate goal of the plugin is triggered, it fails with the following error:

 Error running jOOQ code generation tool: Error generating code for catalog: org.jooq.exception.DataAccessException: SQL [select mysql.proc.db as ROUTINE_SCHEMA, mysql.proc.name as ROUTINE_NAME, mysql.proc.comment as ROUTINE_COMMENT, mysql.proc.param_list, mysql.proc.returns, mysql.proc.type as ROUTINE_TYPE from mysql.proc where mysql.proc.db in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) order by 1, 2, 6]; SELECT command denied to user 'MYSQL_USER'@'some_ip' for table 'proc' -> [Help 1]

Note that the use MYSQL_USER does not have and cannot have access to the mysql database. Is there some way that we can still go ahead and use the codegen plugin?

Update: Tried with the same configuration with plugin version 3.10.5. There was no error.

Answer 1

Why jOOQ accesses the mysql database

There's no other way for jOOQ to reverse engineer your live MySQL database other than accessing the information_schema and the mysql databases, which contain the necessary meta data for jOOQ's code generator.

Turn off generation of routines

If you can't grant your code generator user with the necessary grants, then the relevant queries will simply not work. The exception could be seen as cosmetic, as the code generator will then simply skip generating code for your stored procedures.

You could turn off the generation of routines by specifying:

<includeRoutines>false</includeRoutines>

See the manual for details.

Using a local test database instead

Alternatively, why use your production database to generate jOOQ code, when you can setup a testcontainers database specifically for the code generation task? It is likely you're using testcontainers or some other similar product for your integration testing anyway, so setting up your schema for the code generator shouldn't be too much extra work?