Publication date: July 18, 2023
CockroachDB automatically creates a primary index for each table, which indexes the table's primary key, or another unique value when there is no primary key. A secondary index is also created automatically for columns with a
UNIQUE constraint. CockroachDB v20.2 and v21.1 (the first two versions to support
ALTER PRIMARY KEY) contained a bug where the index automatically created in some cases to provide uniqueness for the existing primary key would have its
EncodingType field marked as
PrimaryIndexEncoding. This behavior was fixed in v21.2, but left secondary indexes with primary encoding.
In CockroachDB v22.2.0 and above, the implementation for
ALTER TABLE began using the new declarative schema changer framework. When adding a new column using the new declarative schema changer, a bug caused the incorrect retrieval of the unique secondary index rather than the primary index. As a result, the secondary index is modified to store the new columns rather than the primary index. This causes the new column data to only be present in the secondary index, which could lead to irrecoverable data loss if the secondary index is deleted by the user. Furthermore, it could lead to inconsistent query results as the primary index does not contain data corresponding to the new column.
If you have gone through the following sequence of steps, you are potentially at risk of data inconsistency and loss:
- Your cluster was on v20.2 or v21.1 at any point.
- You executed
ALTER PRIMARY KEYwhile on v20.2 or v21.1.
- You upgraded to v22.2 and executed an
ALTER TABLE..ADD COLUMNstatement.
This is resolved in CockroachDB by #105828, which makes CockroachDB automatically repair the encoding type for secondary indexes when accessing the table. In summary, this encoding type fix will prevent this issue from occurring, and additional validation will be added to prevent data loss.
This public issue is tracked by #99561.
Users of CockroachDB who have had v20.2 or v21.1 deployed on their cluster should run the following query to check if your cluster is in a potentially corrupt state. If the query returns any data, contact our support team to resolve the inconsistency. Until our support team has had a chance to fix the cluster, refrain from running any schema changes on the impacted table including
DROP INDEX or
ALTER TABLE as it could lead to data loss.
If the query does not return any data, your cluster has not been impacted by this issue. No further action is necessary.
Before running the query to detect if secondary indexes or primary indexes store columns incorrectly, note:
object_nameis the name of the table.
typeis the type of corruption.
index_name_or_col_nameis the impacted index or column.
The detected types are:
"secondary index has incorrect encoding type": The secondary index is seen as a primary index, but columns are correctly stored.
"columns are not stored in primary index": Some columns are stored only in this secondary index.
"column may be missing data": There are no indexes storing this column anymore.
WITH descriptors AS ( SELECT id, crdb_internal.pb_to_json( 'cockroach.sql.sqlbase.Descriptor', descriptor, false ) AS descriptor FROM system.descriptor ), table_descriptors AS ( SELECT id, descriptor -> 'table' AS descriptor FROM descriptors WHERE (descriptor -> 'table') IS NOT NULL AND ( descriptor -> 'table' -> 'viewQuery' ) IS NULL ), column_ids AS ( SELECT id, json_array_elements(descriptor -> 'columns')-> 'id' AS columnid, json_array_elements(descriptor -> 'columns')-> 'name' AS columnname, ( json_array_elements(descriptor -> 'columns')-> 'virtual' ):: STRING AS virtual FROM table_descriptors ), primary_storing_columns AS ( SELECT id, json_array_elements( descriptor -> 'primaryIndex' -> 'storeColumnIds' ) AS storecolumnid FROM table_descriptors ), primary_key_columns AS ( SELECT id, json_array_elements( descriptor -> 'primaryIndex' -> 'keyColumnIds' ) AS keycolumnid FROM table_descriptors ), secondary_indexes AS ( SELECT id, json_array_elements(descriptor -> 'indexes') AS idx FROM table_descriptors ), bad_secondary_indexes_store_columns AS ( SELECT id, idx -> 'name' AS name, json_array_elements(idx -> 'storeColumnIds') AS storecolumnid FROM secondary_indexes WHERE (idx -> 'encodingType'):: INT8 = 1 ), bad_secondary_indexes AS ( SELECT id, name FROM bad_secondary_indexes_store_columns AS bsc WHERE storecolumnid NOT IN ( SELECT storecolumnid FROM primary_storing_columns AS psc WHERE psc.id = bsc.id ) ), bad_secondary_indexes_with_encoding AS ( SELECT id, idx -> 'name' AS name FROM secondary_indexes WHERE (idx -> 'encodingType'):: INT8 = 1 AND (id, idx -> 'name') NOT IN ( SELECT id, name FROM bad_secondary_indexes ) ), primary_index_is_missing_data AS ( SELECT id, columnid, columnname FROM column_ids AS cs WHERE "virtual" != 'true' AND columnid NOT IN ( SELECT storecolumnid FROM primary_storing_columns AS psc WHERE psc.id = cs.id UNION SELECT keycolumnid FROM primary_key_columns AS ksc WHERE ksc.id = cs.id UNION SELECT storecolumnid FROM bad_secondary_indexes_store_columns AS bsc WHERE bsc.id = cs.id ) ) SELECT tbl.database_name || '.' || tbl.schema_name || '.' || tbl.name AS object_name, 'columns are not stored in primary index' AS type, idx.name AS index_name_or_col_name FROM bad_secondary_indexes AS idx, crdb_internal.tables AS tbl WHERE table_id = id UNION SELECT tbl.database_name || '.' || tbl.schema_name || '.' || tbl.name AS object_name, 'secondary index has incorrect encoding type' AS type, idx.name AS index_name_or_col_name FROM bad_secondary_indexes_with_encoding AS idx, crdb_internal.tables AS tbl WHERE table_id = id UNION SELECT tbl.database_name || '.' || tbl.schema_name || '.' || tbl.name AS object_name, 'column maybe missing be data' AS type, badcols.columnname AS index_name_or_col_name FROM primary_index_is_missing_data AS badcols, crdb_internal.tables AS tbl WHERE table_id = id;
Upon upgrade to CockroachDB v22.2.0 and above, a bug from v20.2 and v21.1 could cause the secondary index to be modified upon
ALTER TABLE..ADD COLUMN instead of the existing primary index. If the user drops the secondary index, all data related to the new column will be deleted resulting in irrecoverable data loss. Furthermore, until this cluster is repaired, there is a risk of inconsistent query results as the primary index does not contain data for the newly added columns.
Questions about any technical alert can be directed to our support team.