Description:
MySQL 9.7.1 mysql_tzinfo_to_sql does not preserve the future DST rules in a TZif v2/v3 file.
I reproduced this with the unmodified MySQL Community Server 9.7.1 Linux Generic binary and Europe/Berlin from tzdata-2023c.
The zone file contains 143 explicit transitions. The last one is 2140045200 (2037-10-25 01:00:00 UTC), followed by this POSIX rule:
CET-1CEST,M3.5.0,M10.5.0/3
mysql_tzinfo_to_sql exits successfully, but the generated SQL contains only the 143 explicit transitions. After loading the SQL into fresh time-zone tables and restarting MySQL, Europe/Berlin no longer switches to CEST after 2037.
Future winter dates are correct at UTC+1, but future summer dates stay at UTC+1 instead of UTC+2. Fixed +01:00 and +02:00 conversions for the same dates are correct.
I reproduced the same behavior on MySQL Community Server 8.4.10.
How to repeat:
1. Generate the SQL for Europe/Berlin:
mysql_tzinfo_to_sql /usr/share/zoneinfo/Europe/Berlin Europe/Berlin > berlin.sql
2. Load it into fresh MySQL time-zone tables:
mysql mysql < berlin.sql
3. Restart MySQL, then check the imported data:
SELECT COUNT(*) AS cnt, MAX(t.Transition_time) AS max_trans
FROM mysql.time_zone_transition t
JOIN mysql.time_zone_name n ON n.Time_zone_id=t.Time_zone_id
WHERE n.Name='Europe/Berlin';
Result:
cnt = 143
max_trans = 2140045200
4. Test future conversion:
SELECT
CONVERT_TZ('2038-07-15 12:00:00','+00:00','Europe/Berlin'),
CONVERT_TZ('2100-07-15 12:00:00','+00:00','Europe/Berlin'),
CONVERT_TZ('2100-07-15 12:00:00','+00:00','+02:00');
Actual:
2038-07-15 13:00:00
2100-07-15 13:00:00
2100-07-15 14:00:00
Expected Europe/Berlin results:
2038-07-15 14:00:00
2100-07-15 14:00:00
The expected results were also confirmed with the same system zoneinfo using zdump and Python zoneinfo.
Suggested fix:
TZif files can store recurring future rules in the POSIX footer after the explicit transition blocks. mysql_tzinfo_to_sql currently stops after the explicit transition data.
A possible fix is to load the complete TZif file, enumerate transitions after the last explicit transition, and append them before prepare_tz_info().
I prepared a reference patch against MySQL 9.7.1 using the bundled Abseil time-zone implementation and TimeZone::NextTransition(). The patch expands future transitions up to MYTIME_MAX_VALUE. I will attach it through the Contributions tab.
Description: MySQL 9.7.1 mysql_tzinfo_to_sql does not preserve the future DST rules in a TZif v2/v3 file. I reproduced this with the unmodified MySQL Community Server 9.7.1 Linux Generic binary and Europe/Berlin from tzdata-2023c. The zone file contains 143 explicit transitions. The last one is 2140045200 (2037-10-25 01:00:00 UTC), followed by this POSIX rule: CET-1CEST,M3.5.0,M10.5.0/3 mysql_tzinfo_to_sql exits successfully, but the generated SQL contains only the 143 explicit transitions. After loading the SQL into fresh time-zone tables and restarting MySQL, Europe/Berlin no longer switches to CEST after 2037. Future winter dates are correct at UTC+1, but future summer dates stay at UTC+1 instead of UTC+2. Fixed +01:00 and +02:00 conversions for the same dates are correct. I reproduced the same behavior on MySQL Community Server 8.4.10. How to repeat: 1. Generate the SQL for Europe/Berlin: mysql_tzinfo_to_sql /usr/share/zoneinfo/Europe/Berlin Europe/Berlin > berlin.sql 2. Load it into fresh MySQL time-zone tables: mysql mysql < berlin.sql 3. Restart MySQL, then check the imported data: SELECT COUNT(*) AS cnt, MAX(t.Transition_time) AS max_trans FROM mysql.time_zone_transition t JOIN mysql.time_zone_name n ON n.Time_zone_id=t.Time_zone_id WHERE n.Name='Europe/Berlin'; Result: cnt = 143 max_trans = 2140045200 4. Test future conversion: SELECT CONVERT_TZ('2038-07-15 12:00:00','+00:00','Europe/Berlin'), CONVERT_TZ('2100-07-15 12:00:00','+00:00','Europe/Berlin'), CONVERT_TZ('2100-07-15 12:00:00','+00:00','+02:00'); Actual: 2038-07-15 13:00:00 2100-07-15 13:00:00 2100-07-15 14:00:00 Expected Europe/Berlin results: 2038-07-15 14:00:00 2100-07-15 14:00:00 The expected results were also confirmed with the same system zoneinfo using zdump and Python zoneinfo. Suggested fix: TZif files can store recurring future rules in the POSIX footer after the explicit transition blocks. mysql_tzinfo_to_sql currently stops after the explicit transition data. A possible fix is to load the complete TZif file, enumerate transitions after the last explicit transition, and append them before prepare_tz_info(). I prepared a reference patch against MySQL 9.7.1 using the bundled Abseil time-zone implementation and TimeZone::NextTransition(). The patch expands future transitions up to MYTIME_MAX_VALUE. I will attach it through the Contributions tab.