Index corrupts after table grows too large, unlesss all keys are uppercase

[Juniper]

I recently added a new table called "inventory_trans" to my organization's database as part of an inventory tracking system. Whenever a user action elsewhere in the database creates a change in the current stock level, a record denoting the amount of change is entered in this table, which automatically posts to another table that contains the running total for each item we are tracking. All normal data entry to this table is done via Xbasic; only rarely will anyone enter a record directly.

I had this system up and running for about three weeks without a hint of trouble. The table grew to about 4300 records, no problem. Then one day I made a few changes at once: 1) I added a new posting field rule. 2) I added two filtered indexes to the table to accommodate the new rule. The system also automatically added a new index that was unfiltered with "recno()" as the order expression. 3) I ran a script which added over 17,000 records to the table, bringing the total up to more than 21,000 records. Instantly, I began getting EXTREMELY frequent messages saying that the indexes on this table had been corrupted. When I say extremely frequent, I mean that I was lucky to get 5 records entered before the message would show up again, even when entering manually into the default browse.

I suspected the new field rules, so I reverted to the old ones. No help. I read enough on this forum to think that the filtered indexes were the problem, so I deleted them. Still no help. I thought maybe it was a shadowing problem, since my development copy worked fine, but it was direct. No help there, and I shadowed my dev copy and it still worked fine. I checked over all the Xbasic that modified the table, & couldn't find any logical errors. I threw in a few "wait-for-idle" commands between the record entries just in case I was entering new records too quickly, but still no help. I deleted and rebuilt the indexes. Still no help.

Then I realized that my dev copy had significantly fewer records than my live copy. I copied the live data to the dev copy, and it instantly broke as well. So, now what? Back to the forums...after hours of searching I found a tiny little mention deep in one thread that someone else had consistent trouble with one table's indexes until he converted all the indexed fields to uppercase. I thought that was a bit far-out, but I tried it anyway. Presto! Everything works.

So my question is, WHY does my table exhibit this behavior? Is there some documented reason why the index keys need to be uppercase? How will I know if circumstances are right for this issue to crop up in the future? It seems to me that it's a flaw in the indexing algorithm, but maybe I'm missing something.

Here's the specifics on the table:

TABLE NAME:
Inventory_trans

FIELDS:
Item_ID (C, 8)
Date (D, 8)
Trans_Qty (N, 6.0)
Trans_Type (C, 1)

INDEXES:
Item_ID (Filter: none. Order: Item_ID, Ascending, All)
Itembydate (Filter: none. Order: Item_ID+cdate(DATE), Ascending, ALL)
Recno (Filter: none. Order: recno(), Ascending, All)

The indexes I deleted were:
Verified (Filter: TRANS_TYPE="V" Order: Item_ID+cdate(DATE), Ascending, All)
Unknown (Filter: TRANS_TYPE="U" Order: Item_ID+cdate(DATE), Ascending, All)

Item_ID may contain a number string (such as "1845"), mixed letters and numbers (such as "CD1845"), all letters (such as "CDSLVBLK"), letters & hyphens (such as "CD-R-VAL"), or letters, numbers, and hyphens (such as "2CD-CRYS"). This was the field I converted to uppercase in order to make the indexes work.

Any clues? Thanks in advance...

~Jason

[Juniper]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Hmmm...Maybe my first post was too long. :) Has anyone else experienced this issue with lowercase characters in an index for a moderate-sized table? Any clues?

~Jason

[Tom Cone Jr]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Nope. Haven't seen this, ever. I'd be inclined to think there's something wrong in the data itself. Characters the index can't handle because not allowed in DBF format, maybe.

Don't know your app, obviously, but I were indexing items by date, the date component would be first, and the item second. i.e. cdate(my_date_field) + item_id, instead of the way you have it now.

Also, I think it's bad practice to use fieldnames that match function names. "Date" is not a fieldname I'd use in my tables. DATE() is a built in function.

-- tom

[Juniper]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Is the hyphen permitted in a DBF? To my knowledge, that's the only non-alphanumeric character used in the table. I still find it strange that merely running an update operation using UPPER() would correct characters that aren't permitted.

Thanks for the tip on the name of the date field. If I were building the table over again, I'd name it something else, but I doubt it's worth changing now that most development is done?

And about the index, you're right, most of the time that's the way it's done. But in this case I'm more interested in seeing what happened to a particular item, than what happened on a particular day.

Thanks for the time.

~Jason

[Stan Mathews]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

To my knowledge, that's the only non-alphanumeric character used in the table.

3) I ran a script which added over 17,000 records to the table, bringing the total up to more than 21,000 records.

What was the source of the 17000 records?

[Al Buchholz]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

While some systems accept upper and lower case characters(ie letters of the alphabet) as unique values in an index, Alpha doesn't.

I've overcome that by converting the data in the field into a lager field with the ascii value of each character replacing the original character. You end up with a unique value in a 2 to 3 time longer field.

Like Stan asked, where does this data come from?

You should import the data into Alpha into a plain vanilla intermediate table. No field rules/checking - and then analyze what you have in those fields...

[Juniper]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Hi Stan,

Wow, that's a long story but I'll try to keep it short. In essence, the contents of the ITEM_ID field for each of those 17,000 records came from one of two tables: "cassettes" and "consumables". "Cassettes" is our products table; it shouldn't be called cassettes anymore but I can't change that now. "Consumables" contains the items we use to make our products. The script I ran examined all of our invoices for the last year and entered a summarized version of the records that would have been entered in the inventory_trans table if the system had been running for the last year. (This was so that my statistical calculations would be accurate from the get-go instead of waiting 'til next year) For both of the source tables, the field I copied to ITEM_ID was an 8 character field with the same type of contents I described for ITEM_ID in my first post above. No non-alphanumerics except the hyphen.

Al,

Thanks for the tips. I don't mind that Alpha treats upper and lower characters as the same. But I'm trying to figure out why the index file actually corrupted when lowercase characters were present in the table data.

Thanks to all!
~Jason

[Al Buchholz]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Al,

Thanks for the tips. I don't mind that Alpha treats upper and lower characters as the same. But I'm trying to figure out why the index file actually corrupted when lowercase characters were present in the table data.

Thanks to all!
~Jason

I won't worry about that message as much as putting the data in so the system works.

[Juniper]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Thanks to all for the responses. Just for the record, the app ran perfectly after the fix I described, until yesterday a record with a lowercase ITEM_ID slipped in because a field rule wasn't honored. Guess what? The dreaded "Indexes have become corrupted" message reappeared. I manually changed the offending record to uppercase and the system's back to working smoothly. So whatever the root of the problem is, it really is directly affected by lowercase characters. Strange...

Again, thanks to all for the tips and comments. I'm still curious why things are working the way they are, but since the system's operating fine with the present "workaround," I won't take your time with further questions.

~Jason

An afterthought: The exact value of the field I referred to above was "CD-Poly" which I changed to "CD-POLY".

[Stan Mathews]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Doesn't make any sense to me that you are having the trouble you describe because of an upper/lower case issue. (Not doubting you, just mystified)

Can you duplicate the table under a new name, empty the duplicate, and attach it here for us to see?

[G Gabriel]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

So whatever the root of the problem is.

A bug.
Didn't expect it to, but it's a bug in Excel as well.
I'd submit a bug report and won't hold my breath for a fix.

[Juniper]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Stan,

See attached. There's nothing confidential in the data, so if you want the original table you can have it. I have also attached an emptied copy of the table referenced in the field rules, just in case you need it. The original name of the first table is Inventory_Trans. The original name of the second table is Inventory. Also, remember that I never had any trouble until my record count exceeded about 20,000 or so.

Gabe,

Have you experienced this before, or how are you sure it's a bug?

Thanks to all!
~Jason

[Stan Mathews]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Always fun trying to understand someone else's process.

One observation. In the field rules for the inventory table you have the hide_rec field as required and it has a default expression. I believe that to be redundant - not needing the required designation.

Question: In the inventory_trans table you have a posting field rule for the date and trans_qty fields. Does the onsaverecord event script do the same thing? Hard to follow.

Also missing the inventory_trans_types table.

[G Gabriel]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

?sortsubstr("A,a,G,g,g,a,d,D",",","A")
= "a,a,A,d,D,g,g,G"
'Correct Answer

?sortsubstr("A,a,B,b,D,d,D",",","A")
= "a,A,b,B,d,D,D"
'Correct Answer

?sortsubstr("A,a,B,b,b,D,d,D",",","A")
= "a,A,b,b,B,d,D,D"
'Correct Answer

?sortsubstr("a,A,a,B,b,b,D,d,D",",","A")
= "A,a,a,B,b,b,d,D,D"
'Really?

?sortsubstr("1,6,5,7,a,A,a,B,b,b,D,d,D",",","A")
= "1,5,6,7,a,A,a,b,b,B,d,D,D"
'Really
'Try same in Excel

[G Gabriel]

Re: Index corrupts after table grows too large, unlesss all keys are uppercase

Sorry,..I had it backwards when I referred to those as being correct answer.. they are not.
Here is another example:

?sortsubstr("a,A,b,c,B,C,d,D,D",",","A")
= "A,a,b,B,C,c,D,D,d"

Correct answer should be:
A,a,B,b,C,c,D,D,d