Jooq: Parser cannot handle LOCK TABLES

[bennofs]

Your question:

I have a ddl script that has the following at the end:

LOCK TABLES `schema_migrations` WRITE;
UNLOCK TABLES;

(https://github.com/amacneil/dbmate adds this to the schema.sql files it generates)

Now, I wonder, should the parser support these statements? It's hard for me to annotate them, since the schema is generated by a tool. But of course, I understand the supporting every vendor-specific command is probably not feasible?

Here's the error:

SEVERE: An exception occurred while parsing script source : schema.sql. Please report this error to https://github.com/jOOQ/jOOQ/issues/new
org.jooq.impl.ParserException: Unsupported query type: [1:1] [*]LOCK TABLES `schema_migrations` WRITE;
UNLOCK TABLES;

    at org.jooq.impl.ParserContext.exception(ParserImpl.java:10675)
    at org.jooq.impl.ParserImpl.parseQuery(ParserImpl.java:951)
    at org.jooq.impl.ParserImpl.parse(ParserImpl.java:542)
    at org.jooq.impl.ParserImpl.parse(ParserImpl.java:529)
    at org.jooq.meta.extensions.ddl.DDLDatabase.load(DDLDatabase.java:188)
    at org.jooq.meta.extensions.ddl.DDLDatabase.access$100(DDLDatabase.java:91)
    at org.jooq.meta.extensions.ddl.DDLDatabase$2.load(DDLDatabase.java:169)
    at org.jooq.meta.tools.FilePattern.load(FilePattern.java:165)
    at org.jooq.meta.tools.FilePattern.load(FilePattern.java:116)
    at org.jooq.meta.extensions.ddl.DDLDatabase.create0(DDLDatabase.java:166)
    at org.jooq.meta.AbstractDatabase.create(AbstractDatabase.java:260)
    at org.jooq.meta.AbstractDatabase.create(AbstractDatabase.java:249)
    at org.jooq.meta.AbstractDatabase.setConnection(AbstractDatabase.java:239)
    at org.jooq.codegen.GenerationTool.run0(GenerationTool.java:501)
    at org.jooq.codegen.GenerationTool.run(GenerationTool.java:221)
    at org.jooq.codegen.GenerationTool.generate(GenerationTool.java:216)
    at org.jooq.codegen.GenerationTool.main(GenerationTool.java:188)

Jan 29, 2020 3:48:11 PM org.jooq.tools.JooqLogger error
SEVERE: Cannot read ./config.xml. Error : Unsupported query type: [1:1] [*]LOCK TABLES `schema_migrations` WRITE;
UNLOCK TABLES;

org.jooq.impl.ParserException: Unsupported query type: [1:1] [*]LOCK TABLES `schema_migrations` WRITE;
UNLOCK TABLES;

    at org.jooq.impl.ParserContext.exception(ParserImpl.java:10675)
    at org.jooq.impl.ParserImpl.parseQuery(ParserImpl.java:951)
    at org.jooq.impl.ParserImpl.parse(ParserImpl.java:542)
    at org.jooq.impl.ParserImpl.parse(ParserImpl.java:529)
    at org.jooq.meta.extensions.ddl.DDLDatabase.load(DDLDatabase.java:188)
    at org.jooq.meta.extensions.ddl.DDLDatabase.access$100(DDLDatabase.java:91)
    at org.jooq.meta.extensions.ddl.DDLDatabase$2.load(DDLDatabase.java:169)
    at org.jooq.meta.tools.FilePattern.load(FilePattern.java:165)
    at org.jooq.meta.tools.FilePattern.load(FilePattern.java:116)
    at org.jooq.meta.extensions.ddl.DDLDatabase.create0(DDLDatabase.java:166)
    at org.jooq.meta.AbstractDatabase.create(AbstractDatabase.java:260)
    at org.jooq.meta.AbstractDatabase.create(AbstractDatabase.java:249)
    at org.jooq.meta.AbstractDatabase.setConnection(AbstractDatabase.java:239)
    at org.jooq.codegen.GenerationTool.run0(GenerationTool.java:501)
    at org.jooq.codegen.GenerationTool.run(GenerationTool.java:221)
    at org.jooq.codegen.GenerationTool.generate(GenerationTool.java:216)
    at org.jooq.codegen.GenerationTool.main(GenerationTool.java:188)

Versions:

• jOOQ: 3.12.3
• Java: OpenJDK 64-Bit Server VM (build 25.242-b08, mixed mode)
• Database (include vendor): DDLDatabase
• OS: Linux
• JDBC Driver (include name if inofficial driver):

[lukaseder]

Thank you very much for your message.

We support a specific comment syntax in our parser for all syntax that we cannot (yet) parse:
https://www.jooq.org/doc/3.12/manual/sql-building/dsl-context/custom-settings/settings-parser/

Specifically, you can write:

/* [jooq ignore start] */
LOCK TABLES `schema_migrations` WRITE;
UNLOCK TABLES;
/* [jooq ignore stop] */

This will not have any effect on your real database product, which doesn't understand this comment syntax, but will prevent the statements above from being parsed by our parser.

We could think about supporting that LOCK TABLES syntax, but that seems to be quite an effort, which is probably not worth it right now.

As you're running into quite a few limitations of our parser and/or emulation through H2, I'd like to repeat that suggestion of using https://www.testcontainers.org as a replacement, which will be much more reliable...

[bennofs]

Ok, thank you for you patience with my questions :smile: The reason for not using testcontainers is that I don't want my build process to depend on running docker containers. However, I just now realized that checking in the generated meta source code is probably not as bad as I thought before, and it should solve most of my requirements.

[lukaseder]

However, I just now realized that checking in the generated meta source code is probably not as bad as I thought before, and it should solve most of my requirements.

Yes, that does solve a lot of issues indeed :-)

[knutwannheden]

The reason for not using testcontainers is that I don't want my build process to depend on running docker containers.

Since it isn't entirely clear from your phrasing, I would like to clarify that with Testcontainers your host only has to have a Docker daemon running. The container required for the tests will be downloaded and started (and stopped) automatically by the framework.