Jarek Potiuk created AIRFLOW-7001:
-------------------------------------
Summary: 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: 1.10.9, 2.0.0
Reporter: Jarek Potiuk
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)
