Codegen hangs when generating first table with mysql

[Oliver Schneider]

I'm currently experimenting with jooq and want to introduce it into my company.
I don't wanna see constructed sql strings in code anymore.

I am stuck at trying to generate the code from our mysql db server (it works with a local mysql installation).

The log output I get is something like this:
INFO: Schema generated         : Total: 196.216ms
Aug 13, 2014 10:07:18 AM org.jooq.tools.JooqLogger info
INFO: Sequences fetched        : 0 (0 included, 0 excluded)
Aug 13, 2014 10:07:19 AM org.jooq.tools.JooqLogger info
INFO: Masterdata tables fetched: 0 (0 included, 0 excluded)
Aug 13, 2014 10:07:19 AM org.jooq.tools.JooqLogger info
INFO: Tables fetched           : 19 (19 included, 0 excluded)
Aug 13, 2014 10:07:19 AM org.jooq.tools.JooqLogger info
INFO: Generating tables        : target/generated-sources/jooq-mysql/example/mysql/tables
Aug 13, 2014 10:07:19 AM org.jooq.tools.JooqLogger info
INFO: Generating table         : SomeTable.java.....

and nothing happens after that. I have to abort the process.

I've been through the documentation and tried the main class approach with a xml configuration
as well as the in Java configuration and mostly with pom maven codegen plugin but I always end up
that the executing halts and won't continue.

Perhaps anyone has any idea or experienced something similiar?
Do I perhaps need a special mysql-connector version or version combination?

My local mysql has:
version                 5.5.38-0ubuntu0.12.04.1
version_comment (Ubuntu)        


Our db server has:
version                 5.5.34-32.0
version_comment  Percona Server (GPL), Release 32.0

Thx for reading
and greetings from Berlin,
Oliver

[Lukas Eder]

Hello Oliver,

That's curious. I'm not aware of any limitation to any given MySQL or JDBC version.

What happens next after that log entry is a query on the information_schema.columns table. Can you turn on DEBUG logging to see what happens around that step? More information here:

http://www.jooq.org/doc/latest/manual/sql-execution/logging

Cheers

Lukas

--
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/d/optout.

[Oliver Schneider]

Hello Lukas, thx for the quick response.

Turning on logging (should have come up with that myself ;)) it turns out jooq
is doing exactly what you said:

Executing query          : select `information_schema`.`KEY_COLUMN_USAGE`.`CONSTRAINT_NAME`, `information_schema`.`KEY_COLUMN_USAGE`.`TABLE_NAME`, `information_schema`.`KEY_COLUMN_USAGE`.`COLUMN_NAME` from `information_schema`.`KEY_COLUMN_USAGE` join `information_schema`.`TABLE_CONSTRAINTS` on (`information_schema`.`KEY_COLUMN_USAGE`.`TABLE_SCHEMA` = `information_schema`.`TABLE_CONSTRAINTS`.`TABLE_SCHEMA` and `information_schema`.`KEY_COLUMN_USAGE`.`TABLE_NAME` = `information_schema`.`TABLE_CONSTRAINTS`.`TABLE_NAME` and `information_schema`.`KEY_COLUMN_USAGE`.`CONSTRAINT_NAME` = `information_schema`.`TABLE_CONSTRAINTS`.`CONSTRAINT_NAME`) where (`information_schema`.`TABLE_CONSTRAINTS`.`CONSTRAINT_TYPE` = 'PRIMARY KEY' and `information_schema`.`KEY_COLUMN_USAGE`.`TABLE_SCHEMA` = 'vm144_amazon') order by `information_schema`.`KEY_COLUMN_USAGE`.`TABLE_NAME` asc, `information_schema`.`KEY_COLUMN_USAGE`.`ORDINAL_POSITION` asc

And exactly here is the problem I think: we use one DB for all our vms (around 100) an each vm has a separate schema for all components.
Those are quite a few hundred tables in the end. I tried reducing the query to the minimun: SELECT * from `information_schema`.`KEY_COLUMN_USAGE` LIMIT 50
but this statement runs 'forever' as well. Because I got no idea about this information_schema I searched and found this page:
http://www.pythian.com/blog/how-to-tell-when-using-information_schema-might-crash-your-database/
and doing an EXPLAIN ..... showed: 'Open_full_table; Scanned all databases'

So I think it is not hanging but just will take forever to complete that statement (if the DB doesn't crash before or runs into a timeout).
Do you perhaps have any idea, if I can work around this in case my assumption is right? Perhaps limit it only to query one schema?

Thx,
Oliver

[Lukas Eder]

Hello Oliver,

Thank you very much for this feedback. That's quite unfortunate, though. There is a schema predicate in all of jOOQ's meta queries already. In your case, there's

`information_schema`.`KEY_COLUMN_USAGE`.`TABLE_SCHEMA` = 'vm144_amazon'

I don't think there's much more that can be done from a SQL perspective than hope that this predicate is used efficiently by MySQL's optimiser. Which obviously isn't the case, given the blog post you've linked to.

What happens if you query the JDBC DatabaseMetaData.getPrimaryKeys() for just that one schema? It runs a query against a different information_schema table:

String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, "

+ "COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, 'PRIMARY' AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS " + "WHERE TABLE_SCHEMA LIKE ? AND " + "INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX";

How does the above query perform on your machine?

Nonetheless, the KEY_COLUMN_USAGE table will still need to be queried to discover foreign key constraints. 

A workaround would be to disable code generation for all key information - although that would prevent you from using the ActiveRecord features:

  <!-- Primary key / foreign key relations should be generated and used.
       This is a prerequisite for various advanced features.
       Defaults to true -->
  <relations>true</relations>

From http://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/

Best Regards,

Lukas

[Lukas Eder]

By the way, what version of MySQL is this?

[Lukas Eder]

.... never mind, you said so in the first E-Mail.

[Oliver Schneider]

The query you posted runs fast, a few seconds.
I already tried setting relations to false, but the query against the key constraints is still being executed.

So I think this is bad luck ;(.

Thx for your help though
and thx for jooq!

Oliver

[Lukas Eder]

Hello Oliver,

2014-08-15 9:01 GMT+02:00 Oliver Schneider <ol...@spreadshirt.net>:

The query you posted runs fast, a few seconds.

OK, so that's already a great start. I guess we can replace the queries for primary keys and unique keys by queries against INFORMATION_SCHEMA.STATISTICS - or even better, just use the JDBC Driver's DatabaseMetaData in this case, which is probably already optimal. I've registered #3560 for this.

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

 

I already tried setting relations to false, but the query against the key constraints is still being executed.

Hmm, you're right. I thought that we prevented that from happening, but this flag is currently only a code generator flag, not a jOOQ-meta flag. We should fix that:

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

 

So I think this is bad luck ;(.

Yes. After googling a bit, I found that many people perceive similar issues when queries against the information_schema are executed. E.g.

http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/

 

Thx for your help though
and thx for jooq!

Well, let's hope that your jOOQ experience otherwise is still fruitful! - And thanks again for reporting these issues.

Best Regards,

Lukas

[Lukas Eder]

The query you posted runs fast, a few seconds.

OK, so that's already a great start. I guess we can replace the queries for primary keys and unique keys by queries against INFORMATION_SCHEMA.STATISTICS - or even better, just use the JDBC Driver's DatabaseMetaData in this case, which is probably already optimal. I've registered #3560 for this.

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

This issue is now fixed on GitHub master for jOOQ 3.5.0 and will be backported to 3.4.3, 3.3.4, 3.2.7

[Oliver Schneider]

Awesome Lukas! Thank you very much.

I will clone it and report back when I know if it works now.

Greetings from Berlin,
Oliver

[Lukas Eder]

Hi Oliver,

Thank you very much for further reports. I suspect there might be 1-2 other queries that could be tuned. Would be interesting to learn about those cases.

Best Regards,

Lukas

--

[Oliver Schneider]

Sorry. totally forgot once it worked and I could happily code away :).

Using the 3.5.0-SNAPSHOT with  <relations>false</relations> did the trick.
I like how jOOQ also enables table name/field autocompletion through IDE lookup and type safety of course.
Since we use enums in Mysql, those are cool too.
Thank you very much for your support and for jOOQ.

Have a nice weekend,
Oliver

P.S.:
If I shall try some stuff out for you to check some more queries with Mysql, please say so,
Id gladly help but would need more info.

[Lukas Eder]

Hi Oliver,

Thanks for the feedback and for the nice words. Great to see that you're getting a lot of value out of jOOQ!

Thanks for your offer regarding other queries. So, apparently, all queries seem to perform reasonably well, now that you've turned off <relations/>. That only leaves the foreign key discovery query untested from your side. It currently looks like this:

select 

    `information_schema`.`REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_SCHEMA`,

    `information_schema`.`REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_NAME`,

    `information_schema`.`REFERENTIAL_CONSTRAINTS`.`TABLE_NAME`,

    `information_schema`.`REFERENTIAL_CONSTRAINTS`.`REFERENCED_TABLE_NAME`,

    `information_schema`.`REFERENTIAL_CONSTRAINTS`.`UNIQUE_CONSTRAINT_NAME`,

    `information_schema`.`REFERENTIAL_CONSTRAINTS`.`UNIQUE_CONSTRAINT_SCHEMA`,

    `information_schema`.`KEY_COLUMN_USAGE`.`COLUMN_NAME`

from

    `information_schema`.`REFERENTIAL_CONSTRAINTS`

        join

    `information_schema`.`KEY_COLUMN_USAGE` 

        on  `information_schema`.`REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_SCHEMA` = `information_schema`.`KEY_COLUMN_USAGE`.`CONSTRAINT_SCHEMA`

        and `information_schema`.`REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_NAME` = `information_schema`.`KEY_COLUMN_USAGE`.`CONSTRAINT_NAME`

where

    `information_schema`.`REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_SCHEMA` in ('test')

order by 

    `information_schema`.`KEY_COLUMN_USAGE`.`CONSTRAINT_SCHEMA` asc , 

    `information_schema`.`KEY_COLUMN_USAGE`.`CONSTRAINT_NAME` asc , 

    `information_schema`.`KEY_COLUMN_USAGE`.`ORDINAL_POSITION` asc

Would be great to see how it performs. Replace the above schema name by your schema.

Best Regards,

Lukas

[Oliver Schneider]

Hi Lukas,

the query doesn't work. The table REFERENTIAL_CONSTRAINTS is just too big.
I also checked and there is no index on it; not sure if this is our fault or mysql default or something.
Even a SELECT * FROM `information_schema`.`REFERENTIAL_CONSTRAINTS` LIMIT 1, 1 won't finish in due time (minutes).

Could there be a difference if you use the  JDBC database metadata or does this just lead to the same query?

Greetings from Berlin and a nice weekend,
Oliver

[Lukas Eder]

Hi Oliver,

Thanks for the feedback. Both getImportedKeys and getExportedKeys run queries against KEY_COLUMN_USAGE and TABLE_CONSTRAINTS. Earlier in this discussion, we've already seen that KEY_COLUMN_USAGE is rather slow, so I'm not sure if that would improve things. You could try running DatabaseMetaData.getImportedKeys() or getExportedKeys() at your side, if you want.

But the important thing is to be able to generate primary / unique key information, I guess. So maybe, we should add a code generator configuration to turn off generation only for referential constraints...

I have registered a feature request for jOOQ 3.5 for this:

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

Thanks again for your help!

Lukas