Uploaded image for project: 'MariaDB Connector/ODBC'
  1. MariaDB Connector/ODBC
  2. ODBC-417

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 3.1.14
    • 3.1.15
    • General
    • 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
          Michael Walloch
        2. log_prepared.trace
          95 kB
          Michael Walloch
        3. replicate.cpp
          22 kB
          Michael Walloch
        4. replicate-linux.cpp
          22 kB
          Alexander Barkov

        Issue Links

          Activity

            People

              Lawrin Lawrin Novitsky
              mwax Michael Walloch (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.