| Bug #35640 | Can not Identify row identity | ||
|---|---|---|---|
| Submitted: | 28 Mar 2008 12:50 | Modified: | 24 Dec 2011 13:39 |
| Reporter: | Louis Breda van | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 5.1.x | OS: | Windows (VISTA 64) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | Row Identity, second update, Update not possible | ||
[28 Mar 2008 12:54]
Tonci Grgin
Hi Louis and thanks for your report. This is not enough by far... Please attach complete test case together with tables and data needed to reproduce the problem.
[28 Mar 2008 16:23]
Louis Breda van
Hello, Trying to work arround the problem, I tested with ODBC 3.5. I can report here, that ODBC 3.5 does not have this problem. So, if there is no need to use odbc 5.1, you can use 3.5 to bypass the problem. Louis
[4 Apr 2008 8:28]
Tonci Grgin
Louis, I still have problem following you...
Have you checked on ADO documentation regarding "Update Criteria"?
example: rs.Properties("Update Criteria").Value = ...
I am missing self-contained test case, ODBC traces, general query logs from server etc...
[4 May 2008 23: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".
[11 Jun 2008 19:50]
Louis Breda van
Hello, As described before, the ODBC/SERVER combination now and then could not find the row to be updated. This critical error has become dramatic with when the new ODBC-adapter is combined with server version 5.1.25. Now with server version 5.1.25 updates via ODBC seems to be impossible at all. The only possible ^update^ seems to be from "Null" to some value. Since that made my DB completely unusable, I had to decide to go back to ODBC 3.51.25. That ODBC-version has a lot of disadvantages (supported functionallity and performance), but it works. I had to change a significant number of querys to make them running with ODBC3. I am retesting now, If every thing still works (lots of work, hoping for the best). Note that in general 5.1.25 seems to be an improvement. Louis
[16 Jun 2008 13:27]
Tonci Grgin
Louis, I don't follow you again. Please see my test case and modify where necessary. My test case produces correct result on both 5.0.64 and 5.1.26: mysql> create table bug35640 (Id INT NOT NULL PRIMARY KEY, SomeText VARCHAR(100)); Query OK, 0 rows affected (0.08 sec) mysql> insert into bug35640 select Rbr, Ime from boot; Query OK, 569388 rows affected (6.70 sec) Records: 569388 Duplicates: 0 Warnings: 0 mysql> select * from bug35640 order by Id DESC limit 10; +--------+------------------------+ | Id | SomeText | +--------+------------------------+ | 573906 | MINIL d.o.o. | | 573905 | MINIL d.o.o. | | 573904 | SALON GEL | --<cut>-- +--------+------------------------+ 10 rows in set (0.00 sec) mysql> select * from bug35640 order by Id DESC limit 10; +--------+------------------------+ | Id | SomeText | +--------+------------------------+ | 700000 | blah_blah | | 573906 | Passed here | | 573905 | MINIL d.o.o. | | 573904 | SALON GEL | --<cut>-- +--------+------------------------+ 10 rows in set (0.00 sec) mysql> > "As soon as the queprocessing hits a entry with multiple occurences in the que, the update fails with Can not identify row identity (80040e1b)" How do *you* distinguish from entries with multiple occurrences? Please modify my test case or attach complete one so I can see the problem.
[16 Jul 2008 23: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".
[12 Oct 2009 12:42]
Miroslav Ambros
Hi. I have got the same error.
I'm using mysql-5.4.2-beta-win32.msi together with mysql-connector-odbc-5.1.5-win32.msi. I was able to prepare test case based on Tonci Grgin's sample VB script.
How to reproduce it:
1. Create test table:
create table bug35640 (Id INT NOT NULL PRIMARY KEY, SomeText VARCHAR(100));
2. Insert one row (mainly because of "MoveFirst" call in VB script):
insert into bug35640 values (40001, "qwerty");
3. Run VB script attached - the second attempt to perform Update will cause this error.
The main difference to Tonci Grgin's sample VB script which works well:
* adUseServer instead of adUseClient
* removed .Properties("Update Criteria").Value = 0 (it caused an error when adUseServer was used)
* no cursor movements made between the first and the second Update() (there was "do while not rs.EOF" in Tonci's script which set the cursor position prior to finding record no. "573906" to update)
To say it simply: I want to use server side cursor and to perform two update operations on newly added row in a sequence.
Best regards
M.
[12 Oct 2009 12:43]
Miroslav Ambros
Test case 2
Attachment: bug35640.vbs (application/octet-stream, text), 1.70 KiB.
[12 Oct 2009 12:46]
Tonci Grgin
Miroslav, let me see...
[12 Oct 2009 13:14]
Miroslav Ambros
Correction:
The problem is not caused by second call to Update() but by setting the attribute to the new value after the first call to Update() was performed.
It seems like calling the first Update() after AddNew() doesn't preserve the current record position - so further attribute modifications doesn't know which record is current (thus causing that error).
Wscript.Echo "Inserting..." <--- creates new record and sets it as current record
rs.AddNew
rs("Id") = 700000
rs("SomeText") = "blah_blah"
rs.Update <--- I think this resets the current record position
Wscript.Echo "Updating..."
rs("SomeText") = "blah_blah_2" <--- this causes the error as no record is current
rs.Update
From MSDN article:
http://msdn.microsoft.com/en-us/library/ms677536(VS.85).aspx
...After you call the AddNew method, the new record becomes the current record and remains current after you call the Update method...
[12 Oct 2009 13:16]
Tonci Grgin
Miroslav, this is not specs problem, but SS cursor one... I'll check on this soon.
[14 Oct 2009 11:18]
Miroslav Ambros
There is a mistake in my example. There should be "Option=35" mentioned in connection string to allow opening dynamic recordsets at all (otherwise static ones will be opened and used instead of them). But even with "Option=35" and real dynamic recordsets the example keeps failing with the same "Can not identify row identity" message. A primitive fix: to insert line "rs.MoveLast" after the first call to Update()... :-)
[15 Oct 2009 12:07]
Tonci Grgin
Verified as Miroslav described:
Option Explicit
Const DSN = "Driver={MySQL ODBC 5.1 Driver};Uid=**;Pwd=**;Server=**;Database=**;OPTION=35"
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adOpenUnspecified = -1
Const adUseNone = 1
Const adUseServer = 2
Const adUseClient = 3
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004
'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200
Const adInteger=3
Const adDate=7
Const adVarChar=200
Dim cnxDatabase
Dim strSQL
DIM i
' connecting database
Wscript.Echo "Connecting..."
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)
Wscript.Echo "Preping..."
cnxDatabase.Execute("DROP TABLE IF EXISTS bug35640")
cnxDatabase.Execute("CREATE TABLE bug35640 (Id INT NOT NULL PRIMARY KEY, SomeText VARCHAR(100))")
cnxDatabase.Execute("INSERT INTO bug35640 VALUES(40001, 'TEST')")
Wscript.Echo "Opening recordset..."
Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
.ActiveConnection = cnxDatabase
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.CursorLocation = adUseServer
.Open("SELECT * FROM bug35640")
End With
rs.MoveFirst
Wscript.Echo "Inserting..."
rs.AddNew
rs("Id") = 700000
rs("SomeText") = "blah_blah"
rs.Update
Wscript.Echo "Updating..."
rs("SomeText") = "blah_blah_2"
rs.Update
Wscript.Echo "Closing..."
rs.Close
Set rs = Nothing
Set cnxDatabase = Nothing
[3 Dec 2010 8:58]
Bogdan Degtyariov
verified with MyODBC 5.1.8
[24 Dec 2011 13:39]
Louis Breda van
Valeriy, Sorry I did not replay quicker. The reason for that is that I planned and did a quick test if the problem is still there first. The chinese chacacter problem is related to text fields: - it you select the textfield within mysql it contains normal latin characters - it you access the textfields from within msaccess using VBA it looks ok as well - if you create an ODBC-link to mysql and link the table from the msaccess gui, a lot of times you see chinese characters The problem is there for years and many people including myself have complained about it. The problem is not related to every DB and not always present but frequent. - I can not tell you what is exactly triggering the chinese character problem. - I can not solve it (I tryed eg. changing the character set in the ODBC-connection to udf8, dispite there was no reason to do so, but that does not help) I did the quick test based on old DB's (perhaps having older INODB table formatting), using 5.6.4 64 bit togeter with latest 32 bit ODBC-driver and access 2010 32 bit. One of the bug numbers is #59637 but there are a couple of them, and If I remember correctly I have sees complains dated as early as 2005. Of course it is possible that there is also a long time problem within msaccess, which is triggerd by mysql-odbc connection for some reason. What ever it is anoying, since msaccess (frontend) + mysql (backend) is ideal to do some quick querys and reports. IMHO far more confortable that the MySQL WB. Sincerely, Louis As always of course willing to help debugging the problem (as far as I can do that with reasonable effort, and always based the mysql versions I am curretly running)
Description: Hello, Situation: - need to insert a que of entrys in the "entry" table (entryid + datafields) - entryid is the PK - some entrys have more than one entry in the que - the que is sorted on entryid - latest / most recent entry should be in the table, after processing of the que - table can be emty at start, but also contain data form earlier batches Now proces the que and - create (addnew) an entry if it does not exist or - update an entry if it did exist or - update an entry after an addnew or after a first update As soon as the queprocessing hits a entry with multiple occurences in the que, the update fails with Can not identify row identity (80040e1b) Programm runs without problems when using msaccess via an equvalent odbc connection. Sincerely, Louis How to repeat: latest mysql 5.1 version (more precise special build because 64 bit memory problem). INODB Entrys.Open "SELECT entrys.* FROM entrys ORDER BY entryid;", cn_mysql, adOpenDynamic, adLockPessimistic or optimimistic (equal behavoir) - determine if the entry did exist if not add else update * so far so good * - now the entry was two times in the que first entry: cn_mysql.starttransaction rsEntry.addnew ==> OK cn_mysql.commit second entry: sn_mysql.starttransactio rsEntry!blabla = blabla => *** ERROR Can not identify row rsEntry.update cn_mysql.commit Note that the problem occurs: - when using either lock optimistic or pessimistic - when commiting each record and also when commition lets say every 100 records.