Old CockroachDB index metadata could redirect later column additions

An index that appears disposable can contain the only stored copy of a column after a particular CockroachDB upgrade history. A metadata defect originating in 20.2 and 21.1 left some secondary indexes marked with primary-index encoding. When the newer declarative schema changer later selected an index for a column addition, it could mistake that secondary index for the primary. The new column's data then existed only in the secondary index.

The July advisory A-99561 explains the chain and its data-loss consequence. The 23.1.6 release notes, dated July 24, include additional detection and a guard against dropping an index containing affected column data. Cockroach Labs originally published the advisory July 18. Using the maintenance release date here allows the available fix and disclosure to be considered together.

I like CockroachDB's explicit consistency guarantees, but this issue concerns the representation those guarantees preserve. Replicating an index correctly does not make it safe to drop when metadata has incorrectly made it the only location for a column. The upgrade history is part of understanding that state.

The original defect survived its implementation fix

CockroachDB creates a primary index for a table and uses secondary indexes for additional access paths and uniqueness requirements. The advisory traces this defect to early ALTER PRIMARY KEY support. In 20.2 and 21.1, an index automatically retained to preserve uniqueness of the old primary key could receive an EncodingType value of PrimaryIndexEncoding, despite being a secondary index.

Version 21.2 fixed that behavior for subsequent operations. It did not remove every existing secondary index carrying the earlier encoding value. That left persistent metadata whose interpretation mattered to later code. Updating the implementation that originally produced a descriptor is different from rewriting all descriptors it previously produced.

The later declarative schema changer began handling column additions in 22.2.0. In the affected situation, it retrieved the unique secondary index when it needed the primary index. It then modified that secondary index to store the new column. A historical metadata error became a new data-placement error after another component relied on the old encoding field.

That sequence is why the advisory asks about more than the current version. A cluster that was once on 20.2 or 21.1, executed ALTER PRIMARY KEY there, and later added a column after moving to 22.2 has the described history. The presence of an old release in the history alone does not establish that every table is affected.

Plan choice can expose inconsistent contents

The advisory names inconsistent query results as a consequence before any index is dropped. The primary index lacks the newly added column data, while the affected secondary index contains it. A query's chosen access path can therefore encounter different available contents for the same logical table.

Dropping the secondary index is a more destructive consequence. If it contains the only stored values for the new column, deleting it removes those values. The advisory describes that as irrecoverable data loss. Treating the index as an independently rebuildable access path would rely on an assumption the defective placement has already violated.

This is not a recommendation to reconstruct affected indexes manually. The advisory explicitly tells users with detected problems to contact support and avoid schema changes on the impacted table until it is repaired. That includes DROP INDEX and ALTER TABLE. The restriction follows from where the only surviving data may reside.

My preference is to distinguish an apparently redundant structure from a proven redundant copy. A normal secondary index can be understood through the table definition, but this issue has broken that relationship. The advisory's detection step establishes which stored descriptors need attention before cleanup decisions proceed.

Detection distinguishes several states

The advisory supplies a diagnostic query over system.descriptor, converting stored descriptors to JSON and comparing column identities with the primary and secondary index definitions. It distinguishes an incorrect encoding marker from columns stored only in a secondary index. It also looks for nonvirtual columns with no remaining indexed storage identified by the query.

These categories describe different points in the failure sequence. A secondary index can carry the wrong encoding marker while its columns remain correctly stored. Another table can already have columns absent from its primary index but present in the affected secondary index. If the only storing index has disappeared, a column may already be missing data.

The diagnostic reports the table name, a problem type and the relevant index or column. That gives the support investigation concrete object identities. It is more informative than inferring corruption from an application result alone, because it examines the descriptor relationships the advisory identifies as the source of the failure.

The full query belongs in the linked advisory, where its joins and exclusion of virtual columns remain intact. Reducing it to a short test for one encoding value would not detect all the states the published query covers. The source says an empty result means the cluster has not been impacted by this issue.

The maintenance release adds a drop guard

The advisory identifies 22.2.12 and 23.1.6 as fixed releases. It describes automatic correction of secondary-index encoding on table access and additional validation intended to prevent data loss. The 23.1.6 release notes specifically extend cockroach debug doctor to identify indexes that could lose column data if dropped.

They also add a DROP INDEX check to prevent dropping those indexes. That protection addresses a dangerous next action after inconsistent placement has occurred. It is separate from ensuring later column additions select the correct primary index. Both are relevant because existing persistent state can survive a version upgrade.

Users on the affected 22.2 and 23.1 maintenance ranges have an upgrade target, while users whose diagnostic query returns problems have a separate repair path through support. The fixed code prevents further misuse and adds safeguards. The advisory does not make permission to run arbitrary schema cleanup on an affected table a consequence of merely installing the release.