Generator-jhipster: MySQL - Entity with LocalDate: one-day bug

[pascalgrimaud]

Overview of the issue

Following this ticket: https://github.com/jhipster/generator-jhipster/issues/11734

In a MySQL application, when you save an entity with LocalDate, the result is not correct:

• save with 2020/05/08
• result: 2020/05/07

Motivation for or Use Case

The result should be correct.

Reproduce the error

I pushed this project to reproduce easily: https://github.com/pascalgrimaud/jhipster-burger-11740

git clone https://github.com/pascalgrimaud/jhipster-burger-11740
cd jhipster-burger-11740
npm ci
docker-compose -f src/main/docker/mysql.yml up -d
./mvnw

Then, go to Beer entity Create a new entity Check the drinkDate ##### **Related issues** https://github.com/jhipster/generator-jhipster/issues/11734 ##### **Suggest a Fix** Not sure at all about this solution :

diff --git a/src/main/java/io/github/pascalgrimaud/BurgerApp.java b/src/main/java/io/github/pascalgrimaud/BurgerApp.java
index 6236c6d..3a0b37c 100644
--- a/src/main/java/io/github/pascalgrimaud/BurgerApp.java
+++ b/src/main/java/io/github/pascalgrimaud/BurgerApp.java
@@ -5,6 +5,7 @@ import io.github.pascalgrimaud.config.ApplicationProperties;
 import io.github.jhipster.config.DefaultProfileUtil;
 import io.github.jhipster.config.JHipsterConstants;

+import java.util.TimeZone;
 import org.apache.commons.lang3.StringUtils;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
@@ -41,6 +42,8 @@ public class BurgerApp {
      */
     @PostConstruct
     public void initApplication() {
+        TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
+
         Collection<String> activeProfiles = Arrays.asList(env.getActiveProfiles());
         if (activeProfiles.contains(JHipsterConstants.SPRING_PROFILE_DEVELOPMENT) && activeProfiles.contains(JHipsterConstants.SPRING_PROFILE_PRODUCTION)) {
             log.error("You have misconfigured your application! It should not run " +

##### **JHipster Version(s)** 6.8.0 and maybe less ##### **JHipster Version(s)**

[email protected] /home/pgrimaud/tmp/11-burger
└── (empty)

##### **JHipster configuration, a `.yo-rc.json` file generated in the root folder**

.yo-rc.json file

{
  "generator-jhipster": {
    "promptValues": {
      "packageName": "io.github.pascalgrimaud",
      "nativeLanguage": "en"
    },
    "jhipsterVersion": "6.8.0",
    "applicationType": "monolith",
    "baseName": "burger",
    "packageName": "io.github.pascalgrimaud",
    "packageFolder": "io/github/pascalgrimaud",
    "serverPort": "8080",
    "authenticationType": "jwt",
    "cacheProvider": "ehcache",
    "enableHibernateCache": true,
    "websocket": false,
    "databaseType": "sql",
    "devDatabaseType": "mysql",
    "prodDatabaseType": "mysql",
    "searchEngine": false,
    "messageBroker": false,
    "serviceDiscoveryType": false,
    "buildTool": "maven",
    "enableSwaggerCodegen": false,
    "jwtSecretKey": "YourJWTSecretKeyWasReplacedByThisMeaninglessTextByTheJHipsterInfoCommandForObviousSecurityReasons",
    "embeddableLaunchScript": false,
    "useSass": true,
    "clientPackageManager": "npm",
    "clientFramework": "angularX",
    "clientTheme": "none",
    "clientThemeVariant": "",
    "creationTimestamp": 1588964185564,
    "testFrameworks": ["protractor"],
    "jhiPrefix": "jhi",
    "entitySuffix": "",
    "dtoSuffix": "DTO",
    "otherModules": [],
    "enableTranslation": true,
    "nativeLanguage": "en",
    "languages": ["en"],
    "blueprints": [],
    "prettierJava": true
  }
}

JDL for the Entity configuration(s) entityName.json files generated in the .jhipster directory

JDL entity definitions

entity Beer {
  name String,
  drinkDate LocalDate
}
dto Beer with mapstruct
service Beer with serviceClass

Environment and Tools

openjdk version "11.0.7" 2020-04-14
OpenJDK Runtime Environment (build 11.0.7+10-post-Ubuntu-2ubuntu218.04)
OpenJDK 64-Bit Server VM (build 11.0.7+10-post-Ubuntu-2ubuntu218.04, mixed mode, sharing)

git version 2.26.2

node: v12.16.1

npm: 6.14.2

yeoman: 3.1.1

yarn: 1.22.4

Docker version 19.03.8, build afacb8b7f0

docker-compose version 1.21.2, build a133471

identical .jhipster/Beer.json
INFO! Congratulations, JHipster execution is complete!

[pascalgrimaud]

Related link:

• https://stackoverflow.com/a/61678763
• https://bugs.mysql.com/bug.php?id=99487

The upgrade of mysql-connector to 8.0.20 is not a solution for now as it introduced another bug.

[ecostanzi]

From the documentation:

serverTimezone
Override detection/mapping of time zone. Used when time zone from server doesn't map to Java time zone

We are saying to mysql that our server is in UTC when in many case it's not. On my laptop for example the JVM timezone is Europe/Rome.

So, @pascalgrimaud I think that your solution works. We are aligning the JVM timezone to the jdcb connection string.

Solution 2: align the jdbc connection string to the JVM timezone. This can be done by changing the jdbc connecton string like we did with testcontainers properties file: serverTimezone=${user.timezone}.

I personally prefer your solution, but I'm wondering what is the impact for users who will upgrade to this new version.

These are the tests I did:

With UTC timezone : jdbc:mysql://localhost:3306/burger?...useLegacyDatetimeCode=false&**serverTimezone=UTC**...

curl -X POST http://localhost:8080/api/beers -s -d '{"drinkDate": "2020-05-08"}' -H "Content-Type: application/json"
{
  "id" : 19,
  "name" : null,
  "drinkDate" : "2020-05-08"
}

curl -s http://localhost:8080/api/beers/19
{
  "id" : 19,
  "name" : null,
  "drinkDate" : "2020-05-07" --> wrong
}

With my timezone: jdbc:mysql://localhost:3306/burger?...useLegacyDatetimeCode=false&**serverTimezone=Europe/Rome**...

curl -X POST http://localhost:8080/api/beers -s -d '{"drinkDate": "2020-05-08"}' -H "Content-Type: application/json"
{
  "id" : 20,
  "name" : null,
  "drinkDate" : "2020-05-08"
}

curl -s http://localhost:8080/api/beers/20
{
  "id" : 20,
  "name" : null,
  "drinkDate" : "2020-05-08" --> correct!
}

With ${user.timezone}:
jdbc:mysql://localhost:3306/burger?...useLegacyDatetimeCode=false&**serverTimezone=${user.timezone}**...

curl -X POST http://localhost:8080/api/beers -s -d '{"drinkDate": "2020-05-08"}' -H "Content-Type: application/json"
{
  "id" : 21,
  "name" : null,
  "drinkDate" : "2020-05-08"
}

curl -s http://localhost:8080/api/beers/21
{
  "id" : 21,
  "name" : null,
  "drinkDate" : "2020-05-08" --> correct!
}

[pascalgrimaud]

Thanks for helping here @ecostanzi
I think it's because the default timezone of the JVM is user.timezone
And as you said, maybe the best solution is to align JVM + jdbc url :

• use UTC
  
  
  
  • JVM to UTC timezone (in PostConstruct)
  
  
  • all jdbc url with UTC (needs change for testcontainers only, as dev and prod already use UTC)
  
  
• use user timezone
  
  
  
  • let JVM like this (by default user timezone)
  
  
  • change all jdbc url to use user.timezone (needs change for dev, prod)
  
  

The only problem is exactly like you said: I'm wondering what is the impact for users who will upgrade to this new version.

[ctamisier]

I think there will be a problem if there are several instances of the application around the world. One in Europe and one in Australia. Depending where the entity is saved or re-saved, the date might be affected.

About 8.0.20 version the bug seems fix in ServerPreparedQueryBindValue line 287 by getting a calendar in the current server timezone which won't wrongly affect the date in the next code lines.

With this version it seems that cacheDefaultTimezone is null even if we provide it in the url as &cacheDefaultTimezone=false.

[pascalgrimaud]

I think there will be a problem if there are several instances of the application around the world. One in Europe and one in Australia. Depending where the entity is saved or re-saved, the date might be affected.

@ctamisier : in this case, all the config should be set to UTC, right ?

[ctamisier]

Yes exact. It is also what I've understand about the initial motivation to set to UTC in the connector configuration which seems correct to me and a good way to have unified dates (specially if we use Mysql DATETIME). (cc @avdev4j)

The problem might be here (about 8.0.20): https://github.com/mysql/mysql-connector-j/commit/c1e9c7d1f3ff5b31ca6a2744707ae4dd7840cf3c#r39054941

[ecostanzi]

Let's consider this. If we change the jvm timezone in the postconstruct users that upgrade might not even notice the change and deploy a new version of the app. Besides, if someone has set -Duser.timezone=not-utcin the Java opts, we would override that setting. I'm afraid that in this case many will be affected by the change.

On the other hand, if we change the properties files we would give them the chance to fix the production properties. Hopefully most of the users have externalized the production configuration.

[ctamisier]

Indeed we shouldn't do this:
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

For now we can imagine to use &serverTimezone=${user.timezone} but it is a bit against all the analysis here: https://github.com/jhipster/generator-jhipster/issues/8284 ? (saying UTC everywhere)

For me the solution is a fix on the connector post 8.0.20 (as I think the &cacheDefaultTimezone=false is the solution) but I don't know much how it will go..

[pascalgrimaud]

Yes I believe we just need to wait a new version of mysql connector

[skmaingi]

@pascalgrimaud I removed &serverTimezone=UTC from jdbc url and inserted dates for different Timezone and all were correct on reading them

[skmaingi]

@pascalgrimaud removing &serverTimezone=UTC and setting JVM Timezone will not work for existing MySQL databases that saved LocalDate entries in UTC.

Date conversion maybe required for those who were using previous version.

My opinion, setting timezone in JDBC url or JVM will complicate things in future.

Its preferable to either:

1. Use local timezone or
2. Allow user to set timezone in configuration files

[ctamisier]

A ticket on MySQL Connector/J is opened here: https://bugs.mysql.com/bug.php?id=99713 (about the exception on storing date with the connector 8.0.20)
It is "verfied".
So I think if we have a version 8.0.21 it should work as 8.0.20 should fix the "one day bug" in a proper way.

[DanielFran]

@pascalgrimaud @ctamisier version 8.0.21 is now available: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html

I will create a PR today to update the version in BOM project, so we can close this PR.

[pascalgrimaud]

Thanks a lot.
So the best fix was to wait the new version :)

[ctamisier]

Can you see a fix about this issue ?
eventually here: https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-21.html#connector-j-8-0-21-bug

[DanielFran]

@ctamisier I just checked that mysql/mysql-connector-j@c1e9c7d#r39054941 was on 8.0.21

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html

[DanielFran]

@ctamisier I review again the commits for that version and in fact there is no commit to fix the current issue in 8.0.20, I misread with the commit you put bellow...

[ctamisier]

It should be OK for the next release:
https://github.com/mysql/mysql-connector-j/pull/53

[mraible]

@ctamisier Can you create a PR to use the next release once it happens?

[ctamisier]

Sure ! but @DanielFran might go faster than me :)

[pascalgrimaud]

it looks like the 8.0.22 is available : https://search.maven.org/artifact/mysql/mysql-connector-java/8.0.22/jar

[DanielFran]

Sorry, but I had a cirurgy yesterday so I will be out few weeks...

[mshima]

@DanielFran best wishes.

[pascalgrimaud]

@DanielFran : no worry about JHipster, take care of yourself, it's more important my friend !

[pascalgrimaud]

@ctamisier : I just tested with 8.0.22 and it is finally fixed !!!
As you are the one who did the most of work here (analyzed the bug, opened ticket, followed release, etc), can you propose a pull request at https://github.com/jhipster/jhipster to upgrade this plz ? Then, the bounty will be for you !

<mysql-jdbc.version>8.0.19</mysql-jdbc.version>

[ctamisier]

take care @DanielFran !
Yes I'll submit a PR. Thx.

[pascalgrimaud]

@ctamisier : approved your bounty https://opencollective.com/generator-jhipster/expenses/26862