Pack Not "Deleting" All Records Marked For Deletion

[TotalBeginner]

In a table I have 61050 records, 58859 Not marked for deletion and 2191 Marked for deletion. I run a pack and then a reindex.

Afterwards there are 59708 records, 59708 Not marked for deletion and 0 marked for deletion.

Obviously these figures don't add up.

Looking into the table it is clear that some records marked for deletion are not getting deleted. I have checked this and this is the case.

Why would records marked for deletion not get deleted and further more just get the deleted mark against them removed thereby reinstating the record?

You can only image the havoc this has caused!!!!

 

[Olaf Doschke]

Never heard of this happening in 4 years of using foxpro, but some time is always the first time.

Hmm, how did you count?

select count(*) from table where deleted()
select count(*) from table where not deleted()
select count(*) from table
* vs.
select table
count for deleted() to lnCountDeleted
count for not deleted() to lnCountUndeleted
count to lnCountAll

Maybe some defect in the table caused this to happen, you never know...

Do you still have a backup of the unpacked version?
maybe try

select table
copy to someother.dbf database samedatabase for not deleted() with cdx

and then delete the old table and rename the new one...

Bye, Olaf.

 

[TotalBeginner]

Counted using:
"COUNT FOR NOT DELETD() TO z_not_deleted_count"
And
"COUNT FOR DELETED() TO z_deleted_count"

The two tally with the VFP Record Count.

 

[Olaf Doschke]

...and compared with the result of SQL-Statements?
other methods of counting?

select table
? reccount()
index on deleted() tag hamlet && to be or not to be
set order to hamlet
set key to .t.
lnCount=0
scan
  lnCount = lnCount+1
endscan 
? lnCount
set key to .f.
lnCount=0
scan
  lnCount = lnCount+1
endscan 
? lnCount

* or just a full table scan
select table
set order to
set key to
set filter to
lnCountDeleted = 0
lnCountUndeleted = 0 
lnCountall = 0
scan
  lnCountDeleted = lnCountDeleted+iif(deleted(),1,0)
  lnCountUndeleted = lnCountUndeleted+iif(deleted(),0,1)
  lnCountall = lnCountall +1
endscan

If you always get the same figures *shrug* ???
All of those counts depend on deleted() to return
a correct value. If that is not given you may
have consistent values, but still pack doesn't
delete the records you think it would.
If you still have this table, maybe go to a record
that is shown deleted in a browse (of course
SET DELETED OFF is needed anyway) and then
? deleted(), does that return .T. or .F.
despite of the deletion mark in the browse set?

Maybe someone else has experienced such a problem?

Bye, Olaf.

 

[Olaf Doschke]

If you still have this table, maybe go to a record that is shown deleted in a browse (of course SET DELETED OFF is needed anyway) and then ?deleted(), does that return .T. or .F. despite of the deletion mark in the browse set?
[/quote me myself]
especially on a record that remains after pack.

Bye, Olaf.

 

[Olaf Doschke]

if you already had an index on deleted(), a possible explaination would be, that pack() used that index. If that index was defect, it could result in records remaining, that were deleted and records being deleted that weren't. In that case it would be better to drop that index before packing or Setting optimize off.

Just a guess, though, as I don't know if pack would use rushmore optimization for finding the deleted/undeleted records as it has to do a full table scan anyway.

Bye, Olaf.

 

[Mike Lewis]

TotalBeginner,

The two tally with the VFP Record Count

What record count? RECCOUNT()? If so, that gives you the number of physical records, including deleted ones. Is that what you are seeing?

A couple of other possibilities:

- Is there a SET FILTER in force when you did the counting?

- Are the tables buffered, and have you deleted or recalled any records since the last time you committed the buffer?

Either of the above could give a misleading reading.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[TotalBeginner]

Mike

D Hallam Here! Under Wendy's Alias.


The Tally Count is from that displayed in the Data Session Window. No buffering etc. No Filter etc. Just a table showing x deleted and y not deleted so when a pack is done I am expecting to see y as the total record count in the Data Session window - not z!

I can also see the records marked for deletion before the pack and then after the pack not marked for deletion!

I think it is something to do with the fact I have a DELETED() index on the table but I haven't narrowed it down as yet?

Weired problem though?

 

[TotalBeginner]

OK Guys This Is The Latest:

Using the same code:
COUNT FOR NOT DELETED() TO z_not_deleted

Before I removed the DELETED() Index On the Table the count is 58859

After I remove the DELETED() Index On The table the
count is 59708

Looking at the data and what actual records are marked for deletion the before figure is correct.

So it is definately something related to do with the index. But how can an index effect a pack?

 

[Mike Lewis]

D.

D Hallam Here

Hardly a "total beginner".

Before I removed the DELETED() Index On the Table the count is 58859
After I remove the DELETED() Index On The table the
count is 59708

I don't get it. Definitely something weird here.

Anyone else got any ideas?

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[jimstarr]

Is the index on DELETED() an .IDX? Perhaps it's out of date.

Jim

 

[TotalBeginner]

No the index is a CDX.