Timestamp issue while upgrading to MySQL 5.7

|
| By Webner

Problem: In our application, we have created the session token to provide secure access. We were using current system time stamp in our token as one of the key component. But after upgrading MySQL 5.5 to MySQL 5.7, we were getting random results. Most of the time our session token failed to decode.

Solution: In Mysql server version up to 5.5 or lower if a DateTime value with decimal precision is assigned to a DateTime field it will ignore the decimal points. But in MYSQL 5.7, it will round off the given value. So before version 5.7, if a timestamp of 10:01:7 passed to the database it will store it as 10.02 but in previous versions, it will simply ignore the values after the decimal point and will take it as 10.01.
So we were getting the mismatch in the date field which were used to generate token and were not able to decode.

There are two solutions to avoid the issue:
1. Round up the Unix time excluding milliseconds before persisting to the database. So 10:01:7 will be stored as 10:02).
2. Drop the milliseconds before persisting to the database. In this case 10:01:7 will be stored as 12:01.

So instead of using:

Timestamp systemTime=new Timestamp(System.currentTimeMillis());

Use below one:

Timestamp systemTime=new Timestamp((System.currentTimeMillis()/1000)*1000);

In the above line of code we are simply ignoring the milliseconds.

Leave a Reply

Your email address will not be published. Required fields are marked *