View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0006956VCOMPONENT-VKERNELSQLpublic2014-10-19 15:222015-03-05 09:27
ReporterSteve Albin Assigned ToRuslan Zasukhin  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
PlatformINTELOSMAC OS OS Version*
Product Version5.5.5 
Target Version5.7.5Fixed in Version5.7.5 
Summary0006956: Phantom Index records on specific field - may cause duplicate record return
DescriptionSQL 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 ReproduceDatabase is too big to submit to Mantis, but can be found on my website here:
http://www.jazzdiscography.com/Temp/PhantomIndexRecords.zip

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 InformationI 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.
TagsNo tags attached.

Activities

Steve Albin

Steve Albin

2014-10-27 14:01

reporter   ~0007942

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.
Ivan Smahin

Ivan Smahin

2014-10-27 16:22

developer   ~0007943

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
 FROM Perfs
 WHERE Perfs.nTakeTypePtr = 5
 AND perfs.nSessPtr = 30746
-- 11 records


It seems something wrong with index. Can't be more particular for now.
Steve Albin

Steve Albin

2014-10-27 22:30

reporter   ~0007950

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.
Ruslan Zasukhin

Ruslan Zasukhin

2015-03-05 09:26

administrator   ~0008081

This was very old, and rare enough bug. At least 10+ years.
wow...

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.

Issue History

Date Modified Username Field Change
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