MySQL Bugs: #91112: Again wrong java.sql.Date

Bug #91112	Again wrong java.sql.Date
Submitted:	2 Jun 2018 14:52	Modified:	21 Feb 2020 20:45
Reporter:	Pavel Dvorkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.11	OS:	Windows
Assigned to:		CPU Architecture:	x86
Tags:	regression

Description:
The problem described at https://bugs.mysql.com/bug.php?id=71084 appeared again for 8.0.11 Connector/J

I insert a Date. Server table contains correct data, but when I try to get them, I get a Date 1 day before.

The problem exists for dates which are later than 1982-4-1. For dates <= 1982-4-1 all OK

For 5.1.45 - no problem.

How to repeat:
		String url = "jdbc:mysql://localhost/test";
		String userName = "test";
		String userPassword = "test";

		Connection conn = DriverManager.getConnection(url, userName, userPassword);

		Statement st = conn.createStatement();
		st.execute("drop table if exists test");
		st.execute("create table test ( x date )");

		PreparedStatement pst = conn.prepareStatement("insert into test values (?)");
		java.sql.Date date = java.sql.Date.valueOf("1982-4-1");
		System.out.println("Storing java.sql.Date "+date);
		pst.setDate(1, date);
		pst.execute();

		ResultSet rs = st.executeQuery("select x from test");
		rs.next();
		System.out.println("Got back java.sql.Date: " + rs.getDate(1));

Please change date from 1982-4-1 to 1982-4-2 to see the bug

Hello Pavel,

Thank you for the report.

Thanks,
Umesh

Hi,
Is there a plan to fix this bug ?

Any ideas? Quite a critical problem, makes the version 8 unusable.

This definitely is not a "non-critical" issue.

Hopefully this gets fixed asap

This is still a problem in Connector/J 8.0.13. We were forced back onto 5.1.47 because of this issue. And yes, I would also call that not non-critical, as there is no known workaround ...

try to add option serverTimezone=*** to connection string.

refer https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-known-issues-limitatio...

We already do that, but that doesn't change the fact, that Connector/J converts DATE from the local timezone (e.g. Europe/Berlin) to the server timezone (e.g. UTC). Which is utter nonesense as dates don't have a time part ... We could of course rewrite our code to use localDate.atStartOfDay(ZoneOffset.UTC) to convince Connector/J not to change the Zone :-/ It works fine on Version 5 with the options 'serverTimezone=UTC&useLegacyDatetimeCode=false&noTimezoneConversionForDateType=true'.

Actually, this is not a bug at all.

Unfortunately this falls into the cracks of the JDBC specification and the JDK documentation. Somewhere between the definition of java.sql.Date and ResultSet/PreparedStatement getDate()/setDate() methods.

While java.sql.Date says that a Date instance doesn't carry time information, on the other hand, both ResultSet and PreparedStatment methods mention something about time zone adjustments, using a given calendar or the system default one if none is provided, when the database doesn't store time zone information, which is the case of MySQL server.

This leads to an open interpretation in regard to if and when time zone adjustments should or shouldn't be made. So, in order to handle this we've added the connection properties `noTimezoneConversionForDateType` and `noTimezoneConversionForDateType` so that users could choose what default behavior they prefer in Connector/J 5.1. But they where removed from Connector/J 8.0 so that it becomes more JDBC compliant. You may disagree with our assessment, though, and we'd like to hear about it.

This decision was made mostly because there are some options that avoid the time zone adjustments altogether. Namely:

1. Use the getDate()/setDate() methods that take a calendar and provide one that matches the client time zone. This way all date values read/stored will be interpreted as if they were generated in this client. Note that by using the methods that don't take a calendar instance will result in temporal data being translated to the server time zone when saved and then translated back from the server time zone when being read, and this ok when what you need is the real instant, world wide, that may look different when read from different clients running on different places in the globe.

2. Set the connection option `serverTimezone=<client_time_zone>`, replacing the placeholder with the client time zone, obviously. By doing this, you are basically telling the driver that the server is running on the same time zone as the client, so, any time zone adjustments will end up being made between two of the same time zones and, therefore, no changes are applied.

3. Actually set the server time zone equals to the client time zone (server global variable `time_zone`). This may work since the driver finds out what the server time zone is when initializing the connection but there are some caveats. First, the server and client time zones must match exactly (not just two time zones with same TZ offsets) otherwise some mistakes may occur in the periods where DST switches occur, and, second, all clients connecting this server must run under the same time zone (equal to the server time zone), otherwise unexpected time zone adjustments happen, also meaning that no two clients can run on machines with different default system time zones and supposedly all clients must be regionally located very close together in order to minimize risks.

So, although these things may not look like the nicer way to handle this issue, there are some ways to work it out.

If it comes to it we may consider reverting the removal of `noTimezoneConversionForDateType` and `noTimezoneConversionForDateType` (or add something similar) in Connector/J 8.0 in order to help our users, but we'd like to hear your opinion too.

Finally, unless you convince us that we are actually doing something wrong, this should be changed to a feature request.

Thanks,

Bug#93059 is a duplicate of this one.

Bug#92776 may be a duplicate of this one.

Hi Filipe Silva ,

I understand your explanation but a question is : if so, why this behaviour doesn't exist in 5.1.47 and exists in 8.x ? java.sql.Date and PreparedStatement are the same. Is it possible to transfer 5.1.47 code to 8.x  and close this problem ?
I think it is better to do it if it is possible instead of manipulations with server timezone etc.

Regards, Pavel

Hi Pavel,

Right, this is why I haven't rejected the bug report. There is something we have to do but we definitely don't want to transfer the code from version 5.1 to 8.0. Connector/J 5.1 is full of switches that expose legacy behaviors and often they conflict with each other. This code was growing unmanageable and we don't want to do that again in Connector/J 8.0.

However, since this is one of the areas where the specification doesn't clarify enough how to handle it, mostly because MySQL also doesn't support time zones in datetime data, we are planning to come up with something that may be more usable and maintainable in future versions. Hence the suggestion to file this as "feature request".

In the meantime there are a few workarounds at your disposal. I agree they are not the nicest way of dealing with all this, but at least users are able to control the behaviors they want/need.

I hope you can bear with this until no better alternative is there.

Bug#94538 is a duplicate of this one.

We ran into this issue as well, and after wasting quite some time ended up reverting to 5.1.47. This is a serious bug for us, it stops us from using the 8.x version completely.

I also disagree with the assessment that this is a spec questions. We did found a workaround at the JDBC level, while this fails:

   ps.setDate(2, Date.valueOf(LocalDate.of(2011, 11, 11)))

the three parameter version succeeds:

   ps.setDate(2, Date.valueOf(LocalDate.of(2011, 11, 11)), Calendar.getInstance())

Both the two-parameter setDate method and Calendar.getInstance() specify the use of the VM's time zone, though.

For those using JDBC directly above might be a good workaround. Unfortunately we are not, and we are not really intending to get the third party DAL components adjusted to cater for this bug, hence the rollback to 5.1.47.

BTW: the amount of TODOs in com.mysql.cj.ClientPreparedQueryBindings is concerning us as well. We still run our core databases on MySQL and this is not filling us with confidence. </wining>

Turns out 5.1.47 makes a number of other tests fails since timestamps come back with a 10h shift (we are in UTC+10). I'm trying to find some combination of driver options that may actually work, but so far it's not looking good.

Right now I am at a point of contemplating a wrapper driver that redirects the setDate 2-param version to the 3-param one or running a patched version of your driver.

How you could consider this to be acceptable I do not understand, for us this bug is quite severe. If we wouldn't have the test coverage we have, this would cause data corruption, which I would consider worst case scenario as far as bugs go.

Bug#98436 is marked as a duplicate of this one.

Hi Filipe Silva!

Actually, you are not right that this is not a bug. This is definitely is and I can explain why.

I already explained this in Bug#98436, but the essential part is:

Javadoc of java.sql.PreparedStatement.setDate() method says:

Sets the designated parameter to the given java.sql.Date value using the **default time zone of the virtual machine** that is running the application.

But, the implementation currently uses timezone of the MySQL Server instead of timezone of Java VM.

So, the implementation uses MySQL server timezone, while it MUST use JVM timezone. I checked some other JDBC drivers (MariaDB, PostgreSQL, Oracle) and all them respect the spec and use JVM timezone here.

And what is worse: if you look at the code you will see that current implementation uses two different timezones for java.sql.Date instantiation and for SimpleDateFormat instantiation. And this leads to this situation:

1. You have java.time.LocalDate instance. This instance doesn't have a timezone information at all. So, it is ideal for mapping to MySQL `DATE` column.

2. You call preparedStatement.setObject(1, myLocalDate)

3. It delegates to AbstractQueryBindings.setObject(int parameterIndex, Object parameterObj)

4. It calls setDate(parameterIndex, Date.valueOf((LocalDate) parameterObj));
**IMPORTANT:** Here java.sql.Date is created without any timezone. It contains exact fields of local date.

5. When Calendar is null (this is our case), setDate creates SimpleDateFormat like this:
this.ddf = TimeUtil.getSimpleDateFormat(this.ddf, "''yyyy-MM-dd''", cal, cal != null ? null : this.session.getServerSession().getDefaultTimeZone());

And then uses it like this:
setValue(parameterIndex, this.ddf.format(x), MysqlType.DATE);

**IMPORTANT:** Here MySQL server timezone is used for formatter, despite the fact that java.sql.Date contains local date without timezone. This should be irrelevant. Our java.sql.Date is not contain any timezone anyway, and formatter will format only DDDD-MM-YY anyway. So, what the problem?

But SimpleDateFormat works in a very confusing way. It actually can't format java.sql.Date, it only formats java.util.Date. And because java.sql.Date is a child of java.util.Date, it implicitly also can format java.sql.Date, but without understanding it's semantic (the fact, that unlike java.util.Date, java.sql.Date doesn't contain a timezone). So, SimpleDateFormat just do:
calendar.setTime(date);

which in turn does exactly that:
setTimeInMillis(date.getTime());

But remember: our calendar instance here have session.getServerSession().getDefaultTimeZone(). 

This leads to the observable behavior - SimpleDateFormat just creates a string, when DATE is 1 day before.

In the end, this manipulations leads to the fact that even LocalDate, that doesn't contain any timezone at all, still shifted when stored into Mysql `DATE` column, which also doesn't contain a timezone at all.

Bug#98605 is marked as a duplicate of this one.

Posted by developer:
 
Added the following entry to the C/J 8.0.20 changelog: 

       " When a Calendar was not used, a java.sql.Date value could
       not always be stored into and then retrieved from a MySQL
       server consistently. It was because Connector/J always
       converted a Date value to the server's time zone when
       storing it on the server as a MySQL DATE; but since a
       MySQL DATE does not have any time value, the hour,
       minute, and second parts of the original date was
       effectively lost. If the converted value is one day ahead
       of or behind the original value, when the value was
       retrieved through Connector/J and converted back to the
       local time zone, there was no time value for adjusting
       the date back to its original value, resulting in a
       one-day error. With this fix, any Date value is converted
       to MySQL DATE value using the JVM's time zone, so that
       the value is always consistent when being stored and then
       read back.
       Also, the cacheDefaultTimezone connection property,
       previously removed from Connector/J 8.0, has now been
       restored so that when it is set to false, Connector/J
       becomes aware of the time zone changes of the JVM during
       runtime and converts dates with the updated time zone."