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.