My Usenet posts
Forcing Amazon RDS databases to non-UTC server timezone 6th of July, 2011 POST·MERIDIEM 05: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.