Steps to Reproduce
- Get a large table (>100k rows)
- Add a column to the table with a default value
- It's important that the default value is specified when the column is added, not after.
Expected Behavior: The column is added and default value applied quickly
Actual Behavior: Based on the size of the table this operation can take an excessive time to add the column and then go back through and apply the default value. In some cases, more than a day. When this operation is during an upgrade it can cause the upgrade to take a very long time and give the appearance of not making progress.
Identified tables/columns affected by this issue causing long running upgrades
- em_alert_history table
- columns added between upgrade from Helsinki to Istanbul leading to long running upgrade: correlation_group, correlation_rule_group, group, group_source, impact_services, is_group_alert
- WORKAROUND: follow the step-by-step instructions in KB0623281
- Per the documented workaround, will need to download the following attachments: sys_auto_flush_62c63f08930002000fb3b9ab357ffb4a.xml, sys_remote_update_set_f6d841a6db5bb200b9d5b701ef96198b.xml
- Additional comments
- What data is in the em_alert_history table that is being cleaned? the data is this table stores historical information about alerts and is not used in the UI or impact calculation after 7 days.
- What is being done in the future to prevent this issue? The table cleaner is being implemented via the workaround, but will also be implemented by a fix included in a future release.
Note: this can affect all large tables on upgrade if a new column is added, however, the above table/columns have been identified by ServiceNow as affected by this problem and leading to potential risk of a long upgrade
Outside of the identified columns/tables above, there is no workaround for this issue as it is caused by the underlying operations that add a column. There are plans to improve this process in later releases.
Related Problem: PRB1028261