Known Error of the Week: Creating a UNIQUE index with online alter enabled corrupts the table

[heidi_schnakenb]

This week we're zeroing in on a important User Interface issue that could impact performance if not addressed carefully. Let's take a look at KB0549424: Creating a UNIQUE index via the UI with online alter enabled corrupts the table if the da... and determine what we need to know.

Last summer, we received reports from customers that data was lost when they attempted to add a unique index via the UI. At the time, we were unable to reproduce the issue and had not seen additional cases until more users started upgrading in late fall. A closer look determined that online alter was enabled and the online alter process needed to validate that the data was unique prior to modifying the index table. Failure to do so resulted in records not being inserted into the new table structure and data being lost. This was due to the INSERT IGNORE bypassing the unique key violations.

After adding indexes with a unique constraint, you will see these symptoms if your instance is affected:

• Table corruption
• Data loss
• Failure to insert records into the table

This issue was found to have originated in Calgary, when online alter was introduced. We investigated this issue and implemented a fix in Eureka Patch 8.

For customers who are unable to upgrade to Eureka Patch 8 and are using releases from Calgary to Eureka Patch 7, the following workaround is recommended:

To prevent the loss of data when a unique index is needed and an upgrade is not possible, please use the script below to verify the contents of the column manually before adding the index. The user can determine if there are any duplicate values before creating a unique index by using a script in "Script - Background" such as:

// This script will: select a, count(*) from table and group by a having count(*) > 1
var tableName = 'table_we_will_add_the_index_to';
var columnName = 'column_that_will_get_the_new_index';

var ga = new GlideAggregate(tableName);
ga.addAggregate('count', columnName);
ga.groupBy(columnName);
ga.addHaving('count','>','1');
ga.query();
if (ga.next()) {
   gs.print('The column ' + columnName + ' on table ' + tableName + ' has duplicates.   Creating a unique index will result in data loss.');
} else {
   gs.print('The column ' + columnName + ' on table ' + tableName + ' does not have duplicate values.');
}

If non-unique values are found, remove or rename the duplicate values so they are unique.   Once completed, creating a unique index will no longer result in data loss.