sql: time zone name not recognized when running CockroachDB on windows

[pungggi]

Cannot connect to DB under Windows:

Other users have similar problem with founding timezone see> #31297 (comment)

Environment:

• CockroachDB version [2.1]
• Server OS: Windows 10 (for development)
• Client app [DBeaver 5.2.4]

[pungggi]

I now changed the settings on my machines to UTC, this works.. So the question is how can I properly change the timezone in crdb on windows?

[knz]

@BramGruneir could you take a look at this and determine 1) if you can repro the issue 2) what do you think the cause is

(you don't need to actually solve the issue at this point - we just need a clear picture of what is going on)

[knz]

@pungggi until we investigate this, could you consider running CockroachDB inside a Linux container/VM, and connect to it over the network from the Windows DBeaver?

[pungggi]

@knz I can actually run cockroach no problems there, just the connection to dbeaver is not working because I cannot change the timezone. But i will change the timezone of the machine to be aligned with the driver (UTC) and then reset again.

[helloalbin]

I am facing the same issue here when connecting from the Java program.

ERROR: invalid value for parameter "TimeZone": "Asia/Kolkata"
Detail: timezone data cannot be found

What is the correct TimeZone value?

[knz]

The state of this is that the Go runtime system on Windows does not maintain the same timezone database as on other systems.

As a result the "name" timezones do not work on Windows.

To fix this would require a non-trivial investment in the way the CockroachDB binary is built on windows.

To work around:

• run CockroachDB on a unix system, eg via a VM or container
• use a numeric timezone, eg +07:00

[bdarnell]

This is an annoying trap in the go standard library: if the go toolchain is installed, it will use timezone data from that toolchain even on windows. This is why our testing on windows didn't uncover this, because we haven't done much testing on machines that didn't have go installed.

The place-name time zones are important (numeric time zones are not a substitute because they don't specify which DST rules to use), so I think we're going to have to embed a copy of the zoneinfo database into CRDB to address this.

[JAC2703]

I'm getting the same issue on v 2.1.5 running the server on Ununtu Linux, and the Java client (42.2.5) on a Windows box.

[bdarnell]

@JAC2703 Do you have the tzdata package installed on the linux box? Which time zone are you trying to use?

[JAC2703]

@bdarnell tzdata was missing on one of the nodes which was likely what I was connecting to. Installing it fixed the issue. Can we update the pre-req's on the installation page to require this also? Thanks for the pointer.

apt install -y tzdata

[bdarnell]

Yeah, we should document that. I thought tzdata was installed by default on ubuntu. Were you using a particular variant that was missing it?

[JAC2703]

I'm using a VPS variant (scaleway.com) that's likely stripped it out. If it's happened once though, I'm sure someone else will hit the same issue. It does, however, show up on the 18.04 manifest for server: http://releases.ubuntu.com/bionic/ubuntu-18.04.2-live-server-amd64.manifest

[knz]

The discussion should continue on #36864.

[GuyFran]

Is there an update on this issue ? As much as possible I would like to run my cockroach cluster in Windows directly.

[knz]

Time zone issues are only maybe 5% or less of the work needed to make CockroachDB production-ready on Windows.
FYI there is currently zero testing happening on Windows and we suspect that the performance profile will be rather different due to the way windows handles its filesystem buffer cache and memory allocation.
So yes we plan to improve the situation with time zones, but that will unfortunately not answer your need. If you want to run CockroachDB on windows today, your best bet will be a linux emulation via docker, vmware or similar. Or maybe windows 10 with linux services.

[jordanlewis]

Closing in favor of #36864