Bug Report: vtgate does not respect default time zone changes in my.cnf

[CAEL0]

Overview of the Issue

I'm living in UTC+9 and I changed the default_time_zone to '+00:00' through my.cnf. But SELECT NOW(); does not return UTC timestamp but local timestamp (UTC+9) whereas SELECT NOW() from foo; returns the right result. So it looks like there's a problem with the evalengine package in vtgate when calculating and returning the result.

Reproduction Steps

1. Change the default time zone to UTC by editing my.cnf.

   mysqld:
   configOverrides: |
     default_time_zone = '+00:00'

2. Check the results.

   
   mysql> select @@global.time_zone, @@session.time_zone; -- time zone is set correctly
   +--------------------+-------------+
   | @@global.time_zone | @@time_zone |
   +--------------------+-------------+
   | +00:00             | +00:00      |
   +--------------------+-------------+
   1 row in set (0.01 sec)

mysql> select now(), utc_timestamp(); -- wrong result: now() and utc_timestamp() must be same +---------------------+---------------------+ | now() | utc_timestamp() | +---------------------+---------------------+ | 2024-09-26 14:56:20 | 2024-09-26 05:56:20 | +---------------------+---------------------+ 1 row in set (0.00 sec)

mysql> select now(), utc_timestamp() from tt; -- expected result +---------------------+---------------------+ | now() | utc_timestamp() | +---------------------+---------------------+ | 2024-09-26 05:56:25 | 2024-09-26 05:56:25 | +---------------------+---------------------+ 1 row in set (0.01 sec)

### Binary Version

```sh
mysql> show variables like 'version%';
+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name           | Value                                                                                                                                                                                                                  |
+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| version                 | 8.0.37-Vitess                                                                                                                                                                                                          |
| version_comment         | Version: 18.0.8-SNAPSHOT (Git revision 7f74ae7fe8537601a3bb2d120d114a8187d4748a branch 'use_timezone_setting_correctly_18') built on Wed Sep 25 06:39:54 UTC 2024 by irteam@buildkitsandbox using go1.22.5 linux/amd64 |
| version_compile_machine | x86_64                                                                                                                                                                                                                 |
| version_compile_os      | Linux                                                                                                                                                                                                                  |
| version_compile_zlib    | 1.2.13                                                                                                                                                                                                                 |
+-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This issue seems to be reproducible in releases 18.0, 19.0, and 20.0.

Operating System and Environment details

N/A

Log Fragments

No response

[dbussink]

Like any Go (and basically any Unix program), Vitess processes also honor the TZ environment variable to set the default timezone. You can use that also to configure MySQL as well (instead of changing my.cnf).

So the expected way this works is to set the TZ environment variable to the timezone you want to have configured. It's not expected nor would we implement trying to honor my.cnf settings inside vtgate. Those are MySQL specific settings and not Vitess configuration variables.

To be consistent, you want to make sure you start all processes in the Vitess cluster involved with the proper timezone (again, easiest through using the TZ environment variable).

[dbussink]

  mysqld:
    configOverrides: |
      default_time_zone = '+00:00'

Given this, it looks like you're using perhaps the Vitess operator? I'd recommend setting a global TZ env variable override then for everything and not just configure mysqld here.