The heart of everything to do with computers lies in the most intangible of things – time! We all want to know at what time was a record entered, when did our patron borrow / return a book, when is fine to be calculated, when are our IndexData Zebra re-indexing is supposed to run, when are our email / sms notifications supposed to go out etc. Mostly Koha keeps track of all these (and much more) using datetime data in the database tables. Most of these fields are set by default to CURRENT_TIMESTAMP. As a result when we transact using Koha or update our records, the MySQL server automatically fills in the exact datetime values using the system time. This “automatic” business can be an aid or a hindrance – as you will get to see in a moment.
The backstory a.k.a how we ended up with this
We were using an OpenVZ based VPS located in the US east coast for a client who is located in India. Since OpenVZ VPSes have their time controlled by the host system, there is not much we can do (unlike we can with KVM or Xen based virtualization). Luckily the host itself was setup with ntpdate, so at least the time was accurate. We took the next best route, setting up the timezone using ‘Asia/Kolkata’, which is the TZ definition for India on Linux.
$ sudo dpkg-reconfigure tzdata
It exited successfully showing the following output:
Current default time zone: 'Asia/Kolkata' Local time is now: Wed Sep 7 04:00:01 IST 2016. Universal Time is now: Tue Sep 6 22:30:01 UTC 2016.
The problem
Restarting the VPS, we proceeded with our task and things looked OK. Around 4:39 AM while importing a .mrc file we saw something that clearly showed we had missed something. The lcoal time was September 07, 2016 4:41 AM and yet Koha told us that we had uploaded the .mrc file yesterday – September 06, 2016 at 7:11 PM! Woah!
We quickly referred to the super awesome Koha database schema website and looked up the “import_batches” table. Sure enough, the upload_timestamp was defined as timestamp with the default set as CURRRENT_TIMESTAMP. Thus, while our timezone correction had worked with the rest of system *and* Koha, MySQL was using something else as its time.
Switching to a terminal we fired up the command-line mysql client and took a closer look.
mysql> SELECT @@system_time_zone; +--------------------+ | @@system_time_zone | +--------------------+ | EDT | +--------------------+ 1 row in set (0.00 sec)
Hmmmm… Eastern Daylight time is -4:00 hours behind GMT, just as we are +5:30 hours ahead. A second query further confirmed this.
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2016-09-06 19:29:17 | +---------------------+ 1 row in set (0.00 sec)
The solution
A quick read of http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html and a cross-check inside our /etc/mysql/my.cnf – the mysql configuration file, confirmed that we needed to setup our default timezone and move our MySQL server from the system timezone defaults coming in from the host system’s EDT timezone.
“The value can be given as a named time zone, such as ‘Europe/Helsinki’, ‘US/Eastern’, or ‘MET’. Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.” – MySQL documentation
Going back to our mysql client, we decided to check the time_zone and time_zone_name tables inside the mysql system database. Both turned out to be blank.
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from time_zone; Empty set (0.00 sec) mysql> select * from time_zone_name; Empty set (0.00 sec)
Since we must have the time_zone_name table populated with our zoneinfo data (which resides under /usr/share/zoneinfo/) we turned to the nifty utility mysql_tzinfo_to_sql.
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
It went through and we checked the time_zone_name table again. This time time_zone_name returned 1808 rows. With our timezone reference setup, we turned to /etc/mysql/my.cnf and added the following line under the [mysqld] section in the file:
default-time-zone='Asia/Kolkata'
Restarting the MySQL server with sudo service mysql restart we re-checked select now() and SELECT @@system_time_zone. The results were:
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select now(); +---------------------+ | now() | +---------------------+ | 2016-09-07 05:44:00 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT @@system_time_zone; +--------------------+ | @@system_time_zone | +--------------------+ | IST | +--------------------+ 1 row in set (0.00 sec)
Success!
We reloaded the Manage staged MARC records page in Koha and sure enough it now showed the correct date and time of the upload.
