MySQL Bugs: #38147: error number -2147217864 (80040e38) updating recordset

Bug #38147	error number -2147217864 (80040e38) updating recordset
Submitted:	15 Jul 2008 18:40	Modified:	20 Jan 2012 6:51
Reporter:	german barbosa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	MySql ODBC 5.1.4	OS:	Windows (Xp Professional)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	error 80040e38, updating recordset problem

Description:
I have a application in visual basic 6 I was updating the ODBC driver 3.51 to 5.1, the aplication work perfect with the 3.51 driver the only chance I make was in the DSN, con.Open "DSN=MySqlfundaciob" for con.Open "DSN=MySqlfundacio"
MySqlfundaciob is the 3.51 connection make with the ODBC administrator and MySqlfundacio is the one I make for the 5.1 driver

When  I try to update a recordset the next error show up error number -2147217864 (80040e38) Row cannot be located for updating. Some values may have been changed since it was last read.

I appreciate any help with this,

And please forgive my English I'm from Spain 

How to repeat:
I have the next code
 Set con = New ADODB.Connection
 con.Open "DSN=MySqlfundacio"

SQL1 = "SELECT * FROM Proyecto WHERE codproy='" & txtCodigo.Text & "'"
        Set rsProyecto = New ADODB.Recordset
             
        With rsProyecto
            .ActiveConnection = con
            .CursorLocation = adUseClient
            .CursorType = adOpenDynamic
            .LockType = adLockOptimistic
            .Open SQL1
        End With
        With rsProyecto
            .Fields("totalconc") = gvsPSCampo(Trim$(txtConcedido.Text))
            .Update 'Error show up her
       End With

and the Table structure is 

CREATE TABLE `proyecto` (
  `codproy` varchar(9) NOT NULL,
  `cont` smallint(5) DEFAULT NULL,
  `nsubgrupo` tinyint(3) unsigned DEFAULT NULL,
  `titulo` varchar(255) DEFAULT NULL,
  `tipo_actividad` varchar(25) DEFAULT NULL,
  `subtipo_actividad` varchar(35) DEFAULT NULL,
  `entidad_financ` varchar(12) DEFAULT NULL,
  `organismo` smallint(5) DEFAULT NULL,
  `fentrada` datetime DEFAULT NULL,
  `comite` varchar(10) DEFAULT NULL,
  `finicio` datetime DEFAULT NULL,
  `ffin` datetime DEFAULT NULL,
  `area` varchar(7) DEFAULT NULL,
  `fenvioetico` datetime DEFAULT NULL,
  `faprobetico` datetime DEFAULT NULL,
  `faprobcient` datetime DEFAULT NULL,
  `fconcesionfirma` datetime DEFAULT NULL,
  `registro` varchar(60) DEFAULT NULL,
  `estado` varchar(1) DEFAULT NULL,
  `totalconc` decimal(19,4) DEFAULT NULL,
  `bloqueo` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`codproy`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

the exception aper with any table but this is and example

Please provide ODBC call trace as described at:

http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-trace.html

Log archives ODBC

Attachment: log.zip (application/zip, text), 98.11 KiB.

Hi thanks for the reply I already submit the log files.
SQL.LOG Call Trace ODBC driver 3.1
SQL2.LOG Call Trace ODBC driver 5.1

I have the same problem, is there any solution?

Hi German and thanks for your report.

The oldest tool I have is VS2003, sorry, no VB6...

Now, my test shows that simple case like:
Module Module1
    Sub Main()
        Dim con
        Dim SQL1
        Dim rsProyecto
        con = New ADODB.Connection
        con.Open("DSN=51")
        SQL1 = "SELECT * FROM bug38147 WHERE Fld1 = 'Primo'"
        rsProyecto = New ADODB.Recordset
        With rsProyecto
            .ActiveConnection = con
            .CursorLocation = ADODB.CursorLocationEnum.adUseClient
            .CursorType = ADODB.CursorTypeEnum.adOpenDynamic
            .LockType = ADODB.LockTypeEnum.adLockOptimistic
            .Open(SQL1)
        End With
        With rsProyecto
            .Fields("totalconc") = 10.1
            .Update() 'Error show up her
        End With
    End Sub
End Module
works as expected with both drivers on:
 - remote MySQL server 5.0.68pb-win32
 - MyODBC 3.51.26 & 5.1.5 pre-release
mysql> CREATE TABLE `bug38147` (
    ->   `Fld1` varchar(9) NOT NULL,
    ->   `Fld2` smallint(5) DEFAULT NULL,
    ->   `Fld3` tinyint(3) unsigned DEFAULT NULL,
    ->   `Fld4` smallint(5) DEFAULT NULL,
    ->   `Fld5` datetime DEFAULT NULL,
    ->   `Fld6` varchar(10) DEFAULT NULL,
    ->   `Fld7` datetime DEFAULT NULL,
    ->   `Fld8` datetime DEFAULT NULL,
    ->   `Fld9` varchar(7) DEFAULT NULL,
    ->   `totalconc` decimal(19,4) DEFAULT NULL,
    ->   PRIMARY KEY (`Fld1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.28 sec)

mysql> insert into bug38147 values ("Primo",2,3,4,NULL,"6",NULL,NULL,"9",10.10);

Query OK, 1 row affected (0.05 sec)
Update with 5.1.5:
mysql> select * from bug38147;
+-------+------+------+------+------+------+------+------+------+-----------+
| Fld1  | Fld2 | Fld3 | Fld4 | Fld5 | Fld6 | Fld7 | Fld8 | Fld9 | totalconc |
+-------+------+------+------+------+------+------+------+------+-----------+
| Primo |    2 |    3 |    4 | NULL | 6    | NULL | NULL | 9    |   12.5000 |
+-------+------+------+------+------+------+------+------+------+-----------+
1 row in set (0.00 sec)
Change DSN to 3.51.26, update value back:
mysql> select * from bug38147;
+-------+------+------+------+------+------+------+------+------+-----------+
| Fld1  | Fld2 | Fld3 | Fld4 | Fld5 | Fld6 | Fld7 | Fld8 | Fld9 | totalconc |
+-------+------+------+------+------+------+------+------+------+-----------+
| Primo |    2 |    3 |    4 | NULL | 6    | NULL | NULL | 9    |   10.1000 |
+-------+------+------+------+------+------+------+------+------+-----------+
1 row in set (0.00 sec)
mysql>

Server log:
080718 11:44:31	     28 Query       CREATE TABLE `bug38147` (
  `Fld1` varchar(9) NOT NULL,
  `Fld2` smallint(5) DEFAULT NULL,
  `Fld3` tinyint(3) unsigned DEFAULT NULL,
  `Fld4` smallint(5) DEFAULT NULL,
  `Fld5` datetime DEFAULT NULL,
  `Fld6` varchar(10) DEFAULT NULL,
  `Fld7` datetime DEFAULT NULL,
  `Fld8` datetime DEFAULT NULL,
  `Fld9` varchar(7) DEFAULT NULL,
  `totalconc` decimal(19,4) DEFAULT NULL,
  PRIMARY KEY (`Fld1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
		     28 Query       insert into bug38147 values ("Primo", 2,3,4,NULL,"6",NULL,NULL,10.10)
080718 11:45:02	     28 Query       insert into bug38147 values ("Primo",2,3,4,NULL,"6",NULL,NULL,"9",10.10)
080718 11:50:01	     29 Connect     root@XP-OLD-TOOLS on test
		     29 Query       SET NAMES utf8
		     29 Query       SET character_set_results = NULL
		     29 Query       select database()
		     29 Query       select database()
		     29 Query       SELECT @@tx_isolation
		     29 Query       SELECT * FROM bug38147 WHERE Fld1 = 'Primo'
		     29 Query       UPDATE `test`.`bug38147` SET `totalconc`=12.5000 WHERE `Fld1`=_latin1'Primo' AND `totalconc`=10.1000
080718 11:50:02	     29 Quit       
080718 11:50:18	     28 Query       select * from bug38147
080718 11:50:39	     30 Connect     root@XP-OLD-TOOLS on test
		     30 Query       select database()
		     30 Query       select database()
		     30 Query       SELECT @@tx_isolation
		     30 Query       SELECT * FROM bug38147 WHERE Fld1 = 'Primo'
		     30 Query       UPDATE `test`.`bug38147` SET `totalconc`='10.1000' WHERE `Fld1`='Primo' AND `totalconc`='12.5000'
		     30 Quit       
080718 11:50:45	     28 Query       select * from bug38147

Now, your logs are *unusable*! You do not really expect me to scan all 100000(!!!) lines in both logs??? Please boil down your test case to something small and start logging *only* then so we get log of less than 50 lines please.

Another point; both of your logs show *successful* updates (yes yes, I took a look at 2x100000 lines...) but done via parameters which is not in accordance to your test case. Please post small, compact test case not using any of your specifics.

Jess?

My log

Attachment: Bug38147-SQL.zip (application/zip, text), 6.24 KiB.

Sorry, wrong comment on params in test case, just checked my log (uploaded here). Please provide us with as small log as possible.

Hi

doing some tests I could see, that there is a different behavior depending on
the testcase.

1. read a record and update the record immediately              = OK
2. read a record
   - move data from record to the form
   - move data from form back to the record (without change)
   - Update record                                              = error

reards
Rainer

Rainer:
2. read a record
   - move data from record to the form
   - move data from form back to the record (without change)
   - Update record                                              = error

Updating record with no changes should throw error as there really is nothing to update...

I still don't see full&compact test case attached, nor small trace. Not much I can do...

Rainer,

Make sure you have the "Return matched rows instead of affected rows" option enabled on your DSN. This was added explicitly for the situation you describe.

Hi Jess,

I have aktivated "return matched row" - the result is still error (80040e38)!
By the way, my machine becomes extremly slow ........ 

Just during start of my application I read the table "tbluser" to check if 
the user is authorized - I have to wait about 10 sec for the answer - there
is only 1 record in the table .....

sSQL = "SELECT * FROM tblUser WHERE UserName = '" & UserID & "'"

Any idea what could be wrong?

Thank you
Rainer

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".

Had same error, however had it with recordsets rather than data control.
From another forum, I learnt that if you are using a static cursor for the recordset, using adlockbatchoptimistic instead of adlockoptimistic solved the problem.

gl and cheers
svo

I have just tried the VB code in my Access VB6 and it worked perfectly with the latest ODBC driver version 5.1.9.

Closing the bug.