[
https://issues.apache.org/jira/browse/NIFI-8749?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17397397#comment-17397397
]
David Handermann commented on NIFI-8749:
----------------------------------------
I was able to reproduce this issue on the current main branch using the
attached Flow Definition connected to MySQL 8.0.26 running in Docker.
The following Docker command can be used to start a MySQL container:
{noformat}
docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root-password -d
mysql:8.0.26
{noformat}
The following commands can be run using the MySQL CLI to create and database
and simple table with a {{DATETIME}} column:
{noformat}
CREATE DATABASE nifi;
USE nifi;
CREATE TABLE record (id INT PRIMARY KEY, created DATETIME NOT NULL);
{noformat}
The attached Flow Definition includes a JSON record with a {{created}} value of
{{2021-01-01 12:00:00}}. When inserted into the MySQL table, the {{created}}
column shows the number of hours adjusted based on the time zone offset from
GMT of the operating system running NiFi.
Note that running the attached Flow Definition requires placing
{{mysql-connector-java-8.0.26.jar}} in {{/tmp}}.
The core problem relates to {{DataTypeUtils.getDateFormat()}} setting a default
Time Zone of GMT on the {{SimpleDateFormat}} used for parsing. Removing this
Time Zone override should resolve the problem, but would have other unintended
consequences. Still researching the best approach to a solution as this impacts
other components in addition to {{PutDatabaseRecord}}.
[^NIFI-8749.json]
> PutDatabaseRecord Insert Datetime Type Time Zone Issue
> ------------------------------------------------------
>
> Key: NIFI-8749
> URL: https://issues.apache.org/jira/browse/NIFI-8749
> Project: Apache NiFi
> Issue Type: Bug
> Affects Versions: 1.13.2
> Reporter: Billy Lin
> Priority: Critical
> Attachments: NIFI-8749.json, cc1.png, cc2.png, cc3.png, cc4.png,
> image-2021-07-01-15-17-38-035.png, image-2021-07-30-17-35-58-914.png
>
>
> when I query a record from Mysql to get an AVRO Flowfile, then use
> PutDatabaseRecord(version 1.13.2) insert to another table (with same table
> structure), the data of the 'datetime' type column will change. The original
> data of the first table I query from is '2021-06-28
> {color:#FF0000}13{color}:14:20.0'. and the data of the second table which I
> insert to becomes '2021-06-28 {color:#FF0000}06{color}:14:20.0'.The gap is
> {color:#FF0000}7 hours{color}.
> our local timezone is PDT ({color:#FF0000}-7:00{color}). It seems that Nifi
> has done some timezone conversion work. But the 'datetime' type in mysql
> should have no relation to timezone
>
> when we use version 1.7.1 ,it works fine.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
