# Technical Advisory 99561

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.