How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?

Question

When I run the following command I get an error, however one of my scripts requires it.

SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

Answer 1

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

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
Enter password: 
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

Trivia: If you want to know what the HY00 stands for (a follow up I asked years later)....

Answer 2

As kaiser suggested, the trailing slash is unneeded but without specifying mysql as the database mariadb complains about a missing database. I found that

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

was the correct way to solve the problem in Mariadb 164.

Answer 3

In case you use MySQL on windows and get the error below

SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

From my post on how to resolve this issue:

The issue is you do not have the time zones data installed, to install the time zones on a Windows machine, you will need:

1. Download "timezone_2020a_posix.zip - POSIX standard" from https://dev.mysql.com/downloads/timezones.html

2. Stop the MySQL service

3. Extract the files from the downloaded file and copy them with overwrite to C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql

   Yes, to C:\ProgramData.. and not to C:\Program Files (x86)\MySQL...

4. Start the MySQL service