In the UI adding a Reference field when the cmdb table has 64 indexes and cmdb$par1 is not the current partition leads to the field being added to cmdb$par1, storage alias glommed to cmdb and no index, with Syntax Errors "Unknown column" & "Too many keys" - Known Error

In the UI adding a Reference field when the cmdb table has 64 indexes and cmdb$par1 is not the current partition leads to the field being added to cmdb$par1, storage alias glommed to cmdb and no index, with Syntax Errors "Unknown column" & "Too many keys" Description In the TPP extension model, introduced in Jakarta, the tables in the CMDB hierarchy are flattened onto a number of physical tables in the underlying database known as partitions. Information about what is the current partition is stored in the sys_table_partition table.

When a reference field is added to a table in the CMDB hierarchy, and there are no available fields to use on the current partition table, a new field is created along with a corresponding index. Information is added to the sys_storage_alias table to record which partition table and database field the dictionary field is glommed to.

This problem can be encountered when the current partition table has reached, but not exceeded the 64 index limit imposed by MySQL. If the cmdb table has not exceeded 64 indexes then the sys_table_partition table has a single record for the cmdb$par1 partition and the "is current target" field set to false. For example, the majority of instances will have the cmdb table (source/base table) and one additional partition table: - cmdb - cmdb$par1 / is_current_target = false

In this scenario, when a new reference field is created (and there are no suitable fields available) the instance creates the new field on the cmdb$par1 partition as it can't be created on the cmdb table as there is no room for an additional index.

Due to this problem the sys_storage_alias record the is created has the storage table set to cmdb instead of cmdb$par1, and the index is not created because the ALTER is executed against cmdb and not cmdb$par1. Only after these actions is the sys_table_partition table updated to set "is current target" to true for the cmdb$par1 table.

NOTE: The behaviour when the reference field is added via an update set is different and there is no indication any problem has occurred, but the field is created on the cmdb partition with no index instead of on cmdb$par1 with an index, refer to KB0759320 / PRB1358372.

Symptoms that indicate this problem has been encountered:

An error should be reported in the UI when the reference field is created: "Syntax Error or Access Rule Violation detected by database (Too many keys specified; max 64 keys allowed)":

Syntax errors are reported on forms or lists which include the field: "Syntax Error or Access Rule Violation detected by database (Unknown column 'cmdb0.a_ref_a29' in 'field list'): The sys_schema_change table should have a record with state = "Incomplete" and Alter Type = "Create Index(es)" for the reference field's table and element: Steps to Reproduce

Option A: - If number of indexes on the cmdb partition is less than 64 and is_current_target is false for the partition table cmdb$par1 on sys_table_partition, then: - create new reference fields on cmdb_ci so that the 64 index limit is reached and create one additional reference field, which should display the "Too many keys specified; max 64 keys allowed" error if created through the UI.

Option B - If the number of indexes on the cmdb partition is already at 64 and is_current_target is currently set to true for the partition table cmdb$par1 on sys_table_partition then change is_current_target to false - Invalidate cache from background scripts: gs.invalidateCache(); - Create additional reference fields on cmdb_ci through the UI until the error "Too many keys specified; max 64 keys allowed" is seen.

Workaround This problem is currently under review. Subscribe to this Known Error article by clicking the Subscribe button at the top right of this form to be notified when more information will become available.

Should you observe the errors mentioned above, please contact ServiceNow Technical Support to check and confirm if this problem has been encountered on your instance and to assist in resolving the issue.