Grafana seems to shift timestamps twice if MySQL server_time is set to local time instead of UTC

[xanonid]

It seems that timezone adaptions are done currently twice if the MySQL server time is set to the local timezone for TIMESTAMP columns. If the Unix timestamp is stored in an INT field, everything works correctly, as the MySQL does not perform any timezone convertions.

Possible solution would be to add time_zone=%27+00:00%27 to the connection string in

grafana/pkg/tsdb/mysql/mysql.go

Line 28 in b32d420

cnnstr := fmt.Sprintf("%s:%s@%s(%s)/%s?collation=utf8mb4_unicode_ci&parseTime=true&loc=UTC&allowNativePasswords=true",

What Grafana version are you using?

5.4.3

What datasource are you using?

MySQL 5.7.25 with time_zone=SYSTEM on a non-UTC system.

What OS are you running grafana on?

official Docker Image (Linux)

What did you do?

What was the expected result?

What happened instead?

[marefr]

Storing time in non-timezone aware format in MySQL and with non-UTC timezone of MySQL is not a supported use case in Grafana. Read #13769 for more information.

[jstrong-tios]

This fix in grafana/pkg/tsdb/mysql/mysql.go allowed me to get past this issue. In my case a timezone-naive Datetime field stored in UTC was getting mangled when grafana queried the unix_timestamp(column) and the mysql server time zone was SYSTEM. I believe the underlying issue is that grafana sets loc paramater in its connection string, but not time_zone:

$ git diff
diff --git a/pkg/tsdb/mysql/mysql.go b/pkg/tsdb/mysql/mysql.go
index 0451f8f0d..6665c4c46 100644
--- a/pkg/tsdb/mysql/mysql.go
+++ b/pkg/tsdb/mysql/mysql.go
@@ -7,6 +7,7 @@ import (
        "reflect"
        "strconv"
        "strings"
+       "net/url"

        "github.com/go-sql-driver/mysql"
        "github.com/go-xorm/core"
@@ -26,12 +27,13 @@ func newMysqlQueryEndpoint(datasource *models.DataSource) (tsdb.TsdbQueryEndpoin
        if strings.HasPrefix(datasource.Url, "/") {
                protocol = "unix"
        }
-       cnnstr := fmt.Sprintf("%s:%s@%s(%s)/%s?collation=utf8mb4_unicode_ci&parseTime=true&loc=UTC&allowNativePasswords=true",
+       cnnstr := fmt.Sprintf("%s:%s@%s(%s)/%s?collation=utf8mb4_unicode_ci&parseTime=true&loc=UTC&allowNativePasswords=true&time_zone=%s",
                datasource.User,
                datasource.Password,
                protocol,
                datasource.Url,
                datasource.Database,
+               url.QueryEscape("'+00:00'"),
        )

        tlsConfig, err := datasource.GetTLSConfig()