[CONJ-990] Setting timezone=UTC result in SQLSyntaxErrorException Created: 2022-07-08  Updated: 2022-07-25  Resolved: 2022-07-25

Status: Closed
Project: MariaDB Connector/J
Component/s: JDBC compatibility
Affects Version/s: 3.0.5
Fix Version/s: 3.0.7

Type: Bug Priority: Major
Reporter: Michael Simons Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: UTC, timezones


 Description   

Given a server that does not run in UTC, the new `timezone` parameter described here https://mariadb.com/kb/en/about-mariadb-connector-j/#jdbc-api-implementation-notes cannot work, even with mysql_tzinfo_to_sql fully and correctly loaded.

In `StandardClient` UTC get's translated into a fixed offset:

if (mustSetTimezone) {
        if (clientZoneId.getRules().isFixedOffset()) {
          ZoneOffset zoneOffset = clientZoneId.getRules().getOffset(Instant.now());
          sessionCommands.add("time_zone='" + zoneOffset.getId() + "'");
        } else {
          sessionCommands.add("time_zone='" + conf.timezone() + "'");
        }
      }

Which is literal `Z` (See `System.out.println(ZoneId.of("UTC").getRules().getOffset(Instant.now()));`}

MariaDB server cannot handle this, resulting in

Exception in thread "main" java.sql.SQLSyntaxErrorException: (conn=2235) Setting configured timezone 'UTC' fail on server.
Look at https://mariadb.com/kb/en/mysql_tzinfo_to_sql/ to load tz data on server, or set timezone=disable to disable setting client timezone.
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:280)
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:334)
	at org.mariadb.jdbc.client.impl.StandardClient.postConnectionQueries(StandardClient.java:369)
	at org.mariadb.jdbc.client.impl.StandardClient.<init>(StandardClient.java:205)
	at org.mariadb.jdbc.Driver.connect(Driver.java:70)
	at org.mariadb.jdbc.Driver.connect(Driver.java:101)
	at org.mariadb.jdbc.Driver.connect(Driver.java:27)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:229)



 Comments   
Comment by Diego Dupin [ 2022-07-25 ]

right, strangely, this went unnoticed

Comment by Diego Dupin [ 2022-07-25 ]

corrected with https://github.com/mariadb-corporation/mariadb-connector-j/commit/fec07087915baa9bce5c0b2d5b64215397e6ebce

Comment by Michael Simons [ 2022-07-25 ]

Thanks, Diego, for looking into and fixing this.

And no worries, stuff happens all the time.

Generated at Thu Feb 08 03:19:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.