Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22843

Year 2038 issue executing prepared UPDATE statement via ODBC when binding timestamps

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.1, 10.4.12, 10.5, 10.5.3
    • Fix Version/s: 10.4, 10.5
    • Component/s: Data types, Temporal Types
    • Environment:
      OS: Windows 10, CentOS 6, CentOS 7, Ubuntu 18.04 , Windows 2012
      ODBC Connector 3.0 & 3.1.7

      Description

      We were upgrading to 10.4.12 recently and found an year 2038 issue in the integration tests.
      This is a regression from 10.4.0 which is known good.
      First, let me state that I already bisected the commit that introduced the changed behaviour which is:
      https://github.com/MariaDB/server/commit/34eb98387f
      MDEV-13995
      Probably the best to look at this would be @"Alexander Barkov"

      I changed a MSDN example to do the following
      Steps to reproduce:
      Setup

      CREATE DATABASE IF NOT EXISTS `test` CHARACTER SET utf8 COLLATE utf8_bin;
      USE test;
       
      CREATE OR REPLACE TABLE table_name (id INTEGER, a DateTime(3), b Timestamp(3));
      INSERT INTO table_name (id,a,b) VALUES(1, NULL, NULL);
      

      This is working (log_direct.trace is the corresponding dbuglog):

      UPDATE table_name SET a = TIMESTAMP'2039-12-26 00:00:00' WHERE (id = 1)
      

      Result:

      id|a                  |b                  |
      --|-------------------|-------------------|
       1|2039-12-26 00:00:00|2020-06-10 07:12:11|
      

      This is will set NULL for timestamps affected by the year 2038 issue ( log_prepared.trace is the corresponding dbug log)
      (pseudocode, the complete code will be in the reproducer):

      SQLPrepare(hStmt, "UPDATE table_name SET a = ? WHERE (id = ?)")
      timestamp.year = 2039;  
      timestamp.month = 12;  
      timestamp.day = 26;  
      timestamp.hour = 0;  
      timestamp.minute = 0;  
      timestamp.second = 0;  
      timestamp.fraction = 100; 
      SQLLEN timestamp_len = sizeof(timestamp);
      SQLBindParameter(hStmt, ++paramNum, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 27, 0, &timestamp, 0, &timestamp_len);
       
      SQLINTEGER sqlint = 1;
      SQLLEN int_len = sizeof(sqlint);
      SQLBindParameter(hStmt, ++paramNum, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &sqlint, 0, &int_len);
       
      SQLExecute(hStmt)
      

      Result:

      | id          | a                       | b                        |
      |           1 | <NULL>                  | 2020-06-09 13:28:17.098  |
      

      If you want to use the reproducer program, it can be compiled with
      cl /EHsc /DEBUG:FULL /DUNICODE /D_UNICODE user32.lib odbc32.lib /link /SUBSYSTEM:CONSOLE replicate.cpp
      and run with
      replicate.exe "DSN=DebugMDB; Uid=root; Pwd="

      I wanted to write a test but could not reproduce this with prepared SQL statments like so:

      CREATE DATABASE IF NOT EXISTS `test` CHARACTER SET utf8 COLLATE utf8_bin;
      USE test;
       
      CREATE OR REPLACE TABLE t1 (id INTEGER, a DateTime, b Timestamp);
      INSERT INTO t1 (id,a,b) VALUES(1, NULL, NULL);
      -- SET SESSION debug_dbug = 'd,enter,exit,error,warning,info,loop:t:F:L:O,data/log.trace';
      PREPARE stmt FROM 'UPDATE t1 SET a = ? WHERE (id = ?)';
      set @a=TIMESTAMP'2039-12-26 00:00:00', @b=1;
      execute stmt using @a,@b;
      -- SET SESSION debug_dbug = '';
      SELECT * FROM t1;
      DROP TABLE t1;
      DEALLOCATE PREPARE stmt;
      

      Result:

      id|a                  |b                  |
      --|-------------------|-------------------|
       1|2039-12-26 00:00:00|2020-06-10 07:09:16|
      

        Attachments

        1. log_direct.trace
          64 kB
        2. log_prepared.trace
          95 kB
        3. replicate.cpp
          22 kB

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              mwax Michael Walloch
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: