View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0006956||VCOMPONENT-VKERNEL||SQL||public||2014-10-19 15:22||2015-03-05 09:27|
|Reporter||Steve Albin||Assigned To||Ruslan Zasukhin|
|Platform||INTEL||OS||MAC OS||OS Version||*|
|Target Version||5.7.5||Fixed in Version||5.7.5|
|Summary||0006956: Phantom Index records on specific field - may cause duplicate record return|
|Description||SQL is returning duplicate records on query|
In this specific case, each Perf record is getting repeated 5 times.
The Session table is not being reported on for this SQL, but the pointer for the session record is being used to narrow the search. In other words, only perf records for a specific session are being reported on.
In my diagnose, one of the session table fields (dSessDate) is reported as having phantom index records. Possibly, these index records are causing the repeat.
|Steps To Reproduce||Database is too big to submit to Mantis, but can be found on my website here:|
Use this SQL:
Select Perfs.RecID, Perfs.nSongPtr, Perfs.nSessPtr, Perfs.cHours, Perfs.cMinutes, Perfs.cSeconds, Perfs.lMedleyFl, Perfs.nTakeTypePtr, Perfs.cMatrix, Perfs.nPerfRef, Perfs.nMedleyId, Perfs.dRecCreation, Perfs.dRecUpdate , Songs.cTitle, Songs.Recid, TypeTake.cTypeAbbrev, TypeTake.cTypeName
FROM Perfs, Songs, TypeTake
WHERE TypeTake.Recid = Perfs.nTakeTypePtr
AND Songs.Recid = Perfs.nSongPtr
AND perfs.nSessPtr = 30746
ORDER BY perfs.nPerfRef
|Additional Information||I don't know that this is a Valentina bug. It may very well be something I am doing wrong in the way I'm adding or deleting records. Or maybe my schema is in error. |
I'm submitting this because Ruslan suggested it via the Valentina email list.
|Tags||No tags attached.|
It would be nice to know if anyone is even looking at these reports.
As it turns out, this problem has occurred again for my user, this time on a database reporting no problems in the diagnose. Also, I've upgraded to version 5.5.8 with no success in fixing this issue.
Do it just now. Confirm that
SELECT Perfs.RecID, TypeTake.RECID, Perfs.nTakeTypePtr
FROM Perfs, TypeTake
WHERE TypeTake.Recid = Perfs.nTakeTypePtr AND perfs.nSessPtr = 30746
-- ORDER BY perfs.nPerfRef
-- return 77 records (7 groups of the same records)
SELECT Perfs.RecID, Perfs.nTakeTypePtr
WHERE Perfs.nTakeTypePtr = 5
AND perfs.nSessPtr = 30746
-- 11 records
It seems something wrong with index. Can't be more particular for now.
Thanks for confirming the problem. I have more information.
If you change any one of the returned Perfs records to have a nTakeTypePtr <> 5 then that record only gets returned once. (Valid nTakeTypePtr is 1 - 5.)
Also, reindexing the database does not fix this problem. Cloning does fix the problem but it will come back eventually.
I have previously noted on the Valentina list where I have had databases with corrupt indexes that would not be fixed via a reindex, but would be fixed if I deleted the .ind file and reopened the database. Unfortunately, this particular database uses the mode where the index and data are one file.
This was very old, and rare enough bug. At least 10+ years.
Problem was that on Index Create we did not zero space for its Header, and more important for the first Inventory page. This did break one of algorithm of work with Big pages.
|2014-10-19 15:22||Steve Albin||New Issue|
|2014-10-27 14:01||Steve Albin||Note Added: 0007942|
|2014-10-27 16:22||Ivan Smahin||Note Added: 0007943|
|2014-10-27 22:30||Steve Albin||Note Added: 0007950|
|2014-10-31 09:39||Ivan Smahin||Assigned To||=> Ruslan Zasukhin|
|2014-10-31 09:39||Ivan Smahin||Status||new => assigned|
|2015-03-05 09:24||Ruslan Zasukhin||Target Version||=> 5.7.5|
|2015-03-05 09:26||Ruslan Zasukhin||Note Added: 0008081|
|2015-03-05 09:26||Ruslan Zasukhin||Status||assigned => resolved|
|2015-03-05 09:26||Ruslan Zasukhin||Fixed in Version||=> 5.7.5|
|2015-03-05 09:26||Ruslan Zasukhin||Resolution||open => fixed|
|2015-03-05 09:27||Ruslan Zasukhin||Project||ADK-Xojo-V4RB => VCOMPONENT-VKERNEL|