Checking .CDX Files & there tags

[mstrcmtr]

How can check the .CDX files & there tags are okay and they are not corrupted in start of Program in Main.Prg

 

[Olaf Doschke]

This will take much too much time, as you'd need to check each indexed value and see if it SEEKs back to the record(s) the value comes from.
What you should do as maintainance routine from time to time is reindex.
If you have very regular index defects, look into oplocks and SMB2, they were a reason for that for my customer and turning oplocks off is a solution to that.

Bye, Olaf.

 

[Mike Lewis]

I don't see why this should be necessary. Are you also going to check that all your DBFs are healthy? That there is no corruption in your memo files? That everything else in your environment is as it should be?

If you have some special difficulties with CDXs, better to track down what's causing the problem and eradicate it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

There's no way to check the index for corruption. However, if you experience corruption, I suggest that you prioritize finding out why this happens, there's always a reason.

 

[Dan Freeman]

The reason it's impractical to check an index for corruption is that most corruption happens in the header. If you ask an index "are you OK?" it will look at the header and say "yep!"

You can query ATagInfo(), but that just tells you what the index "knows" about itself. If the index is corrupted chances are good what the index "knows" about itself will be corrupted too.

The BEST way to handle indexing is to use external metadata that can create your specified indexes on call. (Stonefield Database Toolkit is an excellent product for this.)

And I'll agree with the others. If you have frequent problems with index corruption you should find the cause and fix it.

 

[Olaf Doschke]

I always liked the idea to store healthy headers, eg right after creating or altering a dbf, as backups for later restoring them. The header of a dbf changes in the one aspect of the reccount, I'm a bit unsure whether the fpt header changes with new fields stored into it, but obvioulsy any table alteration would trigger a new backup of headers.

SDT is very helpful, also the defacto non MS standard of DBCX (established long before VFPX), but you can of course easily detect header defects by comparison with backed up healthy headers. In still healthy DBF headers - for example - you only find the reccount bytes differ from the original header.

I had a situation with oplocks and very regular, almost reproducable cdx defects, they were not in the header section, they were in nodes. Anyway, you can DELETE TAG ALL and then recreate indexes rather than REINDEX, but you can also restore empty CDX files of a new DBF and REINDEX that. To update the empty CDX after a table alteration you can make a table copy with COPY TO without data and store that empty CDX file as seed for a healthy REINDEX, too, to regrow your index trees from.

Bye, Olaf.

 

[dbMark]

If an index is corrupted then you may have to rebuild it. But don't call "INDEX ON ... TAG ..." repeatedly because it can grow the physical size of the index, sort of the way FPT and PJT files can grow over time. I found code someone had written that copied in refreshed DBF tables and then since the indexes were outdated, the writer recreated them as though they weren't there. Would have worked except the CDX files were still there. Over time the indexes grew, tacking the new indexes onto the CDX file. Several were over 100MBs, one was over a GB! REINDEX fixed them, brought them down to 100KB size. So if the CDX is there and can be updated (not corrupted) then use REINDEX.

 

[Dan Olsson]

If you do REINDEX with corrupted indexes, the result could be wrong (or so I've heard)
DELETE TAG ALL followed by INDEX ON ... TAG ... always works

 

[Mike Lewis]

DELETE TAG ALL followed by INDEX ON ... TAG ... always works

That's right. But it assumes you know what the tags were in the first place (it would be no good in a generic recovery routine). Hence the idea of saving meta data.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Olsson]

Meta data - yes, one should always have that.
I never use "USE" directly in my production apps.
I have a special Open() routine that checks my meta table to see if dbf structure is changed and rebuilds it accordingly (of course only if I can open it EXCL).
Also there is a place to put commands to run after rebuilding, and naturally all the index tags needed.
With parameters I can choose EXCL, IN 0, READONLY, ALIAS, ORDER (with number or name).

 

[Olaf Doschke]

Besides you need to know index definitions to redo them after DELETE TAG ALL, you can't create indexes over and over again with same names. You can decide to leave the already existing tag or overwrite it. That indeed may not be very clean and lead to bloat, but on a healthy header a REINDEX does ZAP the cdx and recreate it from tag infos from scratch, which is clean as long as index info is healthy.

Just to state the obvious - DELETE TAG ALL obviously is clean, as it deletes the CDX and all references to it in either DBF (not sure whether there are any) and DBC/DCT for database tables. As a simple proof see this:

Create Cursor crsTest (iiD I)
? File(Forceext(Dbf(),"cdx")) && .F., no index yet
Index on iid tag xide
? File(Forceext(Dbf(),"cdx")) && .T.
Delete Tag All
? File(Forceext(Dbf(),"cdx")) && .F., cdx file deleted

So you can be very sure after DELETE TAG ALL newly created indexes are built from scratch and thus not bloated and healthier. Stonefield Database Toolkit does that via the info stored in it's DBCX meta data. You can also go with GenDBC generated code, which includes the code creating index tags both in the CREATE TABLE part of creating the DBF and via following INDEX commands. So you can also store "meta data" in the form of code needed to rebuild the indexes. It's always a good thing to have. When you create dbfs manually via designers and neither have a backup of their healthy initial empty states nor create creation code as aftermath via GenDBC or such tools, you only have your idea about the DBFs to reproduce them, you depend on corrupt files be fixable and that's not necessarily the case.

Bye, Olaf.

 

[Mike Lewis]

it deletes the CDX and all references to it in either DBF (not sure whether there are any) and DBC/DCT for database tables

As I understand it, there is no information about the indexes stored in the DBF, even for free tables. VFP knows about the CDX file, either because it is a structural index (and has the same name as the DBF), or because you specify it in the USE command.

But I'm only mentioning this for interest. It doesn't detract from the main argument concerning meta data.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Olsson]

Well, I beg to differ If one USEs a dbf which used to have an associated cdx, VPF tells you that this association is broken, so that must be stored in the dbf header

 

[Tore Bleken]

Right, byte 28 in the DBF's header is 0x01 if the file has (or should have...) a cdx.

https://msdn.microsoft.com/en-us/library/aa975386(v=vs.71).aspx

 

[Mike Lewis]

That's nit-picking. Byte 28 tells you if the DBF has a structural CDX. It tells you nothing about the index tags, or about any non-structural CDXs.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

A bit nit-picking, but DELETE TAG ALL has to care for that DBF byte. And I know there are records about indexes in a DBC with objecttype "index", objectname being the tag name. Im mentioned the DELETE TAG ALL does make changes there, too, as it woiuld otherwise be much easier to just delete the CDX file, but that leads to errors.

You know, people get bad ideas, if they see my sample code deletes the CDX file, they might think they'll fix a broken cdx by just deleting it.

Bye, Olaf.

 

[Mike Lewis]

Yes, I agree. My point about the DBF header was that it does not contain any information about the actual indexes, so if you were going to DELETE TAG ALL / INDEX ON ... TAG, you would need to have either some meta data or some knowledge of the indexes.

It's true that you can get the tag names from the DBC (assuming the table isn't a free table), but I can't see any way of getting the index expressions from there - unless ATAGINFO() can give you that? I'm not sure.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

No, the only place of the index expressions is the cdx header. That's why it's important to have either meta data or code or both for regenerating tables and indexes, if you expect the info stored in the header to get corrupt. And you should always expect the worst things happening, murphys law.

Bye, Olaf.

 

[Dan Freeman]

But more important than figuring out where to get index expressions is finding the culprit and killing it.

 

[Olaf Doschke]

Well, we had a lnegthy side topic discussion, yes.

The culprit seems to be oplocks, as seen in thread184-1761050

Bye, Olaf.