Technical Advisory 99561

On this page

Publication date: July 18, 2023

Description

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:

1. Your cluster was on v20.2 or v21.1 at any point.
2. You executed ALTER PRIMARY KEY while on v20.2 or v21.1.
3. You upgraded to v22.2 and executed an ALTER TABLE..ADD COLUMN statement.

Statement

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.

The fix has been applied to v22.2.12 and v23.1.6.

This public issue is tracked by #99561.

Mitigation

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.

Additionally, users of CockroachDB v22.2.0–v22.2.11 are encouraged to upgrade to v22.2.12. Similarly, users of v23.1.0—v23.1.5 are encouraged to update to v23.1.6.

Before running the query to detect if secondary indexes or primary indexes store columns incorrectly, note:

• object_name is the name of the table.
• type is the type of corruption.
• index_name_or_col_name is 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" is NULL
    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;

Impact

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.

Was this helpful?