Version portability added by MDEV-28263 in case its used on < 10.6 versions.

Daniel, some questions:

• is this something that we should propose on mariadb.org or on our mirrors?
• is there any way to verify that the sql result is OK before putting it live?
• what granularity do you expect for this to be updated?
• shouldn't we gzip this sql file (500K vs 5M)?

1. propose

I'm missing the context of the querstion "propose" asks here.

We should put it somewhere, and mirrors seem a more accessible place to update than mariadb.org.

2. testing

docker run -e MARIADB_INITDB_SKIP_TZINFO=1 -e MARIADB_ROOT_PASSWORD=bob --rm --name m106test docker.io/library/mariadb:10.6

Weak attempt at a basic timezone test:

use mysql;

source xxx.sql; /* pass in by volume maybe */

SET time_zone = 'America/New_York';

SELECT NOW() INTO @nytime;

SET time_zone = 'France/Paris';

SELECT NOW() INTO @paristime;

SELECT @nytime < @paristime;

3. update granuality

https://github.com/docker-library/repo-info/commits/master/repos/ubuntu/remote/20.04.md seems to be few months

http://changelogs.ubuntu.com/changelogs/pool/main/t/tzdata/tzdata_2022a-0ubuntu0.20.04/changelog also very sparotic if we did apt-get update && apt-get install tzdata as part of the cron job.

Or go to the source {{curl -L --etag-save tzdata.etag -o tzdata-latest.tar.gz https://www.iana.org/time-zones/repository/tzdata-latest.tar.gz
}}, or repo https://github.com/rogierschouten/tzdata-generate/commits/master

But monthly from ubuntu updates seems ok.

4. gzip

Does window support gzip well now? Thought old ZIP might be ok though.

tested with:

/tmp/m 

$ (echo 'use mysql;';  docker run --rm docker.io/library/mariadb:10.6 mariadb-tzinfo-to-sql /usr/share/zoneinfo ) > zoneinfo.sql

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.

/tmp/m 

$ docker run -e MARIADB_INITDB_SKIP_TZINFO=1 -e MARIADB_ROOT_PASSWORD=bob -v ./:/docker-entrypoint-initdb.d:z --rm --name m106test -d docker.io/library/mariadb:10.6 

95ff612948ec24c64ed45dbf463e9e5e16aa5ace4466dd577805f792278c21ed

$ docker exec  m106test mariadb -pbob -e "

SET time_zone = 'America/New_York';

SELECT NOW() INTO @nytime;

SET time_zone = 'Europe/Paris';

SELECT NOW() INTO @paristime;

SELECT @nytime < @paristime;

SHUTDOWN"

@nytime < @paristime

1

Having "use mysql;" at the top of the sql file is probably useful to avoid use errors.

Without timezones loaded, immediate errors occur:

MariaDB [(none)]> SET time_zone = 'America/New_York';

ERROR 1298 (HY000): Unknown or incorrect time zone: 'America/New_York'

Can also be tested on < 10.6 version:

$ docker run -e MARIADB_INITDB_SKIP_TZINFO=1 -e MARIADB_ROOT_PASSWORD=bob -v ./:/docker-entrypoint-initdb.d:z --rm --name m103test -d docker.io/library/mariadb:10.3

$ docker exec  m103test mysql -pbob -e "

SET time_zone = 'America/New_York';

SELECT NOW() INTO @nytime;

SET time_zone = 'Europe/Paris';

SELECT NOW() INTO @paristime;

SELECT @nytime < @paristime;

SHUTDOWN"

Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.

@nytime < @paristime

1

Tested also that a syntax error in zoneinfo.sql will cause the container not to start.

https://gitlab.com/mariadb/sysadmin/-/merge_requests/29

Thanks danblack. This has been merged and rewritten a bit (improve logging mechanism and error handling mainly and adapt PATH for production).
A routine has been deployed on the mirror machine and zoneinfo.sql is now generated everyday in /zoneinfo/zoneinfo.zip. I need to sync now with dbart in order to decide how we can push this on osuosl and netcologne (and every mirror will pull from there).

danblack the zoneinfo file is now replicated on the fi mirror (under our control), this is the only mirror serving the file until we put it on netcologne and osuosl with dbart.
https://mirror.mariadb.org/zoneinfo/zoneinfo.zip?mirrorlist

Next time the docker container is updated, I would like to verify that the whole process is working good. I switch the script to using podman instead of docker and I hit a nice bug in conman, so I have added another check. You can probably have a look when you have time, it's almost finished I guess:
https://gitlab.com/mariadb/sysadmin/-/blob/deploy-tzdata/ansible/zoneinfo/generate_zoneinfo.sh

Looks good. Thanks.

Welcome to the use of podman too. (I was too, just had docker=podman alias, and a newer version that avoided the bug).

It occurred to me maybe zonedata_old.zip is a better filename for the old version.

Production scripts in home directories doesn't look great either.

I'm really happy we have a solution now.

> It occurred to me maybe zonedata_old.zip is a better filename for the old version.

You mean zoneinfo_old.zip, should we keep it then?
It's not the case for the moment.

> Production scripts in home directories doesn't look great either.

Yep, fully agree on that, it's already on my TODO list to add a proper sync user for the mirror machine but it will have to wait a bit since it depends on other task.