Getting error while adding an index to a table.

Posted by anzeljamal on 01-Jun-2015 07:02

I have a table which has a field with datatype date with initial value set as ?. I have added some records into the table and some of the records have the date field value as ?. Later on i have made the  date field as mandatory and added  a unique index with one component being this date field. I have rebuild the indexes and i didnt get any error while doing the same. The index rebuild completed without any errors. After that i have added more records into the table. Then i am adding a new index to the table which doesnt have any reference to this date field. But when i loads the df i am getting an error as "** .<Date-field> is mandatory, but has unknown value(?) value (110)".

I have tried to query the record with ?, but couldnt find any records as such. I think it is because of the unique index already present for the field and while rebuilding that index, the record would have not been entered in the index table. So i deactivated all the indexes of the table and enabled just one index, and tried to fetch the data, and i am able to find one record with that date filed value as ?. I have updated it directly and enabled all the indexes and again tried to load the same df, but still i got the same error.

Can any one please suggest what is the issue here? And a solution to overcome a scenario like this ? Will adding a new index rebuilds all the unique indexes of the table ?

All Replies

Posted by S33 on 01-Jun-2015 08:44

That sounds bizarre. What versions of Progress and O/S?

It may be extreme, but you could dump the data from that file, delete the data, load the df, and then load the data.

Posted by Fernando Souza on 01-Jun-2015 09:32

No, adding a new index will not rebuild the other indexes. But when adding a new active index, the records will get scanned so that the keys can be added to the new index and in the process, the records are validated to make sure they are all ok based on the definitions, so if you have a mandatory field with the unknown value, you will see the error described.

And multiple unknown values are allowed in unique indexes, so I don't think the reason you couldn't find them was due to the fact that the index was unique.

Based on the error, it seems there is still a record with the unknown value for the field referenced in the error message (which I am assuming, it is still the date field when you are trying to load the .df).

Can you replicate this with a test table? Which OpenEdge version?

Posted by TheMadDBA on 01-Jun-2015 09:39

I think Fernando has you on the correct path. You aren't getting that error unless there is  actually a date field with ?.

You need to find those records and correct them or you need to turn the mandatory off and enforce that in the code if you don't want to change the historical data.

Posted by anzeljamal on 02-Jun-2015 06:33

Thanks Fernando for your information on this. But still i am confused with the concept. I am explaining you one scenario which i have tried.

I have created a table test with three fields name(character), dob (date, not mandatory, initial ?) and class (character) and with an index name_idx with name field in it. This index is the primary index.

I have created some data into the table with one record having an unknown value in the dob field. Then i have added a new index dob_idx which is unique, inactive and with a combination of fields, name + dob.  Then i have rebuild the new index and changed it to primary. Then i have made the dob field as mandatory. and i have rebuild the primary unique index, dob_idx again. The index rebuild completed without any errors. Now i am trying to add a third index class_idx, which has only one field class and its inactive. When i try to load the df offline, i am getting an error as  " **.dob is mandatory, but has unknown <?> value. <110> ".

So when i am adding a new index to the table, is it trying to scan through the entire data in the table ? if so, why doesnt it show the error while rebuilding the primary unique index ?

The only difference here i am finding with my original issue is here i am able to retrieve the record with dob field equals ?, but in my actual issue, i couldnt retrieve a record with ? value.

What could be the possible issue here? i am not able to get a clear idea on this. Please help me with this.

Posted by anzeljamal on 02-Jun-2015 06:34

@S33 the version of progress is 10.2b and the os is windows 7.

Posted by George Potemkin on 02-Jun-2015 08:22

> So when i am adding a new index to the table, is it trying to scan through the entire data in the table ?

Yes, Progress scans the table using its primary index.

> if so, why doesnt it show the error while rebuilding the primary unique index ?

Idxbuild uses two different algorythms. Try idxbuild without sorting on disk. I guess it will issue the error.

> in my actual issue, i couldnt retrieve a record with ? value.

How did you try to read such records?

Posted by Fernando Souza on 02-Jun-2015 08:25

Because if the index is added inactive, then the index is not populated when it is added. So there is no record scan.

When you add or change fields to be mandatory, it is best that you make sure that field has data in all records. Otherwise you can get the described error in different cases. For instance, if you were to update the record and try to change just the name field. you would have gotten the error there too.

Posted by anzeljamal on 02-Jun-2015 08:50

Thanks George for the information.

I have written a query on the table and in the where clause  i have specified that field directly equals to ?. But i couldn't find a record. Then i tried to deactivate all the indexes from the table and made one index active, which was not a primary index. Then i modified the query to pick that index and additionaly added the condition to check for that particular field equals ?. I found one record matching my condition. I have updated that record and again i have verified whether there record is updated or not.  I didnt get any record matching the condition after that. Then i have activated all the indexes and then tried to load the df. Still i face the same issue.

Also  can you please explain me how to do idxbuild without sorting on disk ? Is it possible to do in version 10.2b ?

Posted by anzeljamal on 02-Jun-2015 08:52

Thanks Fernando for the input.

Here i am adding the index as inactive and i am loading it offline. Still while loading the df itself, it is throwing the error. I believe it is scanning the record while adding the df itself.

Posted by Fernando Souza on 02-Jun-2015 08:58

Just as a test, I would try without the where clause and checking via the IF statement inside the FOR EACH block to see if you can find the record(s).

FOR EACH table-name:

  IF dob = ? THEN ...

END.

Posted by George Potemkin on 02-Jun-2015 09:09

> Also  can you please explain me how to do idxbuild without sorting on disk ?

Answer N when idxbuild will ask:

Do you have enough disk space for index sorting? (y/n)

Posted by Fernando Souza on 02-Jun-2015 09:16

You mean the second .df is loading the index as inactive ? Is there anything else in the .df for that table ?

Posted by anzeljamal on 02-Jun-2015 13:04

Nope it doesn't have anything.Only an index on the class field. When i try to load the df itself, it i throwing the error.

Posted by anzeljamal on 02-Jun-2015 13:05

I have tried the same also, but no luck.

Posted by Fernando Souza on 02-Jun-2015 13:16

But in the .df, the index is defined active or inactive?

Posted by anzeljamal on 02-Jun-2015 13:24

@George  I tried the same method while doing index rebuild in my test scenario on my local machine. It is not throwing error on rebuild. It is getting completed without any errors.

Posted by anzeljamal on 02-Jun-2015 13:25

The index in the df is inactive.

Posted by Fernando Souza on 02-Jun-2015 16:13

I cannot reproduce the error when the index is added as inactive. I suggest you contact Support so they can more closely help you out.

Just a note, there is no use in rebuilding the index after you made the field 'mandatory'. Index rebuild will not have a problem with it, and the mandatory field change does not change the index itself in any way.  You have to change the records to make sure all values are specified for the field that is marked as mandatory.

This thread is closed