Bug #17213 Write Conflict
Submitted: 7 Feb 2006 23:53 Modified: 17 Oct 2007 11:07
Reporter: Young Lim Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51 OS:Windows (Windows XP Pro)
Assigned to: CPU Architecture:Any

[7 Feb 2006 23:53] Young Lim
Description:
When I try to update a record, I get this error:

"Row cannot be located for updating. Some values may have been changed
since it was last read."

This only happens on some records - not all.

No one else is using/accessing the database - it is on my PC.

I've tried using the Return Matching Rows/setting OPTION=2 in my connection string and still gettting the same problem.

I am using MySQL Server 5 and MyODBC 3.51.

How to repeat:
I open the database using Visual Basic 6 and this connection string:

"Driver={MySQL ODBC 3.51 Driver}; " & _
"Server=localhost; " & _
"Database=test; " & _
"UID=admin; " & _
"PWD=password; " & _
"OPTION=2"

The update is standard ADO update: e.g. sv_recordset.update
[8 Feb 2006 10:13] Valeriy Kravchuk
Please, send the exact CREATE TABLE statement for the table you are trying to update, as well as exact UPDATE that is performed. You can get the UPDATE from the ODBC trace file (if you enable tracing for the datasource), or from the general query log on the server.

Anyway, wee need exact and repeatable sequence of actions to demonstrate the behaviour that looks like a bug to you.
[8 Feb 2006 22:09] Young Lim
It is not a CREATE TABLE statement - it is a SELECT.

Here's the code (the select is stored into a recordset in Visual Basic 6):

SELECT * FROM INCI WHERE IDC = 1 (IDC is the primary key and is a long integer)

then to update:

rst("ET") = 2
rst("BT") = 1

rst.update

This write conflict only happens on 2 records out of about 20.
[15 Feb 2006 11:45] Bogdan Degtyariov
VB recordset uses not only primary keys in WHERE clause to update the records.
Therefore we need the table structure that can be displayed by "SHOW CREATE TABLE INCI;" statement.
Most probably - this table contains a double/float/decimal values. Depending on the type of the column MySQL can round the value to fit it to the column type.
For example:
may your table contains two columns: id int primary key, ddata decimal(10,3)
If you insert into ddata some value that exceeds the precision (5.1234 for example), this value will be rounded in MySQL to 5.123. But your recordset variable will still hold its old value 5.1234. So, when you attempt to update this record - ADODB library will generate a query similar to that one:
UPDATE INCI SET .... WHERE id=1 AND ddata=5.1234;
Of course there is no such record with ddata=5.1234, because it was rounded to 5.123 and your application didn’t update the value of the bound variable.
It was just an example, but there are possible other situations (when you use just SELECT and UPDATE etc). We can't tell you what happens in this particular situation because the table structure is unknown for us.
[15 Feb 2006 23:35] Young Lim
The update is on a floating point number. Should I then round it to 2 decimal places all the time?
[17 Feb 2006 7:31] Young Lim
The value I am updated is definitely within the correct precision - it is 980.39.

I've also included a debug statement and it shows the current value in the ADO recordset is 980.39 and it is updating it with exactly the same number. So this appears not be a precision issue.

If I change it to use SQL update (UPDATE SET colname = value), it works.
[20 Feb 2006 12:55] Valeriy Kravchuk
Please, send the SHOW CREATE TABLE INCI results. They are really needed to be able to help you.
[21 Mar 2006 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".
[13 Aug 2007 9:14] Cristina Polo Conde
I have exactly the same problem using MyODBC 3.51.19 and MS Access 2002.

When I try to modify or delete a record, a message notifying a writing conflict appears, however, there is no any other user conected.

I had no problems with the update of this table in the past but since last month I cannot modify any record of this table and the same is happening with other tables, even from different databases, but in this case the error does not appear always.

I would be very grateful if you could help me as soon as possible since it is very important for the company.

The CREATEstatement follows:

contratos |CREATE TABLE `contratos` (
  `cod_presupuesto` varchar(5) NOT NULL default '',
  `area` char(3) default NULL,
  `proveedor` varchar(100) default NULL,
  `descripcion` varchar(255) default NULL,
  `inicio_cont` datetime default NULL,
  `fin_cont` datetime default NULL,
  `f_vto` datetime default NULL,
  `estado` varchar(50) default NULL,
  `observacion_estado` varchar(255) default NULL,
  `periodicidad` int(7) default NULL,
  `tipo_servicio` varchar(50) default NULL,
  `fianza` double(10,3) default NULL,
  `prorroga` varchar(50) default NULL,
  `fichero` varchar(50) default NULL,
  `duracion_contrato` int(7) default NULL,
  `fianza_prov` double(10,3) default NULL,
  `fecha_fianza` datetime default NULL,
  `fecha_adj` datetime default NULL,
  `fecha_form_contrato` datetime default NULL,
  `cuota` double(10,3) default NULL,
  `NContrato` int(11) default NULL,
  `fecha_firma` datetime default NULL,
  `fecha_alta_bd` datetime default NULL,
  `fecha_solicitud` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`cod_presupuesto`)
) TYPE=MyISAM |
[17 Oct 2007 11:07] Susanne Ebrecht
This bug is for MyODBC version 3.51.
I set this bug as duplicate to bug #30386, because there are more actual informations.
[16 Apr 2008 10:59] Tonci Grgin
Please see http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1 regarding recordset.Properties("Update Criteria").Value = 0.