MySQL Bugs: #74905: GetTimeZoneOffset problematic depending on server Time and TimeZone

Bug #74905	GetTimeZoneOffset problematic depending on server Time and TimeZone
Submitted:	17 Nov 2014 19:50	Modified:	17 Feb 2015 0:36
Reporter:	Nicholas Schell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.x	OS:	Any
Assigned to:	Roberto Ezequiel Garcia Ballesteros	CPU Architecture:	Any
Tags:	connector, datetime, timezone, TimeZoneOffset

Description:
The sql used in GetTimeZoneOffset method in Driver.cs uses curtime and utc_time inside a timediff. This is problematic because it does not take the day into the context of the time difference.

Example: say server is in PST (UTC -8) timezone and the current local time is 9:00PM (21:00), which means UTC time is 5:00AM (05:00). Timediff's result will then be 16:00 because it is attempting to diff (21:00 - 05:00). Which clearly is entirely incorrect. If the test was done at 3:00PM (15:00) local time you would not see the issue because timediff would then process (15:00 - 23:00) = -8:00.

I believe this error has actually been present for awhile, at least since 6.8.3. I've only checked 6.9.4 and 6.9.5 which it is still present in.

How to repeat:
Attempt to pull a DateTime object when your local time will be on a different day than actual UTC time. I.E. late at night if you left of UTC, otherwise early morning if you are right of UTC.

Suggested fix:
Use time functions with full datetime instead of just time.

In MySql.Data.MySqlClient.Driver, method GetTimeZoneOffset

Switch SQL FROM

select timediff( curtime(), utc_time() )

TO

select timediff( NOW(), UTC_TIMESTAMP() )

Hello Nicholas Schell,

Thank you for the bug report.
I tried to reproduce the issue at my end using Visual Studio 2013 (C#.Net), MySQL Connector/Net 6.9.3 but not seeing any issues while checking with different times.
Could you please list out the steps you tried out at your end? Please provide repeatable test case in order for us to confirm this issue at our end.

Thanks,
Chiranjeevi.

I tried to reproduce the issue at my end using with MySQL Connector/Net 6.9.5 but not seeing any issue.

I think it should be sort of obvious how the problem arises just looking at the commands that are being used... you shouldn't really need to reproduce it exactly though I can understand the need to just assure that is what is actually happening. But I think you can follow that if the current system time on the server running mysqld is 9:00PM UTC -8, curtime() is going to return 21:00 and utc_time() is going to return 05:00. Correct? I don't think there is any denying that those things WILL happen if the time_zone is UTC -8. So if you timediff (21:00, 05:00), what is the result? It certainly is not -8, which is the time_zone offset you would be expecting.

The crux of the reproduction would be be sure the timezone of the server is correctly set to UTC -8, and mysqld time_zone is set to SYSTEM, AND it is returning correct times with curtime for UTC -8. Then wait until any time after 4PM UTC-8 (because that will correlate to 12:00AM UTC) and the problem in the diff will arise.

As I am typing this response it is actually 4:21PM UTC -8 right now... so here is a quick example just connecting to mysql and running the same commands the connector is going to run.

mysql> SELECT @@global.time_zone, @@session.time_zone, curtime(), utc_time(), timediff(curtime(), utc_time());
+--------------------+---------------------+-----------+------------+---------------------------------+
| @@global.time_zone | @@session.time_zone | curtime() | utc_time() | timediff(curtime(), utc_time()) |
+--------------------+---------------------+-----------+------------+---------------------------------+
| SYSTEM             | SYSTEM              | 16:23:29  | 00:23:29   | 16:00:00                        |
+--------------------+---------------------+-----------+------------+---------------------------------+
1 row in set (0.00 sec)

What should be happening is using now() and utc_timestamp()

mysql> SELECT @@global.time_zone, @@session.time_zone, now(), utc_timestamp(), timediff(now(), utc_timestamp());
+--------------------+---------------------+---------------------+---------------------+----------------------------------+
| @@global.time_zone | @@session.time_zone | now()               | utc_timestamp()     | timediff(now(), utc_timestamp()) |
+--------------------+---------------------+---------------------+---------------------+----------------------------------+
| SYSTEM             | SYSTEM              | 2014-11-19 16:25:49 | 2014-11-20 00:25:49 | -08:00:00                        |
+--------------------+---------------------+---------------------+---------------------+----------------------------------+
1 row in set (0.00 sec)

I am running mysqld 5.5.40-0ubuntu0.12.04.1, though the version of mysql shouldn't really matter.

And this problem is pretty much related to ANY time_zone that is not '00:00' when the local time's day is going to differ from the utc day. Because on the new day the 24 hour clock is going to reset and the diff is just going to be incorrect. I'm just giving the example in my own time_zone -08:00. The same thing would happen for UTC+8 during 00:00-08:00AM local time. If curtime() returns 01:00 that means utc_time() will be 17:00, and timediff( 01:00, 17:00) is NOT +08:00...which is what we want.

Actually I think I know why you probably don't see the problem at all in the connector, there is actually another MAJOR bug in the 6.9.x series which will cause ALL DateTime objects to return as UTC.

I didn't file it, but responded to it here.
http://bugs.mysql.com/bug.php?id=74134

If you look at your DateTime objects in .NET you will see they are all Kind.UTC no matter what the server time_zone is. Which would mask the issue I am reporting, unless you actually compare the times because they should be completely incorrect when you look at them with their TimeZone.

Hello Nicholas Schell,

Thank you for the feedback.
Verifying based on internal discussion with dev's.

Thanks,
Chiranjeevi

Fixed as of the upcoming Connector/Net 6.7.7 release, and here's the changelog entry:

The "GetTimeZoneOffset" method would return an incorrect value depending
on the server Time and TimeZone, as it did not take into account the day
into the context of the time difference.

This bug is being confirmed for additional Connector/Net versions, namely for the 6.8.x and 6.9.x branches.

Thank you for the bug report.