DeleteAllIndexes used to delete all indexes (duh) and then create a new
default primary key on RecordID. It still deletes all indexes and leaves a
default RecordID index but that index is not populated. On further
investigation, even the following does not actually re-build the primary
index:

ALTER TABLE MyTable
DROP PRIMARY KEY,
ADD PRIMARY KEY (RecordID)

This can be reproduced by doing the following:

1. Start with 2 copies of MyTable -- one populated with data and one empty.
2. Replace the .idx file of the populated file with the one from the empty
file.
3. Use the SQL code above to attempt to rebuild and populate the primary
index.

The table will appear to be empty if you open it in DBSYS because the index
is still invalid even though it should have been rebuilt. Also, any
additional indexes added will be empty as well, perhaps because they rely on
the primary index.

This is new behavior with a recent update, perhaps one of the 5.24 updates.

I depend on this to work becuase our software has a built-in backup system
that copies .dat and .blb files (no .idx files) to zip files. On restoring a
backup, the existing indexes are dropped using DropAllIndexes and then
re-created in SQL. This has always worked perfectly before.

We also have a "re-index" maintenance item on our software that uses the
same technique and that is not working either.

For now, I'm looking for a workaround. Repair will work but it is an extra
step that can add a lot of time to the task for large tables.

Thanks,
Danny

Danny,

<< DeleteAllIndexes used to delete all indexes (duh) and then create a new
default primary key on RecordID. It still deletes all indexes and leaves a
default RecordID index but that index is not populated. >>

I can't replicate this here.

<< 1. Start with 2 copies of MyTable -- one populated with data and one
empty.
2. Replace the .idx file of the populated file with the one from the empty
file. >>

You just corrupted the index with step 2.  After that point, all bets are
off except for using RepairTable to repair the table *before* doing anything
further.

<< The table will appear to be empty if you open it in DBSYS because the
index is still invalid even though it should have been rebuilt. >>

No, it shouldn't.  When you replace the index with an invalid empty one,
you've basically made all of the rows invisible to even DBISAM itself.  The
only operation that will repair an invalid index is the RepairTable
functionality.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:B7D94FD2-E9A1-4ECA-A5C2-8936856184E4@news.elevatesoft.com...
>
> You just corrupted the index with step 2.  After that point, all bets are
> off except for using RepairTable to repair the table *before* doing
> anything further.
>
> << The table will appear to be empty if you open it in DBSYS because the
> index is still invalid even though it should have been rebuilt. >>
>
> No, it shouldn't.  When you replace the index with an invalid empty one,
> you've basically made all of the rows invisible to even DBISAM itself.
> The only operation that will repair an invalid index is the RepairTable
> functionality.
>

Hmmm... but the index is not reported as invalid. So DBISAM will happily
continue processing with mismatched table and index files. Seems to me there
should be some data element that should make the table open blow up if you
have this situation.

Maybe there's no solution, but it is something to keep in mind when you're
searching for the cause of a problem. For example, SQL works just fine. So
the program might seem to be OK, but you have an invalid index.

Robert

Robert wrote:
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:B7D94FD2-E9A1-4ECA-A5C2-8936856184E4@news.elevatesoft.com...
>
>>You just corrupted the index with step 2.  After that point, all bets are
>>off except for using RepairTable to repair the table *before* doing
>>anything further.
>>
>><< The table will appear to be empty if you open it in DBSYS because the
>>index is still invalid even though it should have been rebuilt. >>
>>
>>No, it shouldn't.  When you replace the index with an invalid empty one,
>>you've basically made all of the rows invisible to even DBISAM itself.
>>The only operation that will repair an invalid index is the RepairTable
>>functionality.
>>
>
>
> Hmmm... but the index is not reported as invalid. So DBISAM will happily
> continue processing with mismatched table and index files. Seems to me there
> should be some data element that should make the table open blow up if you
> have this situation.

And how would it detect this error? Go through the entire index making
sure it points to a valid record? That takes time. The only "quickie" I
can think of is to count the number of index entries for the primary key
and make sure it agrees with the number of rows in the table.

Or if the table is opened, and it can't find the index file, maybe the
indexes should be built automatically if the property "AutoBuildIndex"
is true?

> Maybe there's no solution, but it is something to keep in mind when you're
> searching for the cause of a problem. For example, SQL works just fine. So
> the program might seem to be OK, but you have an invalid index.

I know some MySQL shops that will run a table verification every morning
just to make sure the tables are not corrupted. This may be overkill but
it is necessary if you want to ensure the tables and indexes are valid.

You may be able to delete the index instead of replacing it with a
corrupted one, and have the program catch an exception and rebuild it.

Dave

"Dave Harrison" <daveh_18824@spammore.com> wrote in message
news:7AB6B7F0-5688-44D5-8437-4E16E16552CF@news.elevatesoft.com...
>>
>> Hmmm... but the index is not reported as invalid. So DBISAM will happily
>> continue processing with mismatched table and index files. Seems to me
>> there should be some data element that should make the table open blow up
>> if you have this situation.
>
> And how would it detect this error?

You could have a number on each one of the parts (DAT, IDX and BLB) that
gets incremented every time you restructure the table. If the numbers don't
match, you know you have a problem.

But this is not my field. The problem might have been solved some other way,
or it might not be solvable.

Robert

Robert wrote:

> "Dave Harrison" <daveh_18824@spammore.com> wrote in message
> news:7AB6B7F0-5688-44D5-8437-4E16E16552CF@news.elevatesoft.com...
>
>>>Hmmm... but the index is not reported as invalid. So DBISAM will happily
>>>continue processing with mismatched table and index files. Seems to me
>>>there should be some data element that should make the table open blow up
>>>if you have this situation.
>>
>>And how would it detect this error?
>
>
> You could have a number on each one of the parts (DAT, IDX and BLB) that
> gets incremented every time you restructure the table. If the numbers don't
> match, you know you have a problem.

Yes and you'd also have to increment the table "generation" number every
time the index gets rebuilt too so they are all in sync. Your idea of
yours may work and could solve the problem of mismatched files. But
that's all it's going to do. It won't check for corrupted indexes etc..
Maybe that's all you need. It's up to Tim to make it work.

Dave

Robert,

<< You could have a number on each one of the parts (DAT, IDX and BLB) that
gets incremented every time you restructure the table. If the numbers don't
match, you know you have a problem. >>

That's what EDB does.  It's not perfect because it's still possible to get
around it, but it works for most purposes.

However, I'm not sure if I can do it with DBISAM due to the pre-existing
applications that do funky things with respect to the index files.  A change
like this could break them pretty easily.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

It used to work fine.

The bottom line is that I need a way to get rid of the old index file and
rebuild it from scratch (short of a full-blown table repair). I do this as a
part of routine database maintenance and, more importantly, after restoring
..dat/.blb files from a backup. That used to be as simple as DeleteAllIndexes
followed by adding indexes to the table. Now it doesn't work.

Deleting the .cdx works but that is not an option for client/server where we
don't necessarily have file-level access.

Thanks,
Danny

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:B7D94FD2-E9A1-4ECA-A5C2-8936856184E4@news.elevatesoft.com...
> Danny,
>
> << DeleteAllIndexes used to delete all indexes (duh) and then create a new
> default primary key on RecordID. It still deletes all indexes and leaves a
> default RecordID index but that index is not populated. >>
>
> I can't replicate this here.
>
> << 1. Start with 2 copies of MyTable -- one populated with data and one
> empty.
> 2. Replace the .idx file of the populated file with the one from the empty
> file. >>
>
> You just corrupted the index with step 2.  After that point, all bets are
> off except for using RepairTable to repair the table *before* doing
> anything further.
>
> << The table will appear to be empty if you open it in DBSYS because the
> index is still invalid even though it should have been rebuilt. >>
>
> No, it shouldn't.  When you replace the index with an invalid empty one,
> you've basically made all of the rows invisible to even DBISAM itself.
> The only operation that will repair an invalid index is the RepairTable
> functionality.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

"Danny Humphress" <danny@_no_spam_remove_this_SurpassSupport.com> wrote in
message news:5B993A48-1C5F-41D3-B7DD-6D1B6C5F27D7@news.elevatesoft.com...
> Tim,
>
> It used to work fine.
>

Use SQL to drop and rebuild the indexes.

Robert

Robert wrote:

> "Danny Humphress" <danny@_no_spam_remove_this_SurpassSupport.com> wrote in
> message news:5B993A48-1C5F-41D3-B7DD-6D1B6C5F27D7@news.elevatesoft.com...
>
>>Tim,
>>
>>It used to work fine.
>>
>
>
> Use SQL to drop and rebuild the indexes.
>
> Robert
>

Robert,
    Yes, but how does he know if the indexes need rebuilding? As he
said, SQL thinks the existing indexes are ok. I think he could create a
stored procedure that would check to see if the index file exists on the
server, and if not, use SQL as you suggested to rebuild the index. He
can use FindFirst() to see if the file exists or to see if the file has
an unusual date stamp (means index needs rebuilding). If he wants to do
it all in SQL then when the index needs rebuilding, just add a row to a
Maint table with the table names for the indexes that need rebuilding.
Or add an SQL script file that has SQL to rebuild the indexes when the
new data file is copied over. There's a lot of ways it can be done. If
you want to get fancy, have the client send a message to the server
saying "Rebuild indexes for these tables...".

Dave