Rui_Campos on Tue, 20 Jun 2017 17:36:32
We are using Azure MySQL database as a service (Preview) and we need to use different timezones in our application - switching automatically with example:
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?
StevenCoder1027 on Wed, 21 Jun 2017 03:16:21
you can set time zone for the Azure MySQL database as a service (Preview) in portal
Lin Leng on Wed, 21 Jun 2017 05:50:07
Hi Rui Campos,
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.
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
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.
Tao Wu_MSFT on Fri, 23 Jun 2017 01:50:44
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.
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.
Andrea Lam on Wed, 18 Jul 2018 22:50:27
You should now be able to populate the time zone tables on your MySQL servers using the “az_load_timezone” stored procedure. Documentation has been updated with how to populate the tables and set the time zone parameter at the global and session levels. Please let me know if you have any additional questions.
Azure portal: https://docs.microsoft.com/en-us/azure/mysql/howto-server-parameters#working-with-the-time-zone-parameter
Azure CLI: https://docs.microsoft.com/en-us/azure/mysql/howto-configure-server-parameters-using-cli#working-with-the-time-zone-parameter
Program Manager, Azure Data