Loading...
Loading...
A data management delete job on a table does not cascade to referencing tables which have a delete reference casacade rule when the table is extended/sharded. The exact same operation on a non-sharded version of the table does successfully cascade. An example of this is the sys_email table which was sharded up until Helsinki when changed it to being non-sharded for all new or zbooted instances. This change also removed the DELETE reference cascade rule on the sys_watermark.email reference field. In this problem the reference cascade rule has been set back to DELETE as it simplifies (in theory) the cleanup of unwanted sys_emails and their related sys_watermark record. On a non-sharded sys_email the deletion of an email cascades to its sys_watermark and other related reocrds. On a sharded sys_email the deletion does not cascade with deleteRecord. The only way to trigger the cascade is to use deleteMultiple in place of deleteRecord, even if only deleting a single sys_email.
The steps to reproduce below test the behaviour on an instance where the sys_email table is not extended/sharded, which is has been the default for new/zbooted instances for a long time. Common to both setups: 1. Delete any existing emails with target = 98ec5fdcd7011200f2d224837e610317 2. Delete any existing sys_watermark records with source record = 98ec5fdcd7011200f2d224837e610317 3. Import the following attached files: - cmn_rota_98ec5fdcd7011200f2d224837e610317.xml - sys_email (instance=98ec5fdcd7011200f2d224837e610317).xml - sys_watermark (sys_created_onON2024-02-21@javascript_gs.dateGenerate('2024-02-21','start')@javas.xml Note: The cmn_rota record may already exist and there may be existing emails and watermarks. Removing the existing emails and watermarks and importing these files ensures a known set of records are present. 4. Ensure that the sys_watermark.email sys_dictionary record has its reference cascade rule set to DELETE 5. Create the glide.cascade.debug system property and set to true NON-SHARDED/NON-EXTENDED sys_email Parent: cmn_rota sys_id=98ec5fdcd7011200f2d224837e610317 3 emails 3 sys_watermarks Emails and watermarks created 2024-02-17 On Washington on a non-sharded email table a delete job for sys_email configured with: target=98ec5fdcd7011200f2d224837e610317 target_table=cmn_rota created_on=2024-02-17 Preview cascade shows: 3 sys_watermark 3 sys_email After execution the actual delete counts: 3 sys_watermark (empty) sys_email The sys_email result is expected since sys_email is explicitly excluded by the Platform, but arguably an admin should be able to trust that anything a delete job, which is configured to record for rollback, will record everything to be able to able to rollback the job. The emails were deleted along with the sys_watermark records The rollback context did NOT capture the sys_email record, only the sys_watermark records. Therefore while the delete job did delete all the necessary records its execution can't be completely rolled back. SHARDED/EXTENDED sys_email Same test on an Utah instance with a sharded sys_email table. To create the sharded sys_email table: 1. Navigate to System Definition => Table Rotations 2. Click New 3. Set name to sys_email 4. Leave type as Extension 5. Set duration to 30 days After sharding delete perform the same common steps to delete any existing sys_email and sys_watermark records related to the cmn_rota record Edit the sys_email XML and update the sys_created_on and sys_updated_on values to fall in the valid_from and valid_to range of the sys_email0000 shard. While not strictly necessary the sys_watermark XML could be updated so the system times are consistent with the emails. Import the three modified XML files. Same 3 emails, same 3 watermarks, but the emails are now not in the base sys_email table but are in the sys_email0000 shard. Preview cascade shows: 3 sys_watermark 3 sys_email Executing the job and viewing the results shows: (empty) sys_watermark (empty) sys_email for actual deleted records for both sys_email and sys_watermark. The emails were deleted but the sys_watermark records were not, so the cascade failed. The rollback context failed to capture any records (would only capture sys_watermark as per note above) Re-import the sys_email records so the instance has the 3 emails and their 3 watermarks. Create a delete job: target=98ec5fdcd7011200f2d224837e610317 target_table=cmn_rota created_at_or_before=2024-02-20 Preview cascade shows: 3 sys_watermark 3 sys_email After execution the actual counts are: 3 sys_watermark (empty) sys_email <= These were deleted and the rollback context did capture the deletion of the sys_watermark records but failed to capture the sys_emails, which were deleted. Therefore the difference appears to be that with a deletion for a specific date does not cascade, but with a date comparison which potentially matches emails in multiple shards then the cascade occurs.
Using a comparison operation such as less than or less than or equal to will result in the delete operation cascading. Please be aware that currently the deletion of sys_email records from either a script or a data management job cannot be captured for rollback, see KB1633846 for PRB1740509. Therefore you may want to consider using System Archiving to initially archive sys_email records and then define an Archive Destroy Rule to delete the archived emails at a later date to add an additional stage to pass through before finally removing the emails permanently.
PRB1740351
Click on a version to see all relevant bugs
ServiceNow Integration
Learn more about where this data comes from
Bug Scrub Advisor
Streamline upgrades with automated vendor bug scrubs
BugZero Enterprise
Wish you caught this bug sooner? Get proactive today.