IssueTracker

In reviewing https://cloud.google.com/sql/docs/mysql/flags, since a DB restart is required to apply the changes to default_time_zone flag, it isn't a very desirable work around.

Any updates on this issue?

Not sure if this is a viable workaround, but I'm currently testing this in our instances that need support for daylight savings time:

# gcloud sql instances patch <instance name> --database-flags init_connect="SET time_zone = 'America/Chicago'"

Essentially, since we can't set time_zone globally, I'm having it set for the session each time a client connects. This gave me the expected behavior in our services using CloudSQL.

This issue is actually nr 8 in most wanted CloudSQL feature requests. But after 3 years of waiting for any feedback, one might assume things will not change from the Google CloudSQL team perspective... no comments, no workarounds, no fixes, no features... just no input at all makes it very hard to understand their stand in this, which is presumably what they see as most fit "for now".

One might question that MySQL daylight saving computation capabilities are ideal. However, not providing this feature still blocks migrating legacy production setups to CloudSQL, in many cases.

Documentation explicitly states that named timezones are not supported and highlights the administrative reasoning behind it, which makes it less likely that this feature will be implemented.

Setting the time zone for MySQL instances @ https://cloud.google.com/sql/docs/mysql/known-issues#administrative_issues

It is possible to set the MySQL time zone to a named area, such as "Europe/Moscow", using a session variable. However, doing so is not supported in Cloud SQL, and is not guaranteed to provide up-to-date time settings. To change the default time zone for your instance, update the default_time_zone flag with the offset from UTC (for example, +10:00). Automatic adjustment to daylight savings time is not supported; you must update the default_time_zone flag manually to account for daylight savings time._

The workaround renttherunway and the docs mention allows to set a named timezone on each connection, sort of fixing this issue.

gcloud sql instances patch <instance name> --database-flags init_connect="SET time_zone = 'America/Chicago';"

However, how can one trust the timezone and especially the DST transitions are correctly populated in the timezone tables?

Docs: doing so is not supported in Cloud SQL, and is not guaranteed to provide up-to-date time settings.

In our case, found out the hard way a difference between Linux and Windows OS based timezone information, which MySQL uses to compute DST transitions. So correcty populated mysql.time_zone_xxx tables, with cluster-wide equal records and transitions etc, are crucial. Luckily we can populate the mysql.time_zone_xxx tables ourselves in CloudSQL.

BUT, what happens when a CloudSQL instance backup is restored? Or in case of maintenance? Are the mysql.time_zone tables records preserved? Will CloudSQL team at any point touch them, or leave them alone?

Storing SQL statements in vcs to populate time_zone table gives us ability to easily (re)populate the correct version of all transitions etc, but still not ideal. Would be nice to know how CloudSQL handles this, in case of backups/maintenance?

@CloudSQL Team: are we allowed and trusted to provide up-to-date time setting this way? Are customer created settings preserved and respected?

Anybody? :)

Google Cloud Support response:

I understand that you want to know if the timezone information populated in the “mysql.time_zone_xxx” tables will be preserved after restoring an instance from backup or after maintenance. Please correct me if I have misunderstood.

To confirm if restoring a Cloud SQL for MySQL instance from backup persists the timezone information, I tried reproducing your use case by following the below steps, and I can confirm that timezone information is preserved.

1. Created a Cloud SQL for MySQL instance.
2. Populated the “mysql.time_zone_name” table with timezone information, and took a backup.
3. Restored the backup created in the above step to the same instance.
4. Queried the “mysql.time_zone_name” and I can see that the timezone information persisted even after restoring the instance from backup.

Similarly for maintenance, we only update the operating system and the database engine, however the data in the tables remains unchanged.

Hi Cloud SQL for MySQL users,

We are working towards making named timezone support available for MySQL users, allowing users to set their database's timezone settings through the global time_zone flag to a named timezone that includes the adjustments for daylight savings time. As mentioned in previous comments, the named timezone information is available at the connection step today; however, the time zones tables on the instance are not yet updated regularly and they may be out of date. We will address these issues when we make support generally available.

https://b.corp.google.com/issues/74600960#comment26

In the interim, please keep upvoting this item so we can continue to prioritize it.

Best,

Akhil

Cloud SQL Product Manager

Hey Cloud SQL team,

I meet a customer last week that was complaining exactly about this gap. Do you happen to any timeline for this feature? https://cloud.google.com/sql/docs/mysql/known-issues#administrative_issues

Thanks,

Federico