Charting by months and zoneinfo

This is just a record of a sneaky problem we’ve been debugging during testing RT 5.0.0.alpha1 that might hit someone else. It isn’t an RT5 (or indeed RT at all) issue but a problem with time zones in the database.

The issue we had was that making charts from TicketSQL searches and displaying counts of tickets by created month just produced one column on our RT5 test box, rather than the 12 monthly columns on our RT4.4.3 live box. We thought it was an RT5 bug so reported it to Best Practical but Jim couldn’t reproduce it.

I dug in further by looking through the RT code and then turning on SQL statement logging. This allowed us to home in on the MariaDB CONVERT_TZ() function - it always returned NULL. As this was used in the SQL query generated by RT (twice!) the data we were getting out just had one row in it, hence the single column in the chart.

The fix is easy: manually load the time zone information in the database. This was on our test CentOS 7 machine, and it appears as though the MariaDB installation didn’t do this automatically, which it had done on our live CentOS 6 server. The command you want if you hit this yourself is:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Run that from the command line, pop your database root password in, wait for a few seconds and then all will be well with your timezones and charts.