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:Microsoft Windows (WinXP SP2 Visual C++.net 2003)
Assigned to: Jess Balint CPU Architecture:Any

[5 Oct 2005 9:24] Jack Bauer
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.
[5 Oct 2005 11:02] Jack Bauer
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.
[5 Oct 2005 11:24] Vasily Kishkin
Could you please create and attach some test case ?
[5 Oct 2005 13:26] Jack Bauer
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
[7 Oct 2005 14:23] Jack Bauer
Any ideas ?
[12 Oct 2005 8:02] Vasily Kishkin
Sorry...I guess I need full text of test case. Could you please provide ?
[14 Oct 2005 10:48] Jack Bauer
Visual C++.net project file

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

[14 Oct 2005 10:51] Jack Bauer
Description

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

[14 Oct 2005 10:54] Jack Bauer
In files i upload my test project, this will demonstrate error. Please try this and see the error "no rows affected....."

Thanks for compilance.
[17 Oct 2005 6:35] Vasily Kishkin
Thanks for the test case. I was able to reproduce the bug. I tested on 3.51.12
[27 Oct 2005 14:20] Jack Bauer
I download, install and try 3.51.12 version, but still this error in version 3.51.12 is not solved.
[5 Nov 2005 15:32] [ name withheld ]
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
[3 Apr 2006 18:38] Jon McDaniel
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????
[6 Jul 2007 21:22] Jess Balint
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').
[10 Jul 2007 21:59] Jess Balint
Committed in rev558, will be 3.51.17.
[20 Jul 2007 12:00] MC Brown
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.
[21 Aug 2007 18:48] Tonci Grgin
Continued in Bug#30539.