| Bug #28016 | Select Query has missing rows when PK? not included in selection | ||
|---|---|---|---|
| Submitted: | 22 Apr 2007 11:41 | Modified: | 27 Jun 2007 19:15 |
| Reporter: | Louis Breda van | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | MyODBC 3.51.14 | OS: | Microsoft Windows (VISTA64) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ODBC | ||
[16 May 2007 8:43]
Tonci Grgin
Louis, this should be connected to your other report about SQSetPos and Jim's report on malformed WHERE... Can you please attach relevant part of general query log from server so that I can see what actually got to it. Btw, I doubt it's Vista and/or x64 related.
[16 Jun 2007 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".
[17 Jun 2007 22:02]
Tonci Grgin
Louis, what's with my feedback?
[18 Jun 2007 4:52]
Louis Breda van
Tonci, I have been very busy. I simply did not have time for trying to port the DB towards mysql. I have the intention however, to do some tests with 5119 and the most recent ODBC-build as soon as I can find some time. Hopefully in the comming week. Sincerely, Louis
[18 Jun 2007 7:01]
Tonci Grgin
Louis, no problem. It was just unlike you to leave report to expire. Can you please test with 3.51.16 as we made numerous changes to this release: http://dev.mysql.com/get/Downloads/MyODBC3/mysql-connector-odbc-3.51.16-win32.zip/from/htt... and come back with results?
[20 Jun 2007 11:01]
Tonci Grgin
Louis, I used 10 field subset from 18 field table for this test. Table contains ~200000 rows and is about 70MB on disk (20+MB indexes). All works fine in my test case. What you seeing is related to UPDATE not updating field as it has same value (AR = 0). Try catching the error in rs.Update and move forward or something... The best I found on web is that you should check for whether UPDATE will do actual update or not. 13 Query USE test 13 Query SELECT Rbr, Godina, Tip1, Datum1, Datum3, Konto, Dug, Pot, Status, Opis, Marker FROM bug28016 WHERE Godina = 2006 AND Tip1 = 'IRA' ORDER BY Rbr 070620 11:03:00 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172423 070620 11:03:19 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172424 070620 11:03:21 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172425 070620 11:03:31 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172426 070620 11:03:33 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172427 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172428 070620 11:03:34 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172429 070620 11:03:35 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172430 070620 11:03:55 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172431 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172432 070620 11:03:56 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172433 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172434 070620 11:03:57 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172435 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172436 070620 11:03:59 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172437 070620 11:04:00 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172438 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172439 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172440 070620 11:04:01 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172441 070620 11:04:07 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172442 070620 11:04:11 13 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172443 070620 11:04:16 2 Query show processlist 070620 11:06:27 2 Query select * from bug28016 WHERE `Rbr`=172432 070620 11:07:40 2 Query SELECT dnevnik.Rbr FROM solusd.dnevnik WHERE dnevnik.Godina = 2006 AND dnevnik.Tip1 = "IRA" AND (Datum3 IS NULL OR Datum3 = '0000-00-00') ORDER BY dnevnik.Rbr 070620 11:08:02 14 Connect root@localhost on test 14 Query SET SQL_AUTO_IS_NULL=0 14 Query select database() 070620 11:08:03 14 Query SELECT @@tx_isolation 14 Query USE test 14 Query SELECT Rbr, Godina, Tip1, Datum1, Datum3, Konto, Dug, Pot, Status, Opis, Marker FROM bug28016 WHERE Godina = 2006 AND Tip1 = 'IRA' ORDER BY Rbr 070620 11:08:06 14 Query UPDATE `test`.`bug28016` SET `Marker`='Z ' WHERE `Rbr`=172423 << Already updated! Error 070620 11:08:23 14 Quit
Description: Hello, I discovered that rows where "missing" when doing a select on a large table, containing a selection of fields. The RS is sorted based on part of the selected fields. A couple of rows can have equal values within the selected fields. One or more of the selected fields kan be NULL. The behavoir I noticed is that either 1) rows are returned which are readable, but not updatable or 2) rows only seems to be returned, and are ofcourse not updatable One thing is for sure, the "missing" rows do exist in the DB I can not disciminate between situation 1) and 2) since the selected fields in the rows can be equal, and from reading that field I can not tell in it is row instance 1/2/3 etc. However since I do not get an error when reading a RS-field, 2) is more likely Trying to identify the uniek row related to the problem, I did also select the not needed PK. That however took the problem away :> Note that I had to modify a couple of selection querys to workaround this problem. Sincerely, Louis not that those querys used to work when using another DB-type (ms-access) How to repeat: Create a large table (x 100K rows) mytable containing fieldsinstances which are partly filled and parltly null SQL_Select = "SELECT mytable.field1, mytable.field2, mytable.field3, "mytable.field4 " + _ "FROM mytable " + _ "WHERE mytable.field2 = 'ape' " + _ "ORDER BY mytable.field3;" RS.Open SQL_Select, cn, adOpenDynamic, adLockOptimistic simplified: while RS.EOF = false RS.movenext testvar = RS!fieldx 'that is probably OK check conditions RS!field = xyz RS.update <== sometimes!! failes! there is no record to update look that way at least wend RS.Close