[MDEV-22843] Year 2038 issue executing prepared UPDATE statement via ODBC when binding timestamps Created: 2020-06-09  Updated: 2023-08-14

Status: Open
Project: MariaDB Server
Component/s: Data types, Temporal Types
Affects Version/s: 10.4.1, 10.4.12, 10.5.3, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Michael Walloch (Inactive) Assignee: Lawrin Novitsky
Resolution: Unresolved Votes: 0
Labels: datatype, regression
Environment:

OS: Windows 10, CentOS 6, CentOS 7, Ubuntu 18.04 , Windows 2012
ODBC Connector 3.0 & 3.1.7

Attachments: File log_direct.trace     File log_prepared.trace     File replicate-linux.cpp     Text File replicate.cpp    
Issue Links:
Relates
relates to MDEV-13995 MAX(timestamp) returns a wrong result... Closed

 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|



 Comments   
Comment by Alexander Barkov [ 2023-07-21 ]

Repeated as described, the UPDATE statement in the prepared execution:

  • sets the column to a non-NULL value in 5.5, 10.1, 10.2, 10.3
  • sets the column to NULL starting from 10.4.

I had to modify the program to run it on Linux. Please find replicate-linux.cpp attached to this issue.

Comment by Alexander Barkov [ 2023-07-21 ]

The problem is that the ODBC driver binds TIMESTAMP_STRUCT (type code SQL_C_TYPE_TIMESTAMP) as MYSQL_TYPE_TIMESTAMP.

It should bind as MYSQL_TYPE_DATETIME instead.

Prior to 10.4 it did not make any difference. In 10.4 we fixed numerous bugs related TIMESTAMP behavior near DST (Daylight saving time) points. So now TIMESTAMP values walk through Items using a native representation (a binary packed "struct timeval" value) rather than MYSQL_TIME representation. The value used in this issue is outside of MariaDB TIMESTAMP supported range, hence the application sets the column to NULL.

This problem should be fixed on the ODBC driver side.

Comment by Georg Richter [ 2023-07-21 ]

I couldn't reproduce this issue in Connector/C (using binary protocol).

Comment by Lawrin Novitsky [ 2023-07-23 ]

This is fixed few versions ago(since 3.1.15 by ODBC-345). I'd say the problem is only to update C/ODBC

Comment by Georg Richter [ 2023-07-23 ]

bar Why does 10.4 set the correct timestamp value for column b, while 11.0 sets it to NULL ?

MariaDB 10.4.31

MariaDB [test]> UPDATE table_name SET a = TIMESTAMP'2039-12-26 00:00:00' WHERE (id = 1);
 
Query OK, 1 row affected (0.001 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [test]> select * from table_name;
 
+------+-------------------------+-------------------------+
 
| id   | a                       | b                       |
 
+------+-------------------------+-------------------------+
 
|    1 | 2039-12-26 00:00:00.000 | 2023-07-23 17:11:41.201 |
 
+------+-------------------------+-------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select version();
 
+---------------------+
 
| version()           |
 
+---------------------+
 
| 10.4.31-MariaDB-log |
 
+---------------------+
 
1 row in set (0.000 sec)

MariaDB 11.0.2

MariaDB [test]> UPDATE table_name SET a = TIMESTAMP'2039-12-26 00:00:00' WHERE (id = 1);
 
Query OK, 1 row affected (0.037 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [test]> select * from table_name;
 
+------+-------------------------+------+
 
| id   | a                       | b    |
 
+------+-------------------------+------+
 
|    1 | 2039-12-26 00:00:00.000 | NULL |
 
+------+-------------------------+------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select version();
 
+----------------+
 
| version()      |
 
+----------------+
 
| 11.0.2-MariaDB |
 
+----------------+
 
1 row in set (0.000 sec)

Comment by Alexander Barkov [ 2023-08-14 ]

georg, there's possibly something wrong with your examples.
Your UPDATE statement modifies the column 'a' but you ask about NULL in the column 'b'.
Can you please provide the entire script demonstrating that the behavior is different in different versions.

Comment by Georg Richter [ 2023-08-14 ]

bar Just noticed that this has changed since 10.10 - The behavior of TIMESTAMP depends on (deprecated) variable explicit_defaults_for_timestamp

Generated at Thu Feb 08 09:17:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.