Upgrade mysql-connector to at least v8.0.23 for platform 2.4.0

Development
1

The current version of MySQL-connector (v8.0.21) doesn’t provide support to set SERVER TIMEZONE via the connection string(it’s a bug). Due to this fact, the server timezone defaults to UTC. This is problematic!! Causes errors building search Index for some patients ~ affects patient search

To fix the issue. IMO I’ve got two options here:

  • Upgrade mysql-connector to at least v8.0.23
  • Revert mysql-connector to use 5.x (the one used by previous platform version 2.3.x)

This commit FORCED TO SET SERVER TIMEZONE IN CONNECT STRING. Fixes the above issue. However, with this upgrade comes incompatibility issues and some breaking changes. I tried upgrading mysql-connector to v8.0.25, couldn’t brought up the server. I encoutered errors one after the other, for example; this might implies upgrade liquibase-core to a higher version.

java.lang.ClassCastException: class java.time.LocalDateTime cannot be cast to class java.lang.String (java.time.LocalDateTime and java.lang.String are in module java.base of loader 'bootstrap')
amrs-upgrade           | 	at liquibase.changelog.StandardChangeLogHistoryService.getRanChangeSets(StandardChangeLogHistoryService.java:328) ~[liquibase-core-3.10.2.jar:?]
amrs-upgrade           | 	at liquibase.changelog.AbstractChangeLogHistoryService.upgradeChecksums(AbstractChangeLogHistoryService.java:66) ~[liquibase-core-3.10.2.jar:?]
amrs-upgrade           | 	at liquibase.changelog.StandardChangeLogHistoryService.upgradeChecksums(StandardChangeLogHistoryService.java:297) ~[liquibase-core-3.10.2.jar:?]

Option two is much safer(I chose to do this), this how i fix the issue checkout tag 2.4.0, downgrade mysql-connector to v5.1.47 (latest in 5.1.x), change hibernate connection driver class from com.mysql.cj.jdbc.Driver to com.mysql.jdbc.Driver. Now I can set serverTimezone=EAT in the connection string.

Is there a better way of fixing the issue above?

cc: @dkayiwa @ibacher @aojwang @dev5

2

Thanks @corneliouzbett that’s interesting, we’ve run into the exact same issue right @frederic.deniger @jfigueiredo?

3

Hello @corneliouzbett , as @mksd said, we have a similar problem, we add some parameters to the driver to make sure that all the dates are converted to UTC when we are saving database using ( and converted back when getting the dates) :

OPENMRS_JDBC_EXTRA_PARAMETERS=&serverTimezone\=Etc/UTC&useLegacyDatetimeCode=true&noTimezoneConversionForTimeType=true&useTimezone=true

But then we had 2 problems:

1º The time parameter is converted to UTC when saving but does not convert back when getting the time from DB, we can fix this with noTimezoneConversionForTimeType=true ( so we don’t convert time types)

2º The same as the above happens on the date type, but here we don’t have a solution for this.

Bottom line is, the main problem we have is with MySQL time and date types. When we are working with datetime it works well.

CC @frederic.deniger

4

I would blame MySQL-connector. Have you tried using a different version of MySQL-connector? 8.1.23 or higher version

5

We had to downgrade the platform to 2.3.3 for KenyaEMR upgrade.

6

Unfortunately, you’ll probably run into this issue, which is caused by Liquibase not being aware of the change to MySQL connector (8.0.23 really should’ve been 8.1.0). I’m working on fixing that.

7

Hello @corneliouzbett

Yes we tried the latest MySQL-connector version but the issue was still here and we faced an issue with Liquibase ( see @ibacher message).

From my point of view, we should not try to configure the MySQL driver to manage Timezone but it should be managed by OpenMRS. @jfigueiredo did some PRs for that :slight_smile: