The Human Resources Service Management application is redesigned in Fuji and one of the changes is the introduction of a table called Service Order (sm_order). Instead of extending the task directly as it did previously, hr_case and hr_task now extend sm_order, which in turn extends the task.
Dublin and later releases introduce task flattening. Task flattening improves performance by combining all task-based tables into a single physical table in the database. These tables exist as a Table-per-Hierarchy (TPH) model. All non-task tables continue to exist as separate tables in the database or in a Table-per-Concrete Class (TPC) model.
When creating new task-based tables, the total number of records in the task table is compared with the value of the glide.db.hierarchy_large_root.task system property. The property has a default value of one million. If the task table becomes very large and exceeds the value of this system property, any new task-based tables are created under a TPC model and are not flattened.
If the Human Resources Service Management plugin is installed and the total number of task records does not exceed the value of the glide.db.hierarchy_large_root.task system property, the sm_order table is created as TPH during an upgrade to Fuji without any issues and no data is lost.
If the number of records in the task table exceeds the value of the glide.db.hierarchy_large_root.task system property, the sm_order table is created under a TPC model, even though hr_case and hr_task exist as TPH. When the system attempts to reparent a TPH table so that it extends a TPC table, data in the TPH table can potentially be lost. The fix for this PRB allows this sort of reparenting done by the system. The workaround forces sm_order to be created as TPH so there are no reparenting issues.
Steps to Reproduce
ServiceNow was able to reproduce the issue on our test systems by following these steps:
- On a Eureka instance, activate the HR Service Management plugin and load demo data.
- Change the value of the glide.db.hierarchy_large_root.task system property so that it is less than the total number of rows in the task table.
- Upgrade the instance to Fuji.
Observe that data in the hr_task and hr_case tables is lost.
Customers with the following three conditions are at risk for this bug and should follow the steps in the workaround to ensure no data is lost during upgrade:
- The total number of records in the task table exceeds the value of the glide.db.hierarchy_large_root.task system property.
- The HR Service Management plugin is enabled.
- The instance is being upgraded to a Fuji family release.
To workaround this issue, ensure that the total number of records in the task table will not exceed the value of the glide.db.hierarchy_large_root.task system property before the upgrade to Fuji.
- Determine the total number of records in the task table.
- Open the list view of the task table. This can be accessed directly by going to https://<instance_name>.service-now.com/task_list.do.
- If the total number of records in the task table is currently less than one million records and will not exceed one million by the time the instance is upgraded to Fuji, no further actions are needed. Otherwise, proceed to step 2.
- Go to System Properties and search for the property named glide.db.hierarchy_large_root.task
- Reference: https://<instance_name>.service-now.com/sys_properties_list.do?sysparm_query=name%3Dglide.db.hierarchy_large_root.task
- If the property does not exist, create a new property with this name and set the Type to string.
- After accessing the property, set the value to 99999999 (99 million).
Please note, if you are upgrading the instance to Geneva or Fuji Patch 4 Hotfix 3 or later, be sure to reset the workaround by setting the default value back to 1 million.
Related Problem: PRB629882