MySQL Bugs: #13766: No rows were affected by the update or delete operation

Bug #13766	No rows were affected by the update or delete operation
Submitted:	5 Oct 2005 9:24	Modified:	20 Jul 2007 12:00
Reporter:	Jack Bauer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.11-2	OS:	Windows (WinXP SP2 Visual C++.net 2003)
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
I have this code:
m_pDB is based on CRecordset *.
m_pDB have members:   m_field1, m_field2, m_field3

With functions: m_pDB.setfilter, or setsort, m_pDB->MovePrev(), m_pDB->MoveLast(), m_pDB->Requery() ... is all working fine. 

In this code:
// m_pDB->AddNew();
// m_pDB->m_field1 = 1;
// m_pDB->m_field2 = 2;
// m_pDB->m_field3 = 0;
// m_pDB->Update();
is all ok, this add a new record to db.

How to repeat:
But in this code :
// m_pDB->Edit();
// m_pDB->m_field1 = 2;
// m_pDB->m_field2 = 3;
// m_pDB->m_field3 = 4;
// m_pDB->Update();
i have everytime this result: "No rows were affected by the update or delete operation"

It's my error or it's a bug ? 

Thx for answer.

I try make new DNS in ODBC with connection to MDB file. In this DNS work update records fine. This seems must be a bug in myODBC.

I search another problem. In http://marc.theaimsgroup.com/?l=mysql-odbc&m=100689315516708&w=2 is answer "check option..."  This solution is not working.

Could you please create and attach some test case ?

I derived a class CMyDb from CRecordset using ClassWizard and tested it with:

CMyDb pDB;	//Working
pDB.Open();	//Working
pDB.m_strFilter.Format("number = %li", 2);	//Wokring
pDB.Requery();	//Work => here have in main variables some good values
pDB.MoveFirst();	//Work
pDB.Edit();		//Work
pDB.number=15;		//Work
pDB.Update();		//Get Error result "No rows were affected by the update or delete operation"
pDB.Close();		//Work

Server Linux MySQL 4.0.24
myODBC 3.51.11-2
In settings myODBC: Check option 'Return Matching Rows' has no effect for this error

Any ideas ?

Sorry...I guess I need full text of test case. Could you please provide ?

Visual C++.net project file

Attachment: Test.zip (text/html), 61.62 KiB.

Description

Attachment: Readme.txt (text/plain), 1.31 KiB.

In files i upload my test project, this will demonstrate error. Please try this and see the error "no rows affected....."

Thanks for compilance.

Thanks for the test case. I was able to reproduce the bug. I tested on 3.51.12

I download, install and try 3.51.12 version, but still this error in version 3.51.12 is not solved.

I just solved a problem just like this one.  In my case, the script that created the database table in question includes the following:
    ID INTEGER NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (ID),
    UNIQUE (ID),
    ...

Trying to update records in the table works only with the row ID=1.

The logged query looks like this (say trying to update row with ID=2):

Update 'table' ... WHERE 'ID'=2, AND ..., AND 'ID'=1;

Notice the second 'ID'=1, we know why the database does not change anything and does not report any error.

Inspecting the table using MySQL Administrator, the followings shows up in the Indices:

PRIMARY
...
ID

Apparently, that's why the second 'ID'=1 comes from.  By deleting 'ID' from Indices list, everything seemed to be good.

The solution to my problem is, of course, to remove
    UNIQUE (ID)
from the script that creates the database.

What I would like to know is, which party has most to blame?  The programmer that added UNIQUE (ID), MySQL that reporting (created?) two duplicated indices, MyODBC driver, or some internal implementation in SQLSetPos which CRecordset used to perform the update.

XZ

So..., this bug was reported last year ( October 2005 ), verified, given a status of "S2" (Serious). SO AFTER 6 MONTHS ------ WHAT IS THE RESOLUTION????

I have to assume that a serious error such as this would have been fixed ASAP. And that you just forgot to update the bug database.

Let me add a few facts about this bug.

1.  It happens every now and then.
2.  It throws a fatal error ( kinda hard to work around )
3.  Stops your application DEAD IN ITS TRACKS.

So..... Why is this still a problem????

Bug is caused by table having default values of 0000-00-00. This value is returned by the driver as NULL (invalid ODBC date) and the update statement includes "WHERE the_date is NULL" (should be "WHERE the_date = '0000-00-00'").

Fix is DSN options that converts zero-dates and the minimum allowed by ODBC ('0000-01-01').

Committed in rev558, will be 3.51.17.

A note has been added to the 3.51.17 changelog: 

Added a new DSN "OPTION" (FLAG_ZERO_DATE_TO_MIN) to retrieve XXXX-00-00
dates as the minimum allowed ODBC date (XXXX-01-01). Added another
option (FLAG_MIN_DATE_TO_ZERO) to mirror this but for bound parameters.
FLAG_MIN_DATE_TO_ZERO only changes 0000-01-01 to 0000-00-00.

Continued in Bug#30539.