What has you here today?    work history (html) about me tajik bookmarks

Forcing Amazon RDS databases to non-UTC server timezone 6th of July, 2011 POST·MERIDIEM 06:37

Amazon Relational Database System (RDS) is a fine service, allowing you to move MySQL and Oracle databases to the care of a company that administers them and backs them up competently and in huge number, and charges a not-unreasonable price for it.

Now, if you’ve chosen MySQL as your database, you’ve probably made other less-than-optimal programming decisions in the past too, like not storing timestamps as UTC, and relying on the database server to have the same time zone as most of the clients. (The latter of which is, in many contexts fine; cf. Japanese developers developing apps for Japan, a country of 120 million people with one time zone.)

Unfortunately, Amazon doesn’t support setting the SQL server time zone of an RDS database instance, it requires UTC. kaz_goto in that thread offers a solution, but that solution hangs when the database instance is rebooted. I’ve developed that solution a little further; my approach requires the following stored procedure in the database: 

 
DELIMITER | 
CREATE PROCEDURE mysql.store_time_zone () 
IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN 
   SET SESSION time_zone = 'Europe/Dublin'; 
END IF | 
DELIMITER ;
And, as in kaz_goto’s post there, the database parameters need to be modified to include something with a space—follow his instructions, but make sure the result reflects the following: 
$ rds-describe-db-parameters PARAMGROUP --source=User
DBPARAMETER  Parameter Name  Parameter Value             Source  Data Type  Apply Type  Is Modifiable
DBPARAMETER  init_connect    CALL mysql.store_time_zone  user    string     dynamic     true 
$

Once this is done, rebooting will not be a problem, though starting up databases with that parameter group but without the associated stored procedure will lead to hangs on connection.

Word of the day: die Umnachtung is German for derangement.

From Paul on the 7th of July at 16:42

Thanks for the great post. We’d been struggling to resolve this for quite a while (and had almost to resorted to setting the timezone at an application level as per http://aws-musings.com/amazon-relational-database-service-rds-the-timezone-problem/) but your post worked a treat.

Note there is a typo and it should be CURRENT_USER() (rather than with a space).

I do have a couple of questions. Firstly, why this works and secondly, are there any side effects?

Presumably, the answer to the first question is along the lines that the database connection resets experienced when using init_connect set with "set timezone =..." directly are caused by the rdsadmin user not liking having a timezone other than UTC, but if that is the case, how did you know?

Many thanks again.

Paul.
From Aidan Kehoe on the 9th of July at 18:16

Hi Paul,

Thanks for the correction!

It was just a working hypothesis that not setting the time zone for the rdsadmin user would allow rebooting; I don’t have any inside knowledge from Amazon, and I only know it works from testing it out. I suppose some code that Amazon runs on RDS instances on reboot deals badly with non-UTC time zones.

In terms of side-effects, note my second-last paragraphs; starting up databases with that parameter group but without the associated stored procedure will lead to hangs on connection. The solution is to reset the parameter group, and perhaps reboot the database. I can’t think of any other side-effects—all the Amazon RDS code should run as the rdsadmin user, running it as anyone else is asking for trouble on their part.

Best,

Aidan
From Paul on the 11th of July at 11:23

Hi Aidan,

Thanks for the update. Your paragraph about the proc being missing is noted (I appreciate that would indeed cause problems).

We had some issues with processes locking (probably bad implementation on my part ;-D), and I was using "show full processlist;" to diagnose locking issues when noticed there was a rdsrepladmin user used for replication.

I presume that setting the timezone for the master rdsrepladmin user would be a bad idea if the same user was set to a different timezone for a read-only replica? However, for Multi A-Z RDS databases, we don’t have access to the slave database, so can only assume that parameter group init_connect changes are also applied to the slave, as otherwise we’ll run into trouble? We could extend your stored proc to not set the timezone for the rdsrepladmin user either, but it makes sense to me to have that user using the same timezone as the application, don’t you think?

Do you have any experience of the various replication options (Multi A-Z or read only replicas) with timezones set other than UTC?

Many thanks again,

Paul.
From Aidan Kehoe on the 12th of July at 22:59

Hi Paul,

Short answer; no, I don’t have any experience of the various replication options, and theoretically a non-UTC time zone could be problematic for them if they use specific users for this purpose and didn’t program in a time-zone-independent way. If you’re sure you’ll never use a username starting with "rds", then you could change the string check to that.

Thanks for the follow-up for the sake of anyone searching, best,

Aidan
From Paul on the 13th of July at 15:54

Hi Aiden,

The only beef I have with _not_ setting the rdsrepladmin user to my chosen timezone is that I can’t quite get my head round whether that would alter the timezone of my data during replication. I guess, as long as the replication user is using the same timezone both "ends" (master and slave), then it shouldn’t matter what that timezone is and it shouldn’t change the time of time based data. I’ll have to try it and see!

Cheers,

Paul.
From ronin on the 23rd of March at 23:32

I was having issues after performing the above, and it was only that I hadn’t thought to give permissions to my user on the stored procedure in the mysql database. My user only had access to one database not the mysql.* database.

GRANT EXECUTE ON PROCEDURE `mysql`.`store_time_zone` TO ’some_user’@’some_host’;

Here is where I found the code above; http://blog.ncode.ca/?p=164
From Praveen on the 9th of April at 13:35

Thanks a lot , that worked like a charm :)

From sohail on the 8th of October at 7:29

Searching this solution for a while, worked, CHEERS !!

Comments are currently disabled.