# Technical Advisory 62842

Publication date: July 29, 2021

## Description

In 20.2, the implementation of `TRUNCATE` changed to keep the table ID stable. Its new behavior is to replace the existing indexes with new, empty indexes. This new code had a bug when it came to indexes which were not yet public (i.e., indexes which were still in the process of being added to the table). A `TRUNCATE` would cause this partially-backfilled index to be made public instead of being dropped, exposing stale pre-truncation data to the query engine.

This issue affects CockroachDB v20.2.0 to v20.2.8.

## Statement

This bug was resolved by #62944.

The fix has been applied to the maintenance release v20.2.9 of CockroachDB by PR #63153.

This public issue is tracked by #62842.

## Detection

1. Check for `TRUNCATE` in logs.

2. Check for out-of-order secondary index ID vs primary index ID:

      WITH descs AS (
                    SELECT d
                      FROM (
                            SELECT crdb_internal.pb_to_json(
                                    'cockroach.sql.sqlbase.Descriptor',
                                    descriptor,
                                    false
                                   )->'table' AS d
                              FROM system.descriptor
                           )
                     WHERE d IS NOT NULL
                 ),
           bad_descs AS (
                        SELECT d, idx
                          FROM (
                                SELECT d,
                                       d->'primaryIndex'
                                        AS pi,
                                       json_array_elements(
                                        d->'indexes'
                                       ) AS idx
                                  FROM descs
                               )
                         WHERE (pi->>'id')::INT8
                               > (idx->>'id')::INT8
                     )
    SELECT database_name,
           schema_name,
           name AS table_name,
           table_id,
           idx->>'name' AS index_name,
           (idx->>'id')::INT8 AS index_id
      FROM crdb_internal.tables
      JOIN bad_descs ON (table_id = (d->>'id')::INT8);

In the following example of query output, the index `idx2` of table `tpcc.public.order_line` is identified as possibly corrupted:

      database_name | schema_name | table_name | table_id | index_name | index_id
    ----------------+-------------+------------+----------+------------+-----------
      tpcc          | public      | order_line |       58 | idx2       |        2
    (1 row)

## Mitigation

Any suspect indexes should be dropped and rebuilt.

To prevent this issue, do not `TRUNCATE` a table while an index is being built.

Users of CockroachDB v20.2 are encouraged to upgrade to v20.2.9 or a later version.

## Impact

All deployments running CockroachDB versions 20.2.0 up to and including 20.2.8 are affected. Cockroach Labs acknowledges the issue and has applied a fix that is available in subsequent maintenance releases for 20.2 (20.2.9 and later).