> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Technical Advisory 62842

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

Publication date: July 29, 2021

## Description

In 20.2, the implementation of [`TRUNCATE`](https://www.cockroachlabs.com/docs/v20.2/truncate) changed to keep the table ID stable. Its new behavior is to replace the existing [indexes](https://www.cockroachlabs.com/docs/v20.2/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 <InternalLink version="releases" path="v20.2">v20.2.0 to v20.2.8.</InternalLink>

## Statement

The fix has been applied to the maintenance release v20.2.9 of CockroachDB.

## Detection

1. Check for `TRUNCATE` in logs.
2. Check for out-of-order secondary index ID vs primary index ID:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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).