首页 > 其他分享 >ODBC批量merge中出现主键冲突的分析

ODBC批量merge中出现主键冲突的分析

时间：2024-04-01 11:56:17浏览次数：18  
标签：price ODBC 数据库 merge SQL 主键 精度 matched

ODBC 批量 merge 中出现主键冲突的分析
一、 文档概括
客户某个 merge 语句运行时，发生主键冲突报错。

经分析，其原因如下：

由于 merge 语句中，ON 里的判断条件（谓词）中存在带精度定义的数字字段，在绑定变量传递过程中，驱动将数值高精度数字传过去，而数据库内的数据已经做了精度限制，导致在符合条件的数据存在的情况下，ON 条件错误地判断为 false；数据库尝试进行插入，插入时，自动转换成限制精度的数字，此时，和数据库内已有的数据发生冲突。

该问题除了 merge 会发生，其他同时满足以下数个条件的 ODBC 程序均有可能出现谓词条件判断错误的问题。

数字类型字段在数据库中限定了精度
程序中使用绑定变量中将其和该字段进行比较
该变量小数部分不为 0
绑定变量未使用 SQL_NUMERIC_STRUCT 或者 SQL_CHAR 类型
包括:

Select … from … where col_with_precision = ?
Update … set … where col_with_precision = ?
Delete from … where col_with_precision = ?
Merge into .. on (col_with_precision = ?) …
对于作为更新数据的 insert/update, 则由于数据库侧会进行精度裁剪，不会出现问题。

如 insert into .. (col_with_precision)values(?);

但，无法从内核层面把col_with_precision = ?后面的数值进行强行的降精度后对比，不符合常规逻辑。

因此，此问题不适合定义为内核的 bug, 而是属于使用 ODBC 程序时，涉及带精度带小数的数字使用上的一个注意事项。

解决方法：

标准用法 A

调整 SQL, 在 SQL 层面指定准确的数据类型，如：

merge INTO Parts using dual on partid = $1::numeric(10,2)
when matched then update set Price = $2
when not matched then insert values($3,$4)
标准用法 B

ODBC 绑定变量时使用 SQL_NUMERIC_STRUCT，保证精度信息正确传到数据库，其中精度部分使用数据库指定的精度或者数字本身的精度。

绕过的用法 A：

​ ODBC 绑定变量时使用 SQL_CHAR，转成 SQL_CHAR 时使用数据库指定的精度或者数字本身的精度

绕过的用法 B：

在 SQL 中绑定变量部分添加 round 函数, 类似于 round( ? , <精度> )，其中精度部分使用数据库指定的精度或者数字本身的精度。

merge INTO Parts using dual on partid = round ($1,2)
when matched then update set Price = $2
when not matched then insert values ($3, $4)
二、问题描述
某个 merge 语句运行时，发生主键冲突报错。

三、问题分析过程
为了保密，所有 SQL 语句、表结构、数据均作了替换：

3.1 报错信息
客户 C 程序通过 ODBC 驱动连接数据库，进行 merge 操作时，报了以下错误：

Duplicate key violentes unique constraint PK_tab
该错误代表发生主键冲突

3.2 SQL 及表结构
SQL 如下：

Merge Into test_tab using dual
On (id = $1 and price = $2)
when match then update set qty = $3
When not matched insert (id,price, qty) values($4,$5,$6);
其中传入变量 $1/$4, $2/$5, $3/$6 两两相同。

Id,qty 为整数，price 为带小数的数字。

对应表结构如下：

列：Id int, price(numeric(10,2)), qty (int)

主键：(id,price)

3.3 Merge 语句逻辑分析
Merge 语句是一个结合 update 和 insert 的 SQL 语句，其含义是，如果判断条件为 true,也就是数据存在， 则执行 update 部分，如果条件为 false, 则执行 insert 部分。

该语句中，判断条件的部分，正好和主键一致，正常情况下，主键对应数据存在，则进行更新，不存在才会 insert, 因此，正常情况下,merge 语句不会出现主键冲突的问题。

3.4 计算机处理带小数的精度问题
计算机内部使用二进制来代表数字，而日常中使用十进制来代表数字。由于十进制数字的小数点后部分转换成二进制，有一定可能出现除不尽的情况。因此，有时候会出现输入浮点数和实际保存浮点数（double/real/float 等类型）不一致的情况。

因此，怀疑本问题是传递给数据库的 price 变量中出现了类似的问题，导致虽然数据库中实际存在，但 merge 判断条件时却不一致的情况。

3.5 逻辑推测
如这个语句：

Merge Into test_tab using dual
On (id = $1 and price = $2)
when match then update set qty = $3
When not matched insert (id,price, qty) values($4,$5,$6);
假设：

表里面存在一条数据：

Id=1, price=4.30, cnt=90
当程序尝试 merge (Id=1, price=4.30, cnt=100)时，给驱动传入以下变量

$1/$4=1, $2/$5=4.30, $3/$6=100
程序内部保存$2/$5 的 4.30 数据，并不是 4.30,而是可能是类似于 4.300000001。

这时候，数据库收到的语句就类似于：

Merge Into test_tab using dual
On (id = 1 and price = 4.300000001)
when match then update set qty = 100
When not matched insert (id, price, qty) values (1, 4.300000001, 100);
数据库引擎首先对 ON 条件进行判断

On (id = 1 and price = 4.300000001)
由于表里存的是 1 和 4.30, 因此，这个表达式返回 false.

然后，数据库引擎根据 merge 的定义，决定进行 insert 操作

Insert into test_tab (id, price, qty) values (1, 4.300000001, 100);
而由于数据库中定义的 price 精度为 2，在插入的过程中，就会把 price 精度进行截断，等同于：

Insert into test_tab (id, price, qty) values (1, 4.30,100);
最终因为(id, price)为(1, 4.30)的记录已经存在，而报了主键冲突的错误。

3.6 SQL 模拟
尝试用纯 SQL 来验证这种情况：

初始化表结构及数据：

Create table test_tab (Id int, price numeric (10,2), qty int);
Alter table test_tab add constraint pk_test_tab primary key (id, price);
Insert into test_tab values (1,4.3,100);
尝试 merge 语句，其中 price 字段传入超出表定义中数据的精度。

Merge Into test_tab using dual
On (id = 1 and price = 4.300000001)
when matched then
update set qty = 100
When not matched then
insert (id, price, qty) values (1, 4.300000001,100);

ERROR: duplicate key value violates unique constraint "pk_test_tab"
DETAIL: Key (id, price) = (1, 4.30) already exists.
的确会报错

3.7 ODBC 官方文档查证
检查了 ODBC 的文档，当进行绑定变量时，如果使用 double/real/float 等类型，而不是 SQL_NUMERIC 或者 SQL_DECIMAL，精度信息并不会被使用。

因此，的确可能产生上述推测的问题。

程序做 Bind（用 double/float/real 类型 4.30，设精度 2）

驱动接收参数（精度信息丢失，double 二进制类型不精确,4.30 => 4.30000001）

驱动把参数 convert 成字符串（精度发生改变 4.30000001）

驱动把字符串传给数据库进程(数据库收到 4.30000001)

3.8 程序模拟
根据以上推测，写了个 C/ODBC 的程序，进行了一次模拟，模拟结果显示，如果是主键中使用 double/real/float 等类型，且使用了小数点后不为 0 的数据，merge 语句在值已经存在时，的确有一定几率会导致主键冲突。

include <sql.h>

include <sqlext.h>

include <sqltypes.h>

include

include

define DESC_LEN 51

define RC_SUCCESSFUL(rc) ((rc)SQL_SUCCESS||(rc)SQL_SUCCESS_WITH_INFO)

SQLRETURN rc;


SQLCHAR err_info[100];
SQLCHAR state[6];
SQLINTEGER NativeError;
SQLSMALLINT err_len;


SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;

void get_dmesg(SQLSMALLINT HandleType, SQLHANDLE Handle, const char sourceSQL)
{
std::cerr << "** get_dmesg from pid:" << pthread_self() << " handle: " << Handle << " point:" << sourceSQL << std::endl;

SQLRETURN rc3 = SQLGetDiagRec(HandleType, Handle, 1, state, &NativeError, err_info, sizeof(err_info), &err_len);
//add log
std::cerr << "*** get dmesg return " << pthread_self() << " " << rc3 << std::endl;

if (RC_SUCCESSFUL(rc3))
{
    std::cerr << "*** get dmesg ErrInfo:" << pthread_self() << " " << err_info << std::endl;
    std::cerr << "*** get dmesg SQLState:" << pthread_self() << " " << state << std::endl;
}
else
{
    std::cerr << "*** get dmesg SQLGetDiagRec failed, SQLSTATE:" << pthread_self() << " " << state << std::endl;
}
// if (HandleType == SQL_HANDLE_STMT)
// {
//     cerr << "*** get dmesg Free handle, handle  " << pthread_self() << " " << std::endl;
//     for (unsigned int i = 0; i <= SQL_MAX_LEN; i++)
//     {
//         if (STMT_ARRAY[i])
//         {
//             SQLFreeHandle(SQL_HANDLE_STMT, STMT_ARRAY[i]);
//             STMT_ARRAY[i] = nullptr;
//         }
//     }
//     reconnect();

}

int main(int argc, char* argv[]){
SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
if (henv == NULL)
{
std::cerr << "ERROR: ODBC ENV Handle Alloc failed " << std::endl;
return -1;
}
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER);

SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (hdbc == NULL)
{
    std::cerr << "ERROR: ODBC DBC Handle Alloc failed " << std::endl;
    return -1;
}
char szConnectStr[100]="pg";

rc = SQLConnect(hdbc, (SQLCHAR *)szConnectStr, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
if (RC_SUCCESSFUL(rc))
{
    std::cerr << "DBConnection sucess" << std::endl;
    SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    if (hstmt == NULL)
    {
        std::cerr << "ERROR: ODBC STMT Handle Alloc failed " << std::endl;
    }
}
else
{
    get_dmesg(SQL_HANDLE_DBC, hdbc, "CDBConnection::connect");
}

define ARRAY_SIZE 1

SQLCHAR * Statement = (SQLCHAR) "merge INTO Parts using dual on partid = ? when matched then update set Price = ? when not matched then insert values(?,?) ";
//SQLCHAR * Statement = (SQLCHAR) "merge INTO Parts using dual on partid = round(?,2) when matched then update set Price = ? when not matched then insert values(?,?) ";
//SQLCHAR * Statement = (SQLCHAR*) "INSERT INTO Parts (PartID, Price) VALUES (?, ?)";
SQLREAL PartIDArray[ARRAY_SIZE];
SQLREAL DescArray[ARRAY_SIZE][DESC_LEN];
SQLREAL PriceArray[ARRAY_SIZE];
SQLLEN PartIDIndArray[ARRAY_SIZE], PartIDIndArray2[ARRAY_SIZE], PriceIndArray[ARRAY_SIZE] , PriceIndArray2[ARRAY_SIZE];
SQLUSMALLINT i, ParamStatusArray[ARRAY_SIZE];
SQLULEN ParamsProcessed;

std::memset(PartIDIndArray, 0, sizeof(PartIDIndArray));
std::memset(PriceIndArray, 0, sizeof(PriceIndArray));

// Set the SQL_ATTR_PARAM_BIND_TYPE statement attribute to use
// column-wise binding.
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, (SQLINTEGER)0);

// Specify the number of elements in each parameter array.
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)ARRAY_SIZE, SQL_NTS);

// Specify an array in which to return the status of each set of
// parameters.
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, (SQLINTEGER)0);

// Specify an SQLUINTEGER value in which to return the number of sets of
// parameters processed.
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &ParamsProcessed, (SQLINTEGER)0);

// Bind the parameters in column-wise fashion.
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, 12, 2, PartIDArray, 0, PartIDIndArray);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, 12, 2, PriceArray, 0, PriceIndArray);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, 12, 2, PartIDArray, 0, PartIDIndArray);
SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, 12, 2, PriceArray, 0, PriceIndArray);

// Set part ID, description, and price.
for (i = 0; i < ARRAY_SIZE; i++) {
//GetNewValues(&PartIDArray[i], DescArray[i], &PriceArray[i]);
PartIDArray[i]=4.30 ;
PriceArray[i]=4.30 ;
PartIDIndArray[i] = 0;
PriceIndArray[i] = 0;
std::cerr << PartIDArray[i] <<std::endl;
}

// Execute the statement.
SQLExecDirect(hstmt, Statement, SQL_NTS);

// Check to see which sets of parameters were processed successfully.
for (i = 0; i < ParamsProcessed; i++) {
printf("Parameter Set Status\n");
printf("------------- -------------\n");
switch (ParamStatusArray[i]) {
case SQL_PARAM_SUCCESS:
case SQL_PARAM_SUCCESS_WITH_INFO:
printf("%13d Success\n", i);
break;

  case SQL_PARAM_ERROR:
     printf("%13d  Error\n", i);
     break;

  case SQL_PARAM_UNUSED:
     printf("%13d  Not processed\n", i);
     break;

  case SQL_PARAM_DIAG_UNAVAILABLE:
     printf("%13d  Unknown\n", i);
     break;

}
}

}
运行前先在数据库创建对应用户密码，在/etc/odbc.ini中设置新的 dsn. 并创建表和主键：

create table Parts (PartID numeric(10,2) primary key, Description varchar(100), Price numeric(10,2) );
然后 编译:

g++ -lodbc -o mergeTest a.cpp
运行：

./mergeTest
第一遍会成功，因为表内没有数据，运行第二遍会失败。

篇幅原因，不对程序进行详细拆解

且在结果，可以看到虽然传的是两位小数点，但数据库端却收到更高精度的数据：

如 4.3000002 等

用 strace 追踪客户端和数据库端的数据传输，也有同样发现：

传入 123456.01 时，被转换成 123456.00999999999

3.9 问题延伸
根据推测及模拟，merge 语句出现的这个问题，是一个特定的错误，正好因为主键冲突暴露了出来。而实际上，问题的核心在于 ODBC 绑定中，数据库精度和程序类型精度不一致才是关键。

其他同时满足以下数个条件的 ODBC 程序均有可能出现类似的问题。

数字类型字段在数据库中限定了精度
程序中使用绑定变量中将其和该字段进行比较
该变量小数部分不为 0
绑定变量未使用 SQL_NUMERIC_STRUCT 或者 SQL_CHAR 类型
包括:

Select … from … where col_with_precision = ?
Update … set … where col_with_precision = ?
Delete from … where col_with_precision = ?
Merge into .. on (col_with_precision = ?) when not matched …
四、解决方案建议
根据以上的分析和模拟，使用 double/real/float 等类型，的确会出现类似问题，其本质是 ODBC 进行绑定变量的时候，精度信息未能正确传递。

为了避免这类问题，需要对程序中出现类似状况的代码进行改造：

标准用法 A

调整 SQL, 在 SQL 层面指定准确的数据类型，如：

merge INTO Parts using dual on partid = $1::numeric(10,2)
when matched then update set Price = $2
when not matched then insert values($3,$4)
标准用法 B

ODBC 绑定变量时使用 SQL_NUMERIC_STRUCT，保证精度信息正确传到数据库，其中精度部分使用数据库指定的精度或者数字本身的精度。

用数据库指定的精度或者数字本身的精度。

/*

  • Convert a string representation of a numeric into SQL_NUMERIC_STRUCT.
    */
    static void
    parse_to_numeric_struct(const char *wv, SQL_NUMERIC_STRUCT *ns, BOOL *overflow)
    {
    int i, nlen, dig;
    char calv[SQL_MAX_NUMERIC_LEN * 3];
    BOOL dot_exist;

overflow = FALSE;
/ skip leading space /
while (wv && isspace((unsigned char) wv))
wv++;
/ sign /
ns->sign = 1;
if (wv == '-')
{
ns->sign = 0;
wv++;
}
else if (*wv == '+')
wv++;

/* skip leading zeros /
while (wv == '0')
wv++;

/* read the digits into calv /
ns->precision = 0;
ns->scale = 0;
for (nlen = 0, dot_exist = FALSE;; wv++)
{
if (wv == '.')
{
if (dot_exist)
break;
dot_exist = TRUE;
}
else if (*wv == '\0' || !isdigit((unsigned char) *wv))
break;
else
{
if (nlen >= sizeof(calv))
{
if (dot_exist)
break;
else
{
ns->scale--;
*overflow = TRUE;
continue;
}
}
if (dot_exist)
ns->scale++;
calv[nlen++] = *wv;
}
}
ns->precision = nlen;

/* Convert the decimal digits to binary /
memset(ns->val, 0, sizeof(ns->val));
for (dig = 0; dig < nlen; dig++)
{
UInt4 carry;
/ multiply the current value by 10, and add the next digit */
carry = calv[dig] - '0';
for (i = 0; i < sizeof(ns->val); i++)
{
UInt4 t;
t = ((UInt4) ns->val[i]) * 10 + carry;
ns->val[i] = (unsigned char) (t & 0xFF);
carry = (t >> 8);
}
if (carry != 0)
*overflow = TRUE;
}
}
绕过的用法 A：

ODBC 绑定变量时使用 SQL_CHAR，转成 SQL_CHAR 时使用数据库指定的精度或者数字本身的精度

绕过的用法 B

在 SQL 中绑定变量部分添加 round 函数, 类似于 round( ? , <精度> )，其中精度部分使用数据库指定的精度或者数字本身的精度。

merge INTO Parts using dual on partid = round($1,2)
when matched then update set Price = $2
when not matched then insert values($3,$4)

标签：price,ODBC,数据库,merge,SQL,主键,精度,matched
From： https://www.cnblogs.com/helloopenGauss/p/18108092

相关文章

  • 客快物流大数据项目（九十三）：ClickHouse的ReplacingMergeTree深入了解 ClickHouse清除重
    ​ClickHouse的ReplacingMergeTree深入了解为了解决MergeTree相同主键无法去重的问题，ClickHouse提供了ReplacingMergeTree引擎，用来对主键重复的数据进行去重。删除重复数据可以使用optimize命令手动执行，这个合并操作是在后台运行的，且无法预测具体的执行时间。在使用optimize命......
  • 使用MergeKit创建自己的专家混合模型：将多个模型组合成单个MoE
    由于Mixtral的发布，专家混合(MoE)架构在最近几个月变得流行起来。虽然Mixtral和其他MoE架构是从头开始预训练的，但最近出现了另一种创建MoE的方法：Arcee的MergeKit库可以通过集成几个预训练模型来创建moe。这些人通常被称为frankenMoEs或MoErges，以区别于预先训练的MoEs。在本文中，我......
  • 数据库中的约束纯干货——主键约束
    目录（一）特点：（二）添加主键约束2.1格式：2.2举例：......
  • Mysql（数据库）知识详解【4】~{索引，主键优化}
    记住满元素中间元素向上裂变就行了因为如果是5个节点，比第一个节点小的算一个指针，逼最后一个节点大的算一个指针，里面是4个指针所有元素都会出现在叶子节点并且诸多叶子节点通过指针构造一张单项链表看我：除了最下面节点，上面的节点（叶子空间最大16k）全部放满内存......
  • LeetCode-21 Merge Two Sorted Lists
    21.MergeTwoSortedLists EasyYouaregiventheheadsoftwosortedlinkedlists list1 and list2.Mergethetwolistsintoone sorted list.Thelistshouldbemadebysplicingtogetherthenodesofthefirsttwolists.Return theheadofthemerg......
  • Pillow教程03：图像处理的基本步骤+分离split+合并merge+混合blend+composite遮罩
    --------------Pillow教程集合---------------Python项目18：使用Pillow模块，随机生成4位数的图片验证码Python教程93：初识Pillow模块（创建Image对象+查看属性+图片的保存与缩放）Pillow教程02：图片的裁剪+复制粘贴+旋转角度+翻转+降噪滤镜（平滑、锐化、边缘检测）Pillow教程03：图像......
  • commit后怎么解决冲突 merge
    这个时候选择rebase（一定选择rebase，企业中规范要求，直接merge，可能会导致一系列问题） 参考：https://blog.csdn.net/weixin_45565886/article/details/126926514merge和rebase的区别参考2：https://blog.csdn.net/muzidigbig/article/details/122519949......
  • ORACLE 在缺少主键ID的情况下删除重复数据，只保留一行
    有时候因未进行数据验证或其他特殊场景，导致数据表中出现重复的数据，需要对重复数据进行删除，并且只保留其中一条。一、当数据表存在主键时，可使用ROW_NUMBER进行排序后删除，参考脚本如下：  删除带有主键的重复数据DELETEFROMDUPLICATES_TABLEWHEREIDIN(SELECTID......
  • 数据库表创建 - 主键约束:在创建数据库表时，可以使用主键约束来唯一标识记录
    数据库表创建-主键约束在数据库设计和管理中，创建数据库表是一个关键步骤。在创建数据库表时，一个重要的概念是主键约束。主键约束用于唯一标识表中的每条记录，它在确保数据完整性和查询效率方面起着至关重要的作用。本文将深入探讨主键约束的概念、作用以及在数据库表创建......
  • stata 14中help merge 文档中的assert错误修正
    如图，小伙伴们注意在stata14中对于Helpmerge文档中，options中assert的用法，我画红线这里是有问题的，应该是notallow而不是allow这里的意思是：使用assert(matchmaster)指定合并文件必须仅包含匹配的/主数据观测值或使用数据中的观察值/和/未匹配的主数据观察结果，并且不得......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99