You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "ASF GitHub Bot (Jira)" <ji...@apache.org> on 2020/03/07 09:41:00 UTC
[jira] [Commented] (AIRFLOW-7001) Mysql 5.7 handles timezone-aware
timestamps differently than 5.6
[ https://issues.apache.org/jira/browse/AIRFLOW-7001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17053960#comment-17053960 ]
ASF GitHub Bot commented on AIRFLOW-7001:
-----------------------------------------
potiuk commented on pull request #7641: [AIRFLOW-7001] Time zone removed from MySQL TIMSTAMP field inserts
URL: https://github.com/apache/airflow/pull/7641
For mysql we should store timestamps as naive values.
Timestamp in MYSQL is not timezone aware. In MySQL 5.6
timezone added at the end is ignored but in MySQL 5.7
inserting timezone value fails with 'invalid-date'
---
Issue link: WILL BE INSERTED BY [boring-cyborg](https://github.com/kaxil/boring-cyborg)
Make sure to mark the boxes below before creating PR: [x]
- [x] Description above provides context of the change
- [x] Commit message/PR title starts with `[AIRFLOW-NNNN]`. AIRFLOW-NNNN = JIRA ID<sup>*</sup>
- [x] Unit tests coverage for changes (not needed for documentation changes)
- [x] Commits follow "[How to write a good git commit message](http://chris.beams.io/posts/git-commit/)"
- [x] Relevant documentation is updated including usage instructions.
- [x] I will engage committers as explained in [Contribution Workflow Example](https://github.com/apache/airflow/blob/master/CONTRIBUTING.rst#contribution-workflow-example).
<sup>*</sup> For document-only changes commit message can start with `[AIRFLOW-XXXX]`.
---
In case of fundamental code change, Airflow Improvement Proposal ([AIP](https://cwiki.apache.org/confluence/display/AIRFLOW/Airflow+Improvements+Proposals)) is needed.
In case of a new dependency, check compliance with the [ASF 3rd Party License Policy](https://www.apache.org/legal/resolved.html#category-x).
In case of backwards incompatible changes please leave a note in [UPDATING.md](https://github.com/apache/airflow/blob/master/UPDATING.md).
Read the [Pull Request Guidelines](https://github.com/apache/airflow/blob/master/CONTRIBUTING.rst#pull-request-guidelines) for more information.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
> Mysql 5.7 handles timezone-aware timestamps differently than 5.6
> ----------------------------------------------------------------
>
> Key: AIRFLOW-7001
> URL: https://issues.apache.org/jira/browse/AIRFLOW-7001
> Project: Apache Airflow
> Issue Type: Improvement
> Components: database, mysql
> Affects Versions: 2.0.0, 1.10.9
> Reporter: Jarek Potiuk
> Priority: Major
>
> In Airflow when UtcDateTime is used, always Timezone is required and added if missing.
> For example when utcnow() function is used to get timezone we get the timestamp in the form of '2020-03-07 07:32:34.121705+00:00'
> When such value - with timezone - is used in MySQL 5.6 the timezone part is IGNORED:
>
> {code:java}
> mysql> create table test (a timestamp(6));
> Query OK, 0 rows affected (0.01 sec)
> mysql> insert into test values ('2020-03-07 07:32:34.121705+00:00');
> Query OK, 1 row affected, 1 warning (0.00 sec)
> mysql> insert into test values ('2020-03-07 07:32:34.121705+01:00');
> Query OK, 1 row affected, 1 warning (0.00 sec)
> mysql> select * from test;
> +----------------------------+
> | a |
> +----------------------------+
> | 2020-03-07 07:32:34.121705 |
> | 2020-03-07 07:32:34.121705 |
> +----------------------------+
> 2 rows in set (0.00 sec)
> mysql> SHOW VARIABLES LIKE "%version%";
> +-------------------------+------------------------------+
> | Variable_name | Value |
> +-------------------------+------------------------------+
> | innodb_version | 5.6.47 |
> | protocol_version | 10 |
> | slave_type_conversions | |
> | version | 5.6.47 |
> | version_comment | MySQL Community Server (GPL) |
> | version_compile_machine | x86_64 |
> | version_compile_os | Linux |
> +-------------------------+------------------------------+
> 7 rows in set (0.00 sec)
> {code}
>
> The same insert in 5.7 results in error:
>
> {code:java}
> mysql> create table test(a TIMESTAMP(6));
> Query OK, 0 rows affected (0.00 sec)
> mysql> insert into test values ('2020-03-07 07:32:34.121705+01:00');
> ERROR 1292 (22007): Incorrect datetime value: '2020-03-07 07:32:34.121705+01:00' for column 'a' at row 1
> mysql> SHOW VARIABLES LIKE "%version%";
> +-------------------------+------------------------------+
> | Variable_name | Value |
> +-------------------------+------------------------------+
> | innodb_version | 5.7.29 |
> | protocol_version | 10 |
> | slave_type_conversions | |
> | tls_version | TLSv1,TLSv1.1,TLSv1.2 |
> | version | 5.7.29 |
> | version_comment | MySQL Community Server (GPL) |
> | version_compile_machine | x86_64 |
> | version_compile_os | Linux |
> +-------------------------+------------------------------+
> 8 rows in set (0.00 sec)
> {code}
> Seems that for MySQL - neither 5.6 (ignore timezone) nor 5.7 (crashes) works properly.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)