Alert MySQL users when they're missing timezone data

[paulrosenzweig]

What's the issue?

MySQL needs to have the system timezone info loaded into its system tables. (docs) When that's not done, it leads to confusing timezone errors in Metabase where reporting timezone doesn't work correctly. (#10335, discourse)

What should we do?

It'd be nice to detect this state and alert users. I think we could either add an alert on the database config page or insert a banner next to the "Report Timezone" dropdown if any connected database has this issue.

The fix is pretty simple once a user knows to do it:

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

How can we detect this?

SELECT COUNT(*) FROM mysql.time_zone_name;

If that's empty we know they're missing all data.

Another approach would be to try and do a conversion and see if it fails.

Without timezone data:

mysql> select CONVERT_TZ('2000-01-01 00:00:00','US/Pacific', 'UTC');
+-------------------------------------------------------+
| CONVERT_TZ('2000-01-01 00:00:00','US/Pacific', 'UTC') |
+-------------------------------------------------------+
| NULL                                                  |
+-------------------------------------------------------+
1 row in set (0.00 sec)

With timezone data:

mysql> SELECT CONVERT_TZ('2000-01-01 00:00:00','US/Pacific', 'UTC');
+-------------------------------------------------------+
| CONVERT_TZ('2000-01-01 00:00:00','US/Pacific', 'UTC') |
+-------------------------------------------------------+
| 2000-01-01 08:00:00                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

:arrow_down: Please click the :+1: reaction instead of leaving a +1 or update? comment

[1renwuruiming]

@flamber when I execute mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql and I find this result but I still got the same error like #24625

[flamber]

@1renwuruiming Please use the forum for questions and troubleshooting: https://discourse.metabase.com/

[1renwuruiming]

@1renwuruiming Please use the forum for questions and troubleshooting: https://discourse.metabase.com/

which issue can I consult @flamber