Cannot connect to MySQL with time zone

[knutwannheden]

I am trying to use MySQLContainer to create a MySQL container using a specific time zone.

Reading the documentation (see https://dev.mysql.com/doc/refman/8.0/en/timezone-problems.html and https://dev.mysql.com/doc/refman/8.0/en/environment-variables.html) this should work by setting the environment variable TZ, which I can achieve using GenericContainer#withEnv(String, String). When I do this, (e.g. by calling withEnv("TZ", "Europe/Zurich")) the container fails to start properly and I run into a timeout in my JUnit test, without any helpful log messages.

After some more testing and reading I found out that I should also set the serverTimezone property (see https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html). Unfortunately the only simple way of achieving this I found was to subclass MySQLContainer and override constructUrlForConnection(String).

To me it seems like it could be useful to add some kind of JdbcDatabaseContainer#addProperty(String, String) (or withProperty()?) method and then use the supplied properties in JdbcDatabaseContainer#createConnection(String). Or possibly have a protected getProperties() method? Or am I going about this the wrong way?

[knutwannheden]

I realize that I can for other properties simply call JdbcDatabaseContainer#createConnection(String) and supply query parameters using the String parameter, but in this case the problem is JdbcDatabaseContainer#waitUntilContainerStarted() which calls the method with the empty string as the parameter.

[rnorth]

Sorry for the slow reply, @knutwannheden.

This is an interesting problem. I don't think it's a bad idea to add a withUrlParam(String, String) method or similar.

Still, it does seem really odd that a URL parameter should be required at all - it seems very counterintuitive. It sounds like you would need to know a MySQL server's TZ settings in order to connect to it, which would be horrible for usability 😬.

Are you sure this is correct?

Without looking into it, my intuition would be that the connection string should only override the server timezone in the context of the connection; without providing one the connection should inherit whatever timezone setting the server has. I'm not saying MySQL will always follow the inutitive approach, of course 😁.

Do you have any example code we could have a poke around with?

[knutwannheden]

This is an interesting problem. I don't think it's a bad idea to add a withUrlParam(String, String) method or similar.

I have filed a separate feature request for this: #1802.

Regarding this issue: From the documentation of the serverTimezone parameter:

Override detection/mapping of time zone. Used when time zone from server doesn't map to Java time zone

I still don't exactly know what the problem is in my case, but it is very easy to reproduce:

public class Bug1764Test {

    @ClassRule
    public static MySQLContainer<?> db = new MySQLContainer<>("mysql:8.0.17").withEnv("TZ", "Europe/Zurich");

    @Test
    public void test() {}
}

If you run this test you should notice that it looks like it is hanging and will time out after some time.

Some debugging revealed that for some reason the JDBC driver receives the time zone "CEST" from the database, which it doesn't know what to do with and ends up throwing a runtime exception at the end of the method TimeUtil#getCanonicalTimezone(String, ExceptionInterceptor). This is then wrapped into an SQLException and rethrown at Testcontainers.

I am a bit surprised that Testcontainers attempts to connect for a full 2 minutes and never logs the original exception (even with TRACE logging enabled), which would have helped to solve this problem. (Note: I did try to play with withConnectTimeoutSeconds(int).)

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If you believe this is a mistake, please reply to this comment to keep it open. If there isn't one already, a PR to fix or at least reproduce the problem in a test case will always help us get back on track to tackle this.

[stale]

This issue has been automatically closed due to inactivity. We apologise if this is still an active problem for you, and would ask you to re-open the issue if this is the case.

[knutwannheden]

@rnorth I can understand that you want to have a stale bot to close old issues. But I still think this is a bit strange in this case, since the issue has been discussed quite a bit and another user also put considerable work into a PR. Are you of the opinion that this issue should not be addressed in Testcontainers?

[rupam16]

I used almost same setup. You have to specify env for TZ (.withEnv("TZ", "Europe/Zurich")), and also override the MySqlContainer's getJDBC url to append timezone there also.

Something like this

    @Override
    public String getJdbcUrl() {
        String url = super.getJdbcUrl();
        url += ((url.contains("?") ? "&" : "?") + "serverTimezone=" + TimeZone.getDefault().getID());
        return url;
    }

[MatthewRyanRead]

Nowadays, this alone seems to work perfectly:

myContainer.withUrlParam("serverTimezone", myTimeZone);
myContainer.withEnv("TZ", myTimeZone);