Bug #88227 Connector/J 5.1.44 cannot be used against MySQL 5.7.20 without warnings
Submitted: 25 Oct 2017 17:22 Modified: 17 Feb 2018 2:50
Reporter: Francisco Lozano Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version: OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[25 Oct 2017 17:22] Francisco Lozano
MySQL 5.7.20 issues warnings when using `tx_isolation` / `tx_read_only` variables, as they're deprecated and removed from 8.0.3 [1].

However, Connector/J 5.1.44 still uses these variables when connecting to 5.7.20, even if in its release notes it acknowledges that these are deprecated and to be removed [2].

My assumption is that the connector checks the MySQL version and uses the old ones in any 5.7.x and the new ones in 8.0.x (transaction_isolation, transaction_read_only). 

Many systems will deal with SQL-level warnings as errors, and raise exceptions when these are found. MySQL 5.7.20 is unusable in any system like that without a fixed Connector/J.

By the way, it's pretty bad to refer from release notes to bug numbers that can't be seen in bugs.mysql.com, please see [3]. It's annoying and shows little respect for open-source users.

[1] https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-20.html
[2] https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-44.html
[3] https://bugs.mysql.com/bug.php?id=26440544

How to repeat:
Just use Connector/J 5.1.44 with MySQL 5.7.20 and try to perform transactions.

Suggested fix:
Connector/J should stop using tx_isolation and tx_read_only for any MySQL 5.7.x where x >=20.
[26 Oct 2017 13:27] Chiranjeevi Battula
Hello Francisco,

Thank you for the bug report.
Verified based on internal discussion with dev's.

[22 Nov 2017 10:36] Francisco Lozano
Given that this is confirmed and that this renders latest MySQL unusable from Java... when do you plan to fix?
[17 Feb 2018 2:50] Daniel So
Posted by developer:
Added the following entry to the Connector/J 5.1.46 changelog:

"When Connector/J 5.1.44 or earlier connected to MySQL 5.7.20 or later, warnings are issued because Connector/J used the deprecated system variables tx_isolation and tx_read_only. These SQL-level warnings, returned from a SHOW WARNINGS statement, might cause some applications to throw errors and stop working. With this fix, the deprecated variables are no longer used with MySQL 5.7.20 and later; also, to avoid similar issues, a SHOW WARNINGS statement is no longer issued for the use of deprecated variables."