Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add missing configurable variables for sql server timezone to support applications using mysql Connector v8.0.22 #595

Open
sughics opened this issue Jul 12, 2022 · 1 comment
Assignees

Comments

@sughics
Copy link
Contributor

sughics commented Jul 12, 2022

According to Mysql 8.0.23 update, the following bug exists in 8.0.22:

When the server's session time zone setting was not understandable by Connector/J (for example, it was set to CEST), a connection could not be established with the server unless Connector/J specified the correct IANA time zone name in the serverTimezone connection property. This happened even if there was actually no need to use any date-time functionality in Connector/J. The issue was fixed by the new connection properties for Connector/J that control date-time handling—see Preserving Time Instants for details. The following now happens with respect to the above-mentioned situation:

If the new connection property connectionTimeZone is set to LOCAL or a specified time zone, the time_zone variable on the server is no longer checked

If connectionTimeZone=SERVER, the check for the time_zone variable is delayed until date-time driver functionality is first invoked, so that an unrecognizable server time zone does not prevent connection to be established. However, when date-time functionality is invoked and the value of time_zone cannot be recognized by Connector/J, an exception is thrown.

Since v 8.0.22 of mysql connector is being used to support liquibase as mentioned here in a few applications, it requires correct IANA time zone name to be explicitly set in the application's database connection URL under the serverTimezone connection property. This property and value is not currently configured for our applications using v 8.0.22 i.e. collectry and logger-service

The following is an example of a expected url:
jdbc:mysql://localhost:3306/collectory?autoReconnect=true&connectTimeout=0&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Australia/Sydney

Update dataSource.url values in the relevant ansible role templates to include the serverTimeZone for applications that are currently configured to use mysql:mysql-connector-java:8.0.22 . Not setting serverTimeZone value for above-mentioned applications causes an error during initial database connection at runtime, preventing the application from starting successfully.

@sughics sughics changed the title Add missing configurable variable for sql server timezone to support on applications using mysql Connector v8.0.22 Add missing configurable variable for sql server timezone to support applications using mysql Connector v8.0.22 Jul 12, 2022
@sughics sughics changed the title Add missing configurable variable for sql server timezone to support applications using mysql Connector v8.0.22 Add missing configurable variables for sql server timezone to support applications using mysql Connector v8.0.22 Jul 12, 2022
sughics pushed a commit that referenced this issue Jul 12, 2022
…mplates to support mysql connector 8.0 .22
@sughics sughics self-assigned this Jul 12, 2022
@vjrj
Copy link
Collaborator

vjrj commented Jul 12, 2022

Good job, @sughics.

I didn't suffer this issue, but I know that in CAS5 the tz is configured. See https://github.com/AtlasOfLivingAustralia/ala-cas-5/blob/master/README.md#configuration

vjrj pushed a commit that referenced this issue Jul 12, 2022
…mplates to support mysql connector 8.0 .22 (#596)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants