Wednesday, January 30, 2013

Change Amazon RDS database timezone to non UTC

Amazon RDS is a very powerful hosted Relational Database solution. I recently came across a big annoyance with it which was not being able to set a default timezone. I had a PHP application along with several scripts talking to MySQL on Amazon RDS.

There are many solutions available around the internet like changing the code on application side and setting the timezone on every connection from the application. However, i wanted a server side solution  to ensure that the timezone is changed for every connection. It would have been hazardous if i fail to change the code even at one place in my applications/scripts.

Many places talked about using CURRENT_USER() function in a stored procedure, checking if the user is not rdsadmin, then set the timezone for that session. However, it did not work for me. Also i wanted to change timezone only for certain users in my database. So, without further discussion, i will jump straight into the implementation.

I created a stored procedure named "change_time_zone" in "mysql" database which is the default database in a MySQL installation.
DELIMITER #
CREATE PROCEDURE mysql.change_time_zone ()
IF user() REGEXP '^(user1|user2|user3)' THEN
SET SESSION time_zone = "America/New_York"
END IF #
DELIMITER ;
The `user()` information functions tells us about the current logged in user. I wanted to execute this procedure only for user1, user2 or user3. Doing a regular expression match here saved me from complex IF conditionals. I simply check of usernames at the beginning of the string.

My second step was to grant execute permission for this stored procedure to all users which was done by:
GRANT EXECUTE ON PROCEDURE `mysql`.`change_time_zone` TO 'user1'@'%';
GRANT EXECUTE ON PROCEDURE `mysql`.`change_time_zone` TO 'user2'@'%';
GRANT EXECUTE ON PROCEDURE `mysql`.`change_time_zone` TO 'user3'@'%';
Doing this will ensure that i do not get the MySQL server has gone away error.

The final step is to execute this stored procedure on every database connection. You can either do this from RDS command line tools or the AWS GUI. I did this from the AWS GUI. Go to your database params and search for "init_connect" and edit its value. Set the value to:
CALL mysql.change_time_zone

For clarity, i have included a picture of how it will be changed.



Now, every time any application connects to our RDS database with any of the users mentioned in the stored procedure, the time will change to EST.

I hope this information is helpful to fellow developers.

2 comments:

  1. If this works, it will be very useful to us.

    Many thanks for your effort.

    ReplyDelete
  2. Worked for me. If you need any help, let me know. I will be glad to help.

    ReplyDelete