You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "Jarek Potiuk (Jira)" <ji...@apache.org> on 2020/03/07 14:14:01 UTC
[jira] [Resolved] (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:all-tabpanel ]
Jarek Potiuk resolved AIRFLOW-7001.
-----------------------------------
Fix Version/s: 1.10.10
Resolution: Fixed
> 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)