MariaDB/MySQL Time Zones with Ignition

Ignition
, , ,
1

We have a project I'm working on internally that will allow me to set up servers however I would like and I'm using this as an opportunity to test and learn things at a deeper level. I know I'm super weak on time zones and their interaction with databases and Ignition/JDBC, so I'm using this to get things right and understand how time is stored in a database and then pulled through JDBC/Ignition so that they show properly to the client.

I know I can use Postgresql and much of this goes away, however, I want to make sure that I understand how things work so I can support existing and new systems that aren't so friendly to playing around.

I've got the different servers setup like this:
MariaDB running on Ubuntu. MariaDB timezone is set for System, and the OS is set for Etc/UTC.
Ignition 8.1.36 running on Windows, where the OS is set for EST.

I have a simple table to test creating new rows and inserting timestamps from different locations, and I'm not getting a consistent timestamp back.

The table is:

CREATE TABLE `tbltest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts_client_insert` timestamp NULL DEFAULT NULL,
  `ts_current_timestamp` timestamp NULL DEFAULT current_timestamp(),
  `note` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

When new records are created, the ts_current_timestamp field is generated with the current_timestamp from the MariaDB server and the time is correct according to UTC. However when reading that data into Ignition, or dBeaver, that column isn't converted from the UTC time to the time zone of client or gateway.

If I insert data from Ignition into the ts_client_insert column using the system.date.now() function, then that data is properly timezone adjusted if I change the client time zone. However, when looking at the column values, which I would assume to be the same or close they aren't.

In reading it seems that the timestamp column internally holds everything as UTC. If that is the case, then why when I insert data through JDBC that value doesn't match the current_timestamp value from the database? Do I have a configuration wrong somewhere? Is this a functional misunderstanding from my end (most likely lol)?

2

Sounds like a bug in the JDBC driver. I was under the impression that timestamp in MariaDB had similar behavior to PostgreSQL's timestamptz, while MariaDB's datetime behaved like PostgreSQL's non-tz plain timestamp.

3

I'm running MariaDB JDBC 3.0.11. I'll update to the absolutely latest and see if it changes anything.

In the mean time, I assume that with the Database in UTC and the Gateway in EST, are there any further JDBC configurations that I would need to do just to double check?

4

Does that JDBC driver offer a connection timezone override option in it connection extra properties? If so, try setting it to UTC.

5

Yep

timezone	

permits to force session timezone in case of client having a different timezone compare to server. The option `timezone` can have 3 types of value: 'disabled' (default) : connector doesn't change time_zone. '<a timezone>': connector will set connection variable to value. see timezone consideration tp know more
Default: 'disabled'. Since 3.0.0

Source: About MariaDB Connector/J - MariaDB Knowledge Base
However this seems to mean that I need to put in something like America/New_York to define that client timezone. I've tested that as well and it didn't change anything.

It also looks like Igntion might be appending serverTimezone to the connection string as well, but according to the documentation that is deprecated.
I'll try setting both and see if that changes anything.

6

Yeah... no change with the latest JDBC driver: 3.3.2

Data inserted with CURRENT_TIMESTAMP aren't being properly timezone converted up to Ignition, however data inserted by Ignition using system.date.now() is.

7

Very strange. /:

My gut says this is a MariaDB problem, not Ignition.

8

In all my years of programming, timezone handling is one of the few places that I always assume there are still more bugs to be found.

3 Likes
9

That actually sounds familiar... Like we did that deliberately to work around a bug with MySQL, I think around when they released MySQL 8.0.

10

@PGriffith
Is there a way to see the actual full JDBC connection string that is being used to connect to a database? I'd like to just verify what things look like to make sure I'm not crazy.

11

No, it doesn't look like it, but I don't think you're crazy and I'm pretty sure I'm right.

If your DB is MySQL and your connection string doesn't include serverTimezone, we're automatically including it with the Gateway's default timezone ID:

if (field.equals(DatasourceRecord.ConnectionProps)
                        && record.getDriver().getVendor().equals(DatabaseVendor.MYSQL)
                        && val != null && !val.contains(mySqlTimezoneProp)) {
12

hmmm... ok. Well then maybe there is something to this with using the Maria JDBC driver?

That is looking at this configuration parameter correct?

image
image756×132 3.4 KB

Is that something we can add a different Driver Type and then test to see if that additional parameter is making a difference? FWIW I don't think it is as I'm seeing the same issues when using a 3rd party database client, dBeaver (which uses the JDBC driver as well).

13

You can try setting the driver type to GENERIC, which will defeat that logic I just posted and ensure that your connection string goes through verbatim. I don't know off the top of my head if that might affect other particular areas where we've got manual patches for buggy behavior.

14

OK I'll take some time and test.

I do think that something might be amiss with my understanding of the timezone configurations in Ignition or somewhere along the line.

Something else I've noticed...
If I use dBeaver, which uses the MariaJDBC driver, to insert and view the data in my test table, I'm getting the correct timezone applied data for all the data EXCEPT for the data inserted by Ignition. I'm able to add the timezone=America/New_York and I get the data, current_timestamp, entered data etc... all in that time zone. When I change the timezone to America/Chicago, the timezone is applied and the data is shown as proper time in that time zone.

It looks like maybe Ignition is ignoring the timezone=America/New_York as part of the connection properties.

This is what I'm specifically using in the extra connection parameters for this datasource:
connectTimeout=120000;socketTimeout=120000;timezone=America/New_York

I've tried GENERIC and MySQL types and neither of them are changing the behavior in Ignition.

So the more that I'm playing around with this I'm wondering if there is something in Ignition that I'm not understanding, or isn't quite right.

Edit:
I've also taken a look at the @@session variables as well between dBeaver and Ignition. In dBeaver if I query the @@session.time_zone variable, select @@session.time_zone;, I get the same time zone as specified in the JDBC connection property timezone. However, when I run the same query in Ignition I get SYSTEM rather than the supplied timezone. So something funky is going on.

15

Update.

After talking with support and doing some testing, this is what I've been able to get working.

MariaDB Database in UTC mode and set to the correct time.
Ignition Gateway in a time zone other than UTC, in this case, it is in America/New_York or EST.

With MariaDB JDBC Driver version 2.7.11 I was able to get the timezone to offset by using these extra connection parameters:

connectTimeout=120000;socketTimeout=120000;rewriteBatchedStatements=true;serverTimezone=UTC;useLegacyDatetimeCode=false

In addition, I was able to keep the Driver Type as "MYSQL":

image

With MariaDB JDBC Driver version in the 3.X.X version specifically 3.3.2 I was able to do the following to get things to work.

Change the driver type to "Generic":
image

Then add/change the extra connection parameters to look like:

connectTimeout=120000;socketTimeout=120000;rewriteBatchedStatements=true;timezone=America/New_York

Additionally, before I changed the driver type to Generic for the 3.x versions, it seems that Ignition is setting parameters on the JDBC connection that aren't valid. I would get this exception when trying to query the database using a system.db.XXX function. Named queries and query bindings seemed to be operating ok.

Exception shortened, I can post the entire stack if anyone is interested.

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "SELECT * from tblemp": (conn=211) invalid fetch size
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:334)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:893)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:859)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runQuery(ClientDBUtilities.java:336)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:350)
	... 24 more

At the end of the day, it looks like the newer/more recent MariaDB JDBC drivers and Ignition don't want to play well. I can do what I need to do with the older version of the JDBC driver. However, I think that IA might want to work on bringing compatibility with the newer MariaDB JDBC drivers and Ignition.

4 Likes