OPEN DATABASE asks "Index does not match the table. Delete index file and recreate the index&qu 1

[dkean4]

I can deal with this when I know which table's Index is blown. But here I have close to 60 tables in a DATABASE and I have no clue if it is complaining about a table index or the DATABASE index. REINDEX DATABASE seems to take a few seconds and does not complain when it is done. So, it must be a specific table, but the DATABASE cannot give me the list of tables so I can quickly test opening each table. I don't want to disconnect the tables and lose the long field names etc...

Any suggestions to speed this up would be appreciated.

Dennis Kean

Simplicity is the extreme degree of sophistication.
Leonardo da Vinci

 

[TamarGranor]

If you can't open the database with OPEN DATABASE, you can still open it with USE:

[pre]
USE MyDatabase.DBC
[/pre]

It's just a table, so you can go through the rows and find the ones that are for tables and check them out.

Tamar

 

[dkean4]

Yes, thank you Tamar.

The DATABASE is trashed really bad. I have to recreate it. The trouble is that I do not know how to transfer the tables to the new DATABASE without losing the field names. Any ideas about that? Is there a way to modify them directly to be accepted by the new DATABASE?




Dennis Kean

Simplicity is the extreme degree of sophistication.
Leonardo da Vinci

 

[dkean4]

Okay, I guess no one else has experienced this DATABASE crash problem. And it is a pain to piece back together. Here is some help for others who suffer this demise.

If the crashed DATABASE has not trashed all the tables extract the good listings of your tables into a list like this and remove the Old DATABASE from the subdirectory where it used to reside.

Copy what is available and add to the list the names of the remaining tables, as per your need.

Now create a new DATABASE name it and make changes in the code to suit your needs.

The code should be self-explanatory. The error trapping is used to make the process fluid. It will prompt you for each table if you want to Delete the link to the old DATABASE. Choose "Delete" and it will continue questioning you until you answer the prompt for each table. It saved me time...

OPEN DATABASE CLIP

LOCAL Terr
CLOSE TABLES ALL 
USE tbl_list

SCAN
	CMD = ALLTRIM(name)+".dbf"
	TRY 
		ADD TABLE &CMD
	CATCH 
		Terr = .T.
	ENDTRY 
	IF Terr
		USE &CMD IN 0 EXCLUSIVE 
		Terr = .F.
		SELECT TBL_List
	ENDIF  
	DELETE 
ENDSCAN

Dennis Kean

Simplicity is the extreme degree of sophistication.
Leonardo da Vinci

 

[Scott24x7]

Dennis,
Though this probably doesn't solve your current issue, it's more about long term maintainability. I have always avoided the use of REINDEX for exactly this reason. If a table gets corrupt, and you have to rebuild it, and the indexes are deleted, if you don't have somewhere where those definitions exist, you're in deep. What I do instead is a routine that does a Pack & Reindex in my system maintenance. There, every table is opened individually, and their indexes are discarded then rebuilt all over again with "INDEX ON" statements. This may seem a little tedious, and requires good maintenance of the code (so when a table changes, index is added or removed, or condition change), then you need to update that. But after you spend hours (days) fiddling with the issue above, it's a small price to pay to maintain such a process. And then you don't need to worry about the indexes because they can always be rebuilt. It's better to remove old indexes before packing as well.

Just my 2 cents on how to make dealing with this issue less burdensome.


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

If you don't have a backup you disappoint me, Dennis. There's a reason for backups, especially also of the DBC/DCT/DCX files. If a DBC doesn't change all that often, an older backup would do, the data of the DBC is almost static, ideally it is completely static, but of course from time to time you extend your data with new fields or tables. For a stable DBC you should refrain of daily adding and removing temporary tables or indexes or fields etc, that wears the DBC files as any other DBF, but if it's static nothing happens to it corrupting it.

Just restoring the DBC/DCT/DCX and all your tables are back in it.

When you now CREATE DATABASE new.dbc and use ADD TABLE to add back all tables you have them with shortened names, you might get an error, as they are not free, they have their DBC back reference.

The only place where you find your long field names and default values and some other stuff is the DBC, there is no redundancy about the metadata when you don't create it by either backup or the other opportunity: gendbc. Gendbc is creating a script to recreate your dbc. In cases, you forget to let it be up to date by redoing it after each dbc version upgrade you have a better chance in adding latest modification in its code than you have restoring an outdated dbc/dct/dcx file triple. That's why that is preferable, but also more work to just copy these files in backups. One thing bad about GenDBC is it doesn't preserve any subfolder structure you might have for your tables. I think gendbcx does that, it's in vfpx (github) but too late for this case, just a tip for the future. It obviously also doesn't take into account any free dbfs you have with some extra config or other data and any different files and file types you may only refer to in your data by a path.

And no, REINDEX is not bad. Yes, if CDX header is broken, REINDEX won't save it. It's not a metadata repair. But even if you just backup the cdx files of empty tables, right after their creation, or ANY state of them, that's healthy, you can copy that back and REINDEX and your indexes are complete and up to date again. In case of DBC you don't need that DCX backup, as the indexes are defined equally for all DBC, it is one and the same table strucure of all DBCs, all headers of dbc and dcx files of VFP databases are 1:1 equal Just the FPT file (DCT) has no static header, that's simply because already the first 4 bytes point to the first free block and that's not static data.

So you can just create a new DBC and take it's DCX file to your corrupt DBC and REINDEX it while you opened it by USE your.dbc, your index data is repaired. If that's the only thing tha was corrupt you can get back there. At least if you still have a copy of the initial state of your corrupted dbc before you began your repair efforts.

All that should be really known to regulars, as I and others more than once gave all these tips.

Bye, Olaf.

 

[dkean4]

Scott24x7

Thanks for the tip, Scott. Fortunately, I was able to retrieve the list of tables and fields of some tables which went down. This is my first crash of this sort. I used to snicker hearing about others recovering and now it is me. This PC has been a monolith in my long string of PCs. But all good things come to an end. Your advice is appreciated. Interesting that INDEX ON reindexes. What happened to me was the DATABASE MEMO file corrupted. I have been making hundreds of changes to the table's structure and having 3 monitors one of which is a 4K monitor, VFP started crashing when I come back from sleep. I have plenty of RAM and disk space, I can't think of anything other than hardware failure, but who knows.

Thank you for the tips...

Dennis Kean

Simplicity is the extreme degree of sophistication.
Leonardo da Vinci

 

[Olaf Doschke]

>Interesting that INDEX ON reindexes

It overwrites a tag, if you have SET SAFETY ON, it'll ask before doing that. You need the index expression to "reindex" via INDEX ON, that's why maintaining such a routine Scott uses is some more work than keeping a healthy CDX, which as I said would even suffice, if it's the initial empty CDX, as long as the table doesn't have new index tags. All REINDEX needs for a helthy reindex is the index expressions, and they are in the CDX header. That's all that needs to be intact. Only of that is broken REINDEX doesn't work. there's always the argument Text files never break. Well, any bytes sotred on HDD normally don't change, that doesn't work better for bytes in the range of ASCII letters.

Bye, Olaf.

 

[dkean4]

Olaf you old warrior, my backup stopped working for some forsaken reason which I know not, on March 28. I have a 4 TB backup system, but I do not look at it regularly to make sure. It was set to back up every day. My fault for assuming. I think that it did give me a warning and I did not attend to it immediately.

When you now CREATE DATABASE new.dbc and use ADD TABLE to add back all tables you have them with shortened names, you might get an error, as they are not free, they have their DBC back reference.

You are right and I know this from the past, but the method I used above has preserved all the long names. I have no clue why. I am totally baffled. Fortunately, using the USE DATABASENAME.DBC also listed most of the tables and their field names. So, within one hour, I was back online. That is why I sent that snippet for you guys, curious if you could figure out why and wherefore the field names were not trimmed down to 9 characters as I fully expected it to do when deleting the DB reference. There is nothing that gets past you, Olaf, but this was a miracle in my book. I could have spent a full week recovering and rewriting the details. One table, however, has bombed on one field named "gene_string" (11 chars) and I had to recreate that whole table because it would not open, the worst part being redoing the field name Descriptions in the Table header.

So, miracles do happen, I guess... Now to fix the backup...

Great advice Olaf... Merci...

Dennis Kean

Simplicity is the extreme degree of sophistication.
Leonardo da Vinci

 

[Olaf Doschke]

No idea how you would get back long names, do you even have long names in your DBFs now? Are there any field names longer than 10 chars?

USE your.dbc alias yourdbc
SELECT Objectname FROM yourdbc WHERE Objecttype="Field" HAVING LEN(ALLTRIM(Objectname))>10

Any fields showing up?

There are more things than long field names in a DBC that you still lost, for example any default values, except some things like the auto increment of integer autoinc fieldsm that comes from the field type described in the DBF header).

Of course, it's possible to get back tables into a dbc that way, if you never made used of DBC features, but those are lost, very surely. You didn't find any magic solution.

Bye, Olaf.

PS: Modify to exactly =10 and filter for names including tilde (I think), there you'd have your changed field names.

 

[dkean4]

OLAF

Happy, Happy, Joy, Joy... form Wren and Stimpy!! Secondary Backup... That is where the game is at. I found another Backup system installed years ago and promptly forgotten! ARRIVA!


Great idea, Olaf...

So you can just create a new DBC and take it's DCX file to your corrupt DBC and REINDEX it while you opened it by USE your.dbc, your index data is repaired. If that's the only thing tha was corrupt you can get back there. At least if you still have a copy of the initial state of your corrupted dbc before you began your repair efforts.

It will be useful in the future...

Cheers...


Dennis Kean

Simplicity is the extreme degree of sophistication.
Leonardo da Vinci

 

[dkean4]

Olaf

Your last post came after my last post....

Yes, Olaf, the DBC contains all the fields from all the tables and I only lost one of many. In my function, it asked me to delete the backlink when I tried to ADD the table to the new DB, so it could replace it with the new DATABASE backlink. I stumbled into that by accident. If you try to USE the table first and answer "DELETE BACKLINK", then you lose the extra characters in the field names because it strips the extras to allow you to use the table without a DATABASE.

I think that the VFP team did some good thinking there... Direct ADD to the new DB is how the long names are saved. And yes, Olaf, I do have many long names and they were all except that one field in a specific table. The reason was that I did USE that table accepting the "DELETE BACKLINK" during the USE XXXX.dbf command. So, you are right... No magic!

I think that we both learned something here. Mon chapeau to the MicroSoft team.

And thank you for your assistance, Olaf.

Dennis Kean

Simplicity is the extreme degree of sophistication.
Leonardo da Vinci

 

[Olaf Doschke]

Well, no, I can't belive that, long names are only stored in a DBC, if you started with a new empty DBC there are no long names to recover. Where should they come from, the DBF header contains the short field names.

So your situation still is unclear. Was the old DBC files also still existing? you only have long names there. The only other place I could think of would be foxuser.dbf, it stores things how you last browsed a certain alias, but IIRC rather only window position and column widths, things like that. Captions? Or even field names?

The long names can't come from the DBF alone. If you USE the DBF and it indeed finds its DBC the long names come from there, they only are stored there. But then ADD TABLE would fail, because the table already belongs to its DBC. Your code then changed nothing for the DBFs.

Bye, Olaf.

 

[dkean4]

Olaf

For me, it is not an issue of belief. I did it. My thoughts on that are that the tables must have the extra info residing in that backlink. If the link is indicated when you try to ADD the table to the DB it might carry the extra data in the link. Otherwise, it would not ask you to remove it. I did look into a linked table file and I was not able to find the extra data contributing to the field names. Possibly that the FOXUSER.DBF is used for that. I leave it to you to figure it out. I have both recovered my files from a backup this morning and last night I restored the Database.

So, we must have a true miracle on our hands. Back to miracles...


Dennis Kean

Simplicity is the extreme degree of sophistication.
Leonardo da Vinci

 

[Olaf Doschke]

No, the issue is not belief, it's simply impossible. What you did must be something you onyl realized halfways. You can't get long names from DBF only, also when you USE the DBF, that doesn't change the info in the DBF header.

If you USE a DBF without error, browse it and see long names, then it still has it's DBC and uses that. What I don't believe is that you only had the new empty DBC. I don't believe what you said. I think you don't lie intentionally, but you didn't realize that what you did was more like hot air, nothing really, the DBF still were intact, their DBC still was there and still was having the long names. The long names don't come from anywhere else but the DBC. And a new DBC has no info on old DBFs.

Then what you finally did is something I also think would always end in the catch block, you tried to add a table to the new DBC, while it still was part of the old DBC, you can't switch databases association of DBF files that way.

This is the data in a DBC with a table with long field name:

And this is the DBF header in Hexedit:

If you delete the backlink and accept your table to become free, nothing is shortened in the DBF header, it never has long names, it has a data structure per field that only allows up to 10 char long names, that doesn't differ in DBFs belonging to a DBC. You lose the long names as VFP can't look them up in the DBC file.

Bye, Olaf.

 

[Olaf Doschke]

Maybe your old DBC was never defect and you only had it open exclusive, then tried to open a dbf of it in a second VFP session. Maybe even two computers were involved and you didn't notice. Don't know exact reasoning of the corruption error message, but the only chance you kept your long names is them coming from the old DBC, which must at least been partially OK, enough so that most DBFs (except one you had problems with) found its names in the DBC, they don't come from somewhere else.

Bye, Olaf.

 

[dkean4]

Olaf

I am completely sympathetic to your POV. But I would not have been running the little script had the DATABASE had listings in it. I CREATED a NEW DATABASE and named it slightly differently. I also removed the original DATABASE from that directory.

But I will take some time this weekend to redo that process and see if I am nuts. Can't do it today.

Cheers...


Dennis Kean

Simplicity is the extreme degree of sophistication.
Leonardo da Vinci

 

[dbMark]

>Interesting that INDEX ON reindexes

I had a co-worker (who in time had become my manager) use INDEX ON daily on large files. One day I happened to notice that the index files were far larger than the tables, and growing by the day. I did some testing and confirmed that INDEX ON concatenated the new index data onto the end of the existing index file. Once he tested it for himself, he changed his code to REINDEX so files didn't bloat.