Bug #33153 Update with primary key effect all rows
Submitted: 11 Dec 2007 20:12 Modified: 4 May 2009 7:37
Reporter: Fuat Akgn Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.0.45 OS:Windows
Assigned to: CPU Architecture:Any
Tags: primary key, UPDATE, whole table

[11 Dec 2007 20:12] Fuat Akgn
Description:
Hi,

I am working on my local MySQL server whose version is 5.0.45

I sometimes have problem with updating a table over an primary key with asp

rs.open"select davetiye from uye where id=1",bag,1,3
rs(0) = rs(0) - 1
rs.update
rs.close

Everytime this sql command executed "davetiye"(integer) column must be decremented by 1 over recordset object which is used in ASP.

This problem is not first time but it doesn't always occure.

I am giving my table named "uye"

DROP TABLE IF EXISTS `bocuk`.`uye`;
CREATE TABLE  `bocuk`.`uye` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ad` varchar(45) default NULL,
  `email` varchar(100) default NULL,
  `davetiye` int(10) unsigned default '20',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin5;

How to repeat:
This problem is not first time but it doesn't always occure as I said. But I really don't know when or how this became...

Suggested fix:
When i use 

rs.open"select * from uye where id=1",bag,1,3
rs("davetiye") = rs("davetiye") - 1
rs.update
rs.close

there will be no problem and only the correct row will be efected.
[12 Dec 2007 12:34] Tonci Grgin
Fuat, this is in no way enough information for me to start working on this report.
Please attach/check following:
 - What is your MyODBC version.
 - Please attach *full* test case.
 - Please check general query log from MySQL server at the time error occurs. To start server with logging, please see manual. What is the actual update command issued?
 - Please check http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1. You can use it like this:
Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Properties("Update Criteria").Value = 0
    .Open(strSQL)
    .Update "Name", "nesto_drugo"
End With
 - Please attach DM trace log (Start/Control Panel/Administrative Tools/Data Sources (ODBC)/ Tracing tab).
 - Is your table error free? Did you check it recently?
[9 Jan 2008 3:11] Jess Balint
See bug#33388 or bug#29765 for related information.
[13 Jan 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[30 Apr 2009 19:04] Fuat Akgn
The same problem is here 
http://bugs.mysql.com/bug.php?id=35881
[4 May 2009 7:37] Tonci Grgin
Thanks Fuat. Marking this report as duplicate of Bug#35881.
[10 Mar 2011 13:05] Tapas Barman
Hi,

i have sqL server running well, and i want to develop a web program using PHP and MySQL as database.

i use SQL server 2005 dan MySQL 5.0, i want to my SQL Server automatic migrate the data to MySQL Server.
So i make a proc to delete old data in MySQL Server and Insert new data to it.
but when i want delete/query at about 10 million data from sql server to mysql server using ODBC 5.1 Driver,

My SQL Server give error
" OLE DB provider "MSDASQL" for linked server "CON_MYSQL" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]MySQL client ran out of memory"."

Please Help me to solve my problem. thx.