Trying to add new indexes to existing Application Groups we encountered an issue where CMOD tries to create a database index with the same name as an existing index.
When you create fields in an Application group and designate them as "index", CMOD creates indexes in the underlying RDBMS.
The name of the indexes is table segment name suffixed with a number denoting the order in which the field was added to the App Group (starting with zero).
For example, if we have an App Group with underlying table name ABC and 4 fields, the first and last of which are "index", CMOD creates 2 indexes in the database ABC_0 and ABC_3.
The problem is that every table without exception also has an index created for the DOC_NAME field, which is an internal field used by CMOD to physically locate the document. The name that CMOD gives to this index depends on the total number of fields in your App Group - so, in this example CMOD creates an index called ABC_4 on the DOC_NAME field.
If you later try to add a new field to the same App Group and designate it as "index", CMOD fails after several minutes with the message "Unable to update the Application Group. A return code of Exists was returned from the Server".
And sure enough, in the System Log there are multiple (depending on how many arssockd are running) DB error messages containing ".. name is already used by an existing object".
After monitoring these DOC_NAME indexes for some weeks we have found that they are never used.
What can we do? DROP the unnecessary index on DOC_NAME?