On one of our production environments, we had an issue our application related to time, specially modified time and creation time of the entities. It was quite random in nature for some entities.

We suspected that there might be issue in the timezone configuration.

Analysing the problem

On production environment, we had limited permissions. We were not allowed to change system timezone. We noticed that Jboss is automatically picking the system timezone “Europe/Berlin” (which was configured from root user) and if we check the timezone for the linux user for which we have access it shows GMT time. The MySQL database is picking the GMT timezone.

This mismatch of timezone were creating issues when creating record/updating record in database. Default current date generated from Java comes different than the “CURRENT_TIMESTAMP” of MySQL.

How JVM gets default timezone

JVM gets the default timezone as follows:

• Looks to environment variable TZ if it is not set then
• JVM looks for the file /etc/sysconfig/clock and tries to find the ZONE entry.
• If the ZONE entry is not found, the JVM will compare contents of /etc/localtime with the contents of every file in /usr/share/zoneinfo recursively. If found it will assume that timezone.

[admin@localhost ~]$ echo $TZ
Europe/Berlin
[admin@localhost ~]$ 

or

[admin@localhost ~] $ cat /etc/sysconfig/clock
ZONE=”Europe/Berlin”
UTC=false
ARC=false
[admin@localhost ~] $

Set timezone in MySQL database

Let’s set the timezone in MySQL database.

If you find any value configured in above command , then you need to set the same value for MySQL timezone.

For example, if you find “Europe/Berlin” as shown in the above example, then the value of MYSQL timezone can be changed as follows:

1. Verify MySQL timezone database:

   If you find a record as below

   mysql> SELECT * from mysql.time_zone_name t where name = ‘Europe/Berlin’;
   +----------------------+--------------------+
   | Name | Time_zone_id |
   +----------------------+---------------------+
   | Europe/Berlin | 403 |
   +---------------------+---------------------+
   1 row in set ( 0.00 sec)
   

   then go for step 3 to setup mysql timezone otherwise go to step 2 to load mysql timezone from Operating System’s timezone database.

2. Load Mysql timezone database from Operating System’s timezone database using following command :

   mysql_tzinfo_to_sql [time_zone_db_path] | mysql –u[username] -p[password] mysql
   

   where [username] and [password] are replaced by the actual values, and username here should be root which is having the mysql database administration access. The [time_zone_db_path] should be replaced with actual path for Operating System’s timezone database (the default path on linux is /usr/share/zoneinfo)

   As shown above, this command will ignore some timezones which are not supported by mysql. After running the above command, please verify mysql timezone in the database again (as shown step 1). The timezone should be present now. However, if you still do not find any record in mysql database of your interest, then please contact the system administrator of Linux, to install the correct timezones in operating system’s database. If everything works correctly, please move to the next step of setting the MySQL timezone.

3. Setting MySQL timezone

   a. Login into the system with user root.

   b. Open file /etc/init.d/mysqld

   c. Locate following code

   # Pass all the options determined above, to ensure consistent behavior.
   
   # In many cases mysqld_safe would arrive at the same conclusions anyway
   
   # but we need to be sure. 
   
   ` /usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile" \ 
   
   --log-error="$errlogfile" --pid-file="$mypidfile" \
   
   /dev/null 2>&1 &`
   

   d. Add --timezone="Europe/Berlin" as follow :

   /usr/bin/mysqld_safe --datadir="$datadir" --timezone="Europe/Berlin"--socket="$socketfile" \ --log-error="$errlogfile" --pid-file="$mypidfile" \ /dev/null 2>&1 &
   

   More details on how to setup timezone on linux can be found at Linux Tips and Default Timezone in Java