683 views

Description

This problem is centered around the length of time it can take to add a new column with a default value (that is NOT prefixed with "javascript:".) to large tables (100K+),  In addition to the time spent creating the new column, we must re-run through all of the data again to apply the default value.

Steps to Reproduce

 

  1. Get a large table (>100k rows)
  2. 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.

Workaround

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

Seen In

Istanbul Patch 3a

Fixed In

Kingston

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-05-14 00:05:51
Published:2017-06-22