MySQL Bugs: #103287: SQLSetPos(SQL_DELETE) does not produce diagnostic for duplicate key

Bug #103287	SQLSetPos(SQL_DELETE) does not produce diagnostic for duplicate key
Submitted:	12 Apr 2021 13:57	Modified:	4 Jun 2021 6:32
Reporter:	Steve Millington	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If I try to update a row such that the new primary key already exists, or indeed any other unique key already exists, then I am expecting an error with a diagnostic containing state 23000 and a message something like "Duplicate entry 'A' for key 'A'". With MySQL ODBC connector I get an error (SQLRETURN is -1), but there is no diagnostic record associated with the error.

Doing the exact same thing with the MariaDb Driver (version 3.1.11) against the same MySQL database operates correctly.

How to repeat:

I am using MySQL 5.7.12 and MySQL ODBC Driver 8.0.23 on 64-bit Centos 7.

Running the exact same test with the MariaDb Driver (version 3.1.11) operates correctly.

I will be attaching a .c source file, and a Makefile for building and running on a Linux 64-bit system.

I have no reason to believe that the problem is specific to the platform nor the specific type of the columns being updated.

C source and Makefile for problem demonstration

Attachment: MySQL103287.tgz (application/x-compressed, text), 2.36 KiB.

With the example provided, the results are

$ make drop
./MySQL103287 drop Test-MySQL1
ODBC Driver libmyodbc8a.so - version 08.00.0023
ODBC DBMS MySQL - version 5.7.12
Dropping table BUG103287
$ make create
./MySQL103287 create Test-MySQL1
ODBC Driver libmyodbc8a.so - version 08.00.0023
ODBC DBMS MySQL - version 5.7.12
Creating table BUG103287
$ make insert
./MySQL103287 insert Test-MySQL1
ODBC Driver libmyodbc8a.so - version 08.00.0023
ODBC DBMS MySQL - version 5.7.12
Inserting records into table BUG103287
$ make update
./MySQL103287 update Test-MySQL1
ODBC Driver libmyodbc8a.so - version 08.00.0023
ODBC DBMS MySQL - version 5.7.12
SQLSetPos(SQL_UPDATE, SQL_LOCK_NO_CHANGE) -> -1
$ 

But with MariaDB ODBC driver the results are

$ make create
cc -c -m64 -Wall MySQL103287.c -o MySQL103287.o
cc -m64 MySQL103287.o -lodbc  -o MySQL103287
./MySQL103287 create Test-MySQL2
ODBC Driver libmaodbc.so - version 03.01.0011
ODBC DBMS MySQL - version 05.07.000012
Creating table BUG103287
$ make insert
./MySQL103287 insert Test-MySQL2
ODBC Driver libmaodbc.so - version 03.01.0011
ODBC DBMS MySQL - version 05.07.000012
Inserting records into table BUG103287
$ make update
./MySQL103287 update Test-MySQL2
ODBC Driver libmaodbc.so - version 03.01.0011
ODBC DBMS MySQL - version 05.07.000012
SQLSetPos(SQL_UPDATE, SQL_LOCK_NO_CHANGE) -> -1
SQLSetPos(SQL_UPDATE, SQL_LOCK_NO_CHANGE): 23000:1:1062:[ma-3.1.11][5.7.12]Duplicate entry 'B' for key 'A'
$

Thank you for the bug report and test case.

Posted by developer:
 
The patch and the corresponding unit test are pushed into the source repository.

That is great news Bogdan. Does that mean I am likely to see the fix in the next patch version (8.0.26)? Or has it missed the dates for that and I will have to wait till the one after.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.26 release, and here's the proposed changelog entry from the documentation team:

Attempting to update a row with an existing unique key would not emit a
diagnostic error that reported the problem, such as "Duplicate Entry".

Thank you for the bug report.

Sounds good - thanks Philip.

Eagerly awaiting the release :-)