[MDEV-27113] Importing timezone tables does not work in recent version Created: 2021-11-23  Updated: 2022-06-07  Resolved: 2022-06-07

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.6.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Remy Fox Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes MDBF-389 provide timezone data files Closed
Relates
relates to MDEV-18948 Package time zone tables for Windows ... Open

 Description   

According to the documentation we should take our time zone tables from https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html#time-zone-installation.

Importing them gives a syntax error in 10.6.5, whereas is worked fine in 10.5 versions.

 Comments   
Comment by Daniel Black [ 2021-11-23 ]

MySQL documentation isn't the same as ours - https://mariadb.com/kb/en/mysql_tzinfo_to_sql/ (though in this case its pretty close).

Which was the version of mariadb-tzinfo-to-sql (mariadb-tzinfo-to-sql --version)?

What is the syntax error? I couldn't reproduce it. If you are using a mysql_tzinfo_to_sql before 10.6.1 you'll get the syntax error around Offset per MDEV-25556.

Comment by Remy Fox [ 2021-11-23 ]

I used instructions from https://mariadb.com/kb/en/time-zones/#mysql-time-zone-tables for Windows. There is a link in the documentation to import a sql file that you can download from mysql's website. I don't have the exact error anymore, but it failed on this statement:

INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 
 (@time_zone_id, 0, -968, 0, 'LMT')
 
,(@time_zone_id, 1, 0, 0, 'GMT')
 
;

It's the first line which also holds an unquoted 'offset'.

Comment by Daniel Black [ 2021-11-24 ]

I'm so sorry.

Here's a version https://gist.githubusercontent.com/grooverdan/809ae2cb68b062e9b2cf6406e6c14aea/raw/71535b63217251268b6f8e2e83c00deb9f0a2e34/timezones.sql

We'll get on to offering it properly and stop just referring to the MySQL file.

Comment by Daniel Black [ 2021-11-25 ]

wlad how do you feel about including the timezone sql file (perhaps preloaded into table) in the Windows packaging?

Given Windows can run WSL2 containers this should be easy enough though I'm sure the Windows tinezoneapi could generate the same information.

using mariadb container to generate stdout of the sqlfile, stderr contains apt upgrade +

 
$ podman run --rm mariadb:10.6 sh -c '(apt-get update && apt-get install -y tzdata ) 1>&2 && mariadb-tzinfo-to-sql /usr/share/zoneinfo'> /tmp/x
 
Get:1 http://archive.ubuntu.com/ubuntu focal InRelease [265 kB]
 
Get:2 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
 
Get:4 http://security.ubuntu.com/ubuntu focal-security/restricted amd64 Packages [682 kB]
 
Get:3 https://archive.mariadb.org/mariadb-10.6.5/repo/ubuntu focal InRelease [7761 B]
 
Get:5 http://archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
 
Get:6 https://archive.mariadb.org/mariadb-10.6.5/repo/ubuntu focal/main amd64 Packages [17.4 kB]
 
Get:7 http://archive.ubuntu.com/ubuntu focal-backports InRelease [101 kB]
 
Get:8 http://archive.ubuntu.com/ubuntu focal/restricted amd64 Packages [33.4 kB]
 
Get:9 http://archive.ubuntu.com/ubuntu focal/multiverse amd64 Packages [177 kB]
 
Get:10 http://security.ubuntu.com/ubuntu focal-security/main amd64 Packages [1280 kB]
 
Get:11 http://archive.ubuntu.com/ubuntu focal/universe amd64 Packages [11.3 MB]
 
Get:12 http://security.ubuntu.com/ubuntu focal-security/multiverse amd64 Packages [30.1 kB]
 
Get:13 http://security.ubuntu.com/ubuntu focal-security/universe amd64 Packages [823 kB]
 
Get:14 http://archive.ubuntu.com/ubuntu focal/main amd64 Packages [1275 kB]
 
Get:15 http://archive.ubuntu.com/ubuntu focal-updates/multiverse amd64 Packages [33.3 kB]
 
Get:16 http://archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [1692 kB]
 
Get:17 http://archive.ubuntu.com/ubuntu focal-updates/universe amd64 Packages [1098 kB]
 
Get:18 http://archive.ubuntu.com/ubuntu focal-updates/restricted amd64 Packages [738 kB]
 
Get:19 http://archive.ubuntu.com/ubuntu focal-backports/main amd64 Packages [2668 B]
 
Get:20 http://archive.ubuntu.com/ubuntu focal-backports/universe amd64 Packages [7185 B]
 
Fetched 19.8 MB in 7s (2777 kB/s)
 
Reading package lists...
 
Reading package lists...
 
Building dependency tree...
 
Reading state information...
 
tzdata is already the newest version (2021e-0ubuntu0.20.04).
 
0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded.
 
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
 
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
 
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.

Comment by Vladislav Vaintroub [ 2021-11-25 ]

Windows can run WSL and what not, but I would rather be hesitant to make it prerequisite for builds.
I do not know much about timezone API, traditionally Windows naming was incompatible to IANA,
but ICU is included into Windows 10, and ICU seems to be using IANA tzdata, which perhaps Linux is also using, so using ICU seems like a better option.
See https://devblogs.microsoft.com/oldnewthing/20210527-00/?p=105255

Is this table preloaded on many/most Linux installations (I've no idea, did not research) ?

Comment by Daniel Black [ 2021-11-25 ]

Linux distro tend to not to preload it however the Docker Library mariadb container does. The process used to be to find https://mariadb.com/kb/en/time-zones/#mysql-time-zone-tables and get the SQL files from MySQL, which doesn't work with 10.6+.

So we're at the point of integrate it into windows in some form, or provide some SQL files directly (which we might have to do for a little while with 10.6 releases out).

Comment by Sascha Meyer [ 2022-05-31 ]

@DanielBlack please note that the unquoted `Offset` term in the Gist file leads to an SQL exception when running it in HeidiSQL as "Offset" seems to be a reserved term. Escaping it with grave accents solved the issue.

Comment by Daniel Black [ 2022-06-01 ]

SaschaM78, I'm a little confused. Offset is quoted with grave accents in the gist. MDEV-28263 reworked the sql file to be like this. Is there any HeidiSQL issues with this?

Comment by Sascha Meyer [ 2022-06-02 ]

@DanielBlack you are right, it seems HeidiSQL had striped out the accents on copy-paste. Sorry for causing this unnecessary trouble.

Comment by Daniel Black [ 2022-06-07 ]

now mirrored - https://mirror.mariadb.org/zoneinfo/

updated https://mariadb.com/kb/en/time-zones/#mysql-time-zone-tables

Generated at Thu Feb 08 09:50:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.