Uploaded image for project: 'Apache Airflow'
  1. Apache Airflow
  2. AIRFLOW-7001

Mysql 5.7 handles timezone-aware timestamps differently than 5.6

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0, 1.10.9
    • Fix Version/s: 1.10.10
    • Component/s: database, mysql
    • Labels:
      None

      Description

      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:

       

      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) 
      

       

      The same insert in 5.7 results in error:

       

      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)
      

      Seems that for MySQL - neither 5.6 (ignore timezone) nor 5.7 (crashes) works properly.

       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              potiuk Jarek Potiuk
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: