Question

Rui_Campos on Tue, 20 Jun 2017 17:36:32


Hi,

We are using Azure MySQL database as a service (Preview) and we need to use different timezones in our application - switching automatically with example:

SET TIMEZONE='Europe/Lisbon';

However, we find out that by default MySQL database is not loaded with timezones information.

To load timezones information we need to do:

/usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uUSER mysql

where USER should be root/admin and have privileges to the mysql schema.

But trying to do that with the 'Server admin login name' as defined on Azure Portal we get errors that user has access denied to make changes to mysql schema.

How can we load timezones information into the MySQL database?

Thanks,

Rui Campos


Sponsored



Replies

StevenCoder1027 on Wed, 21 Jun 2017 03:16:21


Hi

you can set time zone for the Azure MySQL database as a service (Preview) in portal

set timezone for Azure databas for MySQL


Lin Leng on Wed, 21 Jun 2017 05:50:07


Hi Rui Campos,

>>SET TIME_ZONE='Europe/Lisbon';

If I read this correctly, you are trying to change timezone setting at session level, which requires time_zone tables in mysql schema or zoneinfo database in operation system to be populated.

>>/usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uUSER mysql

Well as per BOL this is only used on systems that have a zoneinfo database(such as linux) and I don’t think it’s gonna work with Azure Database for MySQL, so I tried a another solution. Unfortunately I’m seeing the exact same access denied error when doing that. We are currently look into this and will keep you posted in this thread.

If you have any other questions, please let me know.

Regards,
Lin

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


StevenCoder1027 on Wed, 21 Jun 2017 07:52:15


If you want to change the time zone globally, you can try to change the value in portal

and if you to change the time zone by session, you can try to use command like "set TIME_ZONE='+9:00

currently, we don't support format like PST, it requires you update the MySQL table from official pulished data, Supporting upload time zone table is in road map, but not in GA scope now, we like to learn more your scenario, can you help?

Rui_Campos on Wed, 21 Jun 2017 10:00:04


Hi everyone,

Thanks for your answers. 

Explaining better my use case:

Our application is a multi-tenant application with a different MySQL schema for each client. Each client has a different subdomain (e.g. acme.smark.io) and when we get HTTP requests from a specific client we check client information (which includes the name of the database schema for that client and the timezone that client operates) and in runtime we do the following on each MySQL connection:

USE client_database_schema;
SET time_zone = 'client_timezone'

Example for a client in Brazil:

USE smarkio_acme;
SET time_zone = 'America/Sao_Paulo';

The timezone is a parameter that is configured when setting up a new client (account) in our application and therefore we need to be able to support all timezones on our MySQL server and need to be able to switch at session level at runtime.

We need to do that as we do some insert/updates using NOW() from database to set the current time and that needs to be done on client's timezone.

I understand it is not currently available but can this be an operation performed by Azure Support upon request until the feature is implemented and becomes GA? This only needs to be loaded once per MySQL server.

Thanks,
Rui Campos

Tao Wu_MSFT on Fri, 23 Jun 2017 01:50:44


Currently, we don’t allow user to change the system table today, since it might cause unrepairable failures. The time_zone_* is in the sys database, that’s why customer cannot import the data today.
 
Same as system level time zone setting. They cannot use the time zone name, but they can still use the time zone format like “-08:00” as workaround first.
And, this is not a onetime operations. The time zone info is changed at least every one year due to day light saving time change. That’s another reason we are hesitant populate data for user before we have long term solution.
I have recorded this requirement and will investigate more in the future.

Rui_Campos on Fri, 23 Jun 2017 11:39:25


Thank you Lin.

As an update, we have made a change in our application to set the timezone based on the offset interval "-08:00", "+1:00" and it is working ok. 

- Rui