Publication date: April 12, 2022
Description
In a multi-region database, calling IMPORT INTO on a REGIONAL BY ROW table could violate UNIQUE constraints, including PRIMARY KEY constraints, resulting in duplicate keys.
Affected versions: v21.2.0 to v21.2.7
Statement
This is resolved in CockroachDB by PR 79293. The fix was applied in the maintenance release of CockroachDB v21.2.8.
This public issue is tracked by 79281.
Mitigation
Users of CockroachDB v21.2.0 to v21.2.7 who have used IMPORT INTO must audit their REGIONAL BY ROW tables with UNIQUE constraints to ensure these tables do not contain UNIQUE constraint violations.
Users should also upgrade to v21.2.8 or a later version.
v21.2.6 or later
Users of CockroachDB v21.2.6 and later have access to the following built-in functions, which can be used to easily validate UNIQUE constraints:
SELECT crdb_internal.revalidate_unique_constraints_in_all_tables();SELECT crdb_internal.revalidate_unique_constraints_in_table('table_name');SELECT crdb_internal.revalidate_unique_constraint('table_name', 'constraint_name');
If any constraint fails validation, the functions will return an error with a hint about which data caused the constraint violation. These violations can then be resolved manually by updating or deleting the rows in violation.
Prior to v21.2.6
Users of CockroachDB releases prior to v21.2.6 may audit the table for duplicate keys by running a query of the following form, for each UNIQUE constraint that may have been affected:
SELECT * FROM t GROUP BY unique_col1 [, unique_col2 ...] HAVING count(*) > 1;
If a query returns any results, this indicates that the table contains UNIQUE constraint violations. To fix the violations, update all but one of the duplicate keys with a different value.
Impact
REGIONAL BY ROW tables spanning multiple regions could contain non-unique data in unique columns if IMPORT INTO was used to import data with duplicate keys. Versions affected include v21.2.0 to v21.2.7, v22.1.0-alpha.1 to v22.1.0-alpha.5, and v22.1.0-beta.1.
Please reach out to the support team for more information.
Was this helpful?
