Differences between mysql and mysql5_7 dialect?

[Daniel Haughton]

I have had to upgrade Jooq from 3.11 to 3.13, and the mysql5_7 dialect is not available in the free version. What are the differences between the dialects? All the current queries in the application work fine using the mysql dialect on your mysql5_7 DB. Is there anything I should be concerned about with the different dialects?

[Lukas Eder]

Hi Daniel,

Thank you very much for your message. You can find a recent answer to this question here:

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

Please let me know if you have any additional questions regarding dialect versions or licensing, and I'll be very happy to assist you.

Best Regards,

Lukas 

On Tue, Aug 18, 2020 at 10:23 AM Daniel Haughton <danielha...@gmail.com> wrote:

I have had to upgrade Jooq from 3.11 to 3.13, and the mysql5_7 dialect is not available in the free version. What are the differences between the dialects? All the current queries in the application work fine using the mysql dialect on your mysql5_7 DB. Is there anything I should be concerned about with the different dialects?

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/fec05700-399d-4b1d-90ff-d59031b7d6ddn%40googlegroups.com.

[Daniel Haughton]

Thanks Lucas. To be clear, I am going to be using the mysql dialect on a mysql5_7 DB

So it mentions that features that are introduced later my current version (5_7 )obviously wont work on my 5_7 DB.
You mention how "Rarely, PostgreSQL decides to make a backwards incompatible change" Has there been any such changes in mysql after 5_7?

[Lukas Eder]

Hi Daniel,

Thanks for your message. From the top of my head, I'm not aware of any backwards incompatible changes in MySQL - although some of them may have slipped by us because MySQL has *a lot* of settings that influence how SQL is parsed (such as e.g. lower_case_table_names), so I'm not 100% certain.

Looking at the code base of the commercial distribution, I can see that there are some newer syntaxes that are being generated e.g. for:

- Derived column lists (when renaming tables and columns in one go, e.g. SELECT a, b FROM t AS t (a, b) (check https://www.jooq.org/translate/ to see the difference). This can impact a few emulations as derived column lists are often used in jOOQ's internals

- SELECT .. FOR SHARE is generated differently

- VALUES clause support has changed

More improvements are certainly coming, as MySQL is adding better syntax support. For example, we'd like to stop rendering FROM DUAL in MySQL, which our integration tests indicate is never needed in MySQL 8, but occasionally is in MySQL 5.7: https://github.com/jOOQ/jOOQ/issues/7421

As mentioned in that linked issue, we do recommend using the jOOQ Professional Edition with older RDBMS versions as we will continue to guarantee and integration test jOOQ on that database version. There may be more subtle changes in the future where we want to be able to profit from improved syntax and features in the generic SQLDialect.MYSQL, continuing to render the old syntax only in versioned dialects of the jOOQ Professional Edition.

I hope this helps,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/5287ed48-032e-4a0e-9883-f7c2413d9aean%40googlegroups.com.

[Lukas Eder]

For the record, we have now added a dialect support matrix documenting what RDBMS version is supported by which jOOQ version:

https://www.jooq.org/download/support-matrix

This matrix doesn't list individual features that jOOQ is using / requiring, as that list of features wouldn't be easy to keep up to date, as opposed to the actual version support.