JOOQ - Drizzle JDBC - MySQL issue

[glen.a....@gmail.com]

Hi, I've been struggling with an issue regarding JOOQ, Drizzle JDBC and MySQL.

Drizzle JDBC is a BSD licensed JDBC driver: http://www.drizzle.org/content/download

While attempting to call context.meta().getTables() I've been getting exceptions like this:
java.lang.ArrayIndexOutOfBoundsException: -1
    at org.jooq.impl.AbstractRecord.getValue0(AbstractRecord.java:271)
    at org.jooq.impl.AbstractRecord.getValue0(AbstractRecord.java:275)
    at org.jooq.impl.AbstractRecord.getValue(AbstractRecord.java:169)
    at org.jooq.impl.Utils.setValue(Utils.java:1121)
    at org.jooq.impl.ResultImpl.intoGroups(ResultImpl.java:915)
    at org.jooq.impl.MetaImpl$MetaSchema.getColumns(MetaImpl.java:262)
    at org.jooq.impl.MetaImpl$MetaSchema.getTables(MetaImpl.java:238)
    at org.jooq.impl.MetaImpl.getTables(MetaImpl.java:151)

After some digging around in the source, and perusing some searches I believe it has to do with the way Drizzle lower cases all column names when it returns results. From what I've read the lower casing of column names is intended in Drizzle ( ref: https://lists.launchpad.net/drizzle-discuss/msg06133.html ).

As for JOOQ. These two lines are hard coded in MetaImpl.java

Field<String> tableSchem = fieldByName(String.class, "TABLE_SCHEM");
Field<String> tableName = fieldByName(String.class, "TABLE_NAME");

and Fields.field(Field<T>) and most Field.equals() implementations do comparisons based on the 'name' of the column, but does so using a case sensitive string equals(). I am unsure if this is a specific design choice or not.

As a temporary solution switching to the MariaDB JDBC connector ( https://downloads.mariadb.org/client-java/+releases/ ) is a viable workaround however I'd prefer to keep using Drizzle if possible.

[Lukas Eder]

Hello,

Thanks for reporting this. 

To understand the broader context: What is your experience running jOOQ with Drizzle in general? What is the reason you chose Drizzle rather than MySQL (or MariaDB or even CUBRID). Do you know who's behind Drizzle? The reason I ask is because I'm always keeping an eye out for new potential database integrations. In the long run, jOOQ works best with those databases that are explicitly integration tested. Other integrations aren't officially supported.

Nonetheless, let's have a look at your issue:

2013/7/30 <glen.a....@gmail.com>

Hi, I've been struggling with an issue regarding JOOQ, Drizzle JDBC and MySQL.

Drizzle JDBC is a BSD licensed JDBC driver: http://www.drizzle.org/content/download

While attempting to call context.meta().getTables() I've been getting exceptions like this:
java.lang.ArrayIndexOutOfBoundsException: -1
    at org.jooq.impl.AbstractRecord.getValue0(AbstractRecord.java:271)
    at org.jooq.impl.AbstractRecord.getValue0(AbstractRecord.java:275)
    at org.jooq.impl.AbstractRecord.getValue(AbstractRecord.java:169)
    at org.jooq.impl.Utils.setValue(Utils.java:1121)
    at org.jooq.impl.ResultImpl.intoGroups(ResultImpl.java:915)
    at org.jooq.impl.MetaImpl$MetaSchema.getColumns(MetaImpl.java:262)
    at org.jooq.impl.MetaImpl$MetaSchema.getTables(MetaImpl.java:238)
    at org.jooq.impl.MetaImpl.getTables(MetaImpl.java:151)

Hmm, yes that error message certainly has room for improvement. It would be better to display the requested field name if access is done by field name. I'll improve that in the next release:

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

 

After some digging around in the source, and perusing some searches I believe it has to do with the way Drizzle lower cases all column names when it returns results. From what I've read the lower casing of column names is intended in Drizzle ( ref: https://lists.launchpad.net/drizzle-discuss/msg06133.html ).

As for JOOQ. These two lines are hard coded in MetaImpl.java

Field<String> tableSchem = fieldByName(String.class, "TABLE_SCHEM");
Field<String> tableName = fieldByName(String.class, "TABLE_NAME");

SQL case-sensitivity is a mine field. In this particular case, the JDBC specs specify upper-case column names, so this clearly is a drizzle bug in my opinion. I've reported it here:

https://bugs.launchpad.net/drizzle/+bug/1206375

 

and Fields.field(Field<T>) and most Field.equals() implementations do comparisons based on the 'name' of the column, but does so using a case sensitive string equals(). I am unsure if this is a specific design choice or not.

Yes it is. Unfortunately, SQL allows for being case-insensitive by default. This was a big mistake in early days when SQL started to be standardised, as many pre-existing databases had case-insensitive identifiers. jOOQ, by default, quotes all identifiers and treats them as being case-sensitive. This can be overriden for SQL rendering, but not for accessing data. I wonder if jOOQ should support a Setting flag to enable case-insensitive field access in Records (and probably equals() methods, too).

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

Cheers

Lukas

[glen.a....@gmail.com]

Wow, thanks for your awesome and detailed response!

What is your experience running jOOQ with Drizzle in general?

Apart from this issue with getting table names, none yet. Note that I'm only using the JDBC connector from Drizzle, I'm using MySQL as the actual server for the moment(though planning to move to MariaDB).
 

What is the reason you chose Drizzle rather than MySQL (or MariaDB or even CUBRID).

I was after a BSD alternative for the Oracle MySQL JDBC connector. I hadn't head of CUBRID but I see it has a BSD connector. That might be a viable alternative(for both the connector and maybe even the server), thanks, I'll check it out.

Do you know who's behind Drizzle?

Apart from what I gather from their website, no.

[Lukas Eder]

2013/7/30 <glen.a....@gmail.com>

Wow, thanks for your awesome and detailed response!

No worries.

What is your experience running jOOQ with Drizzle in general?

Apart from this issue with getting table names, none yet. Note that I'm only using the JDBC connector from Drizzle, I'm using MySQL as the actual server for the moment(though planning to move to MariaDB).

Note that jOOQ 3.1 officially supports MariaDB:

http://www.jooq.org/notes.php?version=3.1

This includes integration testing against an actual MariaDB instance and the official MariaDB JDBC driver.

What is the reason you chose Drizzle rather than MySQL (or MariaDB or even CUBRID).

I was after a BSD alternative for the Oracle MySQL JDBC connector. I hadn't head of CUBRID but I see it has a BSD connector. That might be a viable alternative(for both the connector and maybe even the server), thanks, I'll check it out.

CUBRID isn't a MySQL fork, but aims for replacing MySQL in web contexts. I haven't used it in production, but I'm impressed with its tool chain, given that it is a relatively new database, created by the Korean NHN corporation. But if this is just a licensing issue, then MariaDB is probably your best replacement for MySQL, given that the lead developer was the original MySQL developer.

Do you know who's behind Drizzle?

Apart from what I gather from their website, no.

OK, thanks!

Lukas 

[Lukas Eder]

#2655 is finally fixed in jOOQ 3.3 and will be merged to jOOQ 3.2.2

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

Cheers

Lukas

2013/7/30 Lukas Eder <lukas...@gmail.com>