[
https://issues.apache.org/jira/browse/AIRFLOW-7001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17054118#comment-17054118
]
ASF subversion and git services commented on AIRFLOW-7001:
----------------------------------------------------------
Commit 3a7931eab1c6424c5d0c0837fbc9228bf2c5992e in airflow's branch
refs/heads/v1-10-test from Jarek Potiuk
[ https://gitbox.apache.org/repos/asf?p=airflow.git;h=3a7931e ]
[AIRFLOW-7001] Time zone removed from MySQL TIMSTAMP field inserts
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'
(cherry picked from commit e7d65661ba569b148cd3d560a00883392935b01d)
> 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
> Fix For: 1.10.10
>
>
> 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)
