MySQL Bugs: #35881: update fails - Build WHERE -> insert

Bug #35881	update fails - Build WHERE -> insert_fields() failed / SQLSetPos
Submitted:	7 Apr 2008 16:38	Modified:	23 Apr 2008 8:28
Reporter:	Bogdan Pytlik	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S4 (Feature request)
Version:	3.51.23, 5.1.3	OS:	Windows (w2k SP4)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
using MFC CRecordset apis
one record in the table
update fails with "Build WHERE -> insert_fields() failed." error
ODBC sql log shows

mysql_odbc_1    650-308	EXIT  SQLSetPos  with return code -1 (SQL_ERROR)
		HSTMT               00831C30
		SQLSETPOSIROW      		SQLSETPOSIROW      		UWORD                        2 <SQL_UPDATE>
		BOOL                         0 <SQL_LOCK_NO_CHANGE>

		DIAG [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Build WHERE -> insert_fields() failed. (0) 

myRS uses 2 columns of 3 column table

The bug as a workaround if all columns defined in table
 are used in myRS (#define HACK in *.cpp and rebuild)

We are seeking fix as the workaround I found 
may not work as our customers are free to add columns to 
the tables our app is using.

How to repeat:
see attached VS6 project
create database test1, 
create DSN test1, configure DSN with odbc-opts-test1.reg.txt
open *.dsw 
build mysql_odbc_1 project
run mysql_odbc_1.exe

files to recreate the issue

Attachment: pr5.zip (application/zip, text), 3.46 KiB.

Hi Bogdan and thanks for your report.

Looking at ODBC specs (http://msdn2.microsoft.com/en-us/library//ms713507.aspx) I must say I would expect error to be thrown with SQLState of 23000 or 44000 as you have *no* primary key defined and you're not fetching all of the fields from table which should make positioned update impossible. This requires more investigation tough.

I can't make up my mind here (see my prior remark).
Testing with MSSQL server shows it's *not* capable of making this update too (!) but at least MS marks such SELECT (with missing fields) as "Read only" thus error message is more clear.
So, if there's an error here, it is in fact that "SELECT only_few_fields" query is not marked as RO.

Workaround(s) is obvious; use all fields in SELECT statement or change your code.

I will let c/ODBC team decide on this one.

MyODBC 5.1.3 fails in the same manner.

I did more tests and it appears that if PKEY (unique index) 
column is present in the SELECT clause
then the positional update will succeed 
even when not all columns are present in SELECT clause
see attached pr5.2.zip

test with added primary key

Attachment: pr5.2.zip (application/zip, text), 1.49 KiB.

Bogdan,
Can you include an ODBC trace log from the 5.1.3 driver? Thanks.

odbc trace log with 5.1.3 driver

Attachment: pr5.3.zip (application/zip, text), 1.99 KiB.

I have done a test on 

os : win server 2003 sp1
mysql version : 5.0.45
odbc conector : 5.1

This error no come out already. Running succesfully.

The workaround of including primary key in SELECT does resolve issue, at least for my use. As long as this works for future releases I am fine.
I am lowering severity S2=>S4 to indicate that.

Bug#33153 was marked as duplicate of this report.