macOS Big Sur MySQL Time Zones

I realise my post titles are looking more and more like cries for help, but what can you do?

Issue- Needed to connect a MySQL database to Zoho Analytics to torture some data. Zoho provides a connector for this which is (argh) Java based and also requires the Oracle driver to be installed separately… but installing MariaDB requires installing Brew and I dislike Brew more at the moment so let’s stick with MySQL.

The MySQL install was version 8.16 and the operating system was macOS Big Sur. Interestingly this install did not have any my.cnf example files, and it was going to be too difficult to learn enough about them to roll my own just to add time zone support. Luckily we did have tz tables in the operating system and it is possible to set the tz option as a global directive without using a my.cnf preferences file.

Check the currently set time zone-

SELECT @@global.time_zone;

it will probably return ‘system’. Next we see if the tz tables are present in the database

SELECT * FROM mysql.time_zone_name;

If you get a null answer here you’ll know they aren’t loaded. Trying to load them gave me this error-

dyld: Library not loaded: @loader_path/../lib/libssl.1.0.0.dylib
Referenced from: /usr/local/bin/mysql_tzinfo_to_sql
Reason: image not found

and it turned out that I needed to link the DYLD Library with the one in /local using

 export DYLD_LIBRARY_PATH="/usr/local/mysql/lib/:/usr/local/mysql/lib"

Then we could try to load the time zone tables again

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

And this time it worked! Now we just have to go back to MySQL and update the TZ with-

SET GLOBAL time_zone ='Australia/Sydney';

and then check again that it’s set properly with an earlier command ‘SELECT @@global.time_zone;’

 

Success! But this will only be correct until a reboot. To make the changes permanent, use the ‘Set Persist’ command, which writes changes to the ‘mysql-auto.cnf’ file. For this it would be-

SET PERSIST time_zone ='Australia/Sydney';

Now let’s reboot and see if it worked- and that’s a yes!