291 views

Description

 
Issue Description
After committing an update set that deletes a custom field from a table in the cmdb hierarchy, other tables may show errors like this in list view:
Syntax Error or Access Rule Violation detected by database (Unknown column 'cmdb0.a_ref_12' in 'field list')
 
This error happens when that table has a column mapped to the same storage alias as was used by the column that was deleted. The field (a_ref_12) is deleted from the table when the update set to delete the column is committed, even when other tables use the same field as a storage alias. Data stored in that field is lost.
 
Note: This happens only when deleting a column through an update set. It does not occur on the instance the update set is created.
 
Symptoms Observed
  • Syntax error similar to Syntax Error or Access Rule Violation detected by database (Unknown column 'cmdb0.a_ref_12' in 'field list')
  • While the custom field was dropped through the Update Set and no longer exists in the dictionary, it's storage alias is still there
 
Build information
Customers that have upgraded from pre-Jakarta to Jakarta - Jakarta Patch 6 will be at risk for this problem
 
Additional Information
PRB1234751 was fixed in KP1 and JP7.  This problem addresses the issue that undergoing TPP can put the cmdb dictionary structure in a bad state such that you are at risk to this problem. Upgrading to JP7+ or KP1+ from a family prior to Jakarta ensures that the cmdb does not get put into this bad state. If already upgraded to Jakarta and are on an impacted build, upgrade to a fixed version of this problem to ensure there is no longer a risk.

Steps to Reproduce

Reproduced this on an internal Jakarta Patch 6 instance as follows: 
  1. On a temporary source instance, create table A that extends cmdb_ci. Capture this in update set 1.
  2. Add a column to the new table that is a reference field 
  3. Complete the update set 1 and export it to xml
  4. On empjdegraaf3 (target instance), import the update set and commit it
  5. Confirm  the table and the column is created. Note down the storage alias it uses. For example, a_ref_12 on storage table cmdb$par1
  6. Create another table on empjdegraaf that also extends cmdb_ci and has a reference field. 
  7. Check the storage alias for this new field. It is likely to use the same storage alias a_ref_12. If not, manipulate it so it does through xml export/import
  8. Create some records for this new table, ensure the reference field has data
  9. Through sql - add the a_ref_12 field to the cmdb table - assuming it does not exist yet. As it is not clear under what conditions the platform decides to add it to cmdb when committing the update set, this is a manual step in this scenario to reproduce it.
  10. Clear cache - notice that because the reference field exists on both tables now, the list view is no longer showing the value for it that was created in step 8, this part should be resolved through PRB1234751
  11. On the temporary instance where the first update set was created, create a new update set and delete the column that was created in step 2
  12. Complete the update set 1 and export it to xml
  13. On the target instance, import the update set and commit it
  14. Confirm that a_ref_12 is dropped from cmdb and cmdb$par1 and the data for the other table that depended on it is now lost for that column
 
Column is dropped despite other fields pointing to it, causing data loss for those fields.

Workaround

At this time there is no current workaround. If the symptoms are observed for this issue please reach out to Customer Support.  We are currently running internal audits and reaching out to anyone at risk of running into this issue if running the steps to reproduce.


Related Problem: PRB1244420

Seen In

There is no data to report.

Intended Fix Version

London

Fixed In

Jakarta Patch 9
Kingston Patch 5

Safe Harbor Statement

This "Intended Fix Version" information is meant to outline ServiceNow's general product direction and should not be relied upon in making a purchasing decision. The information provided here is for information purposes only and may not be incorporated into any contract. It is not a commitment, promise, or legal obligation to deliver any material, code, or functionality. The development, release, and timing of any features or functionality described for our products remains at ServiceNow's sole discretion.

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-07-04 04:22:52
Published:2018-03-28