DECIMAL256 Index Upgrade

Indexes containing DECIMAL256 values written by an older index writer may have missing key components or rows. The repaired decoder cannot recover those old keys; rebuild affected indexes from their base tables before reopening queries.

Scope

DECIMAL precision above 38 uses DECIMAL256. Inspect ordinary and UNIQUE indexes, including composite keys and hidden primary-key suffixes. The known path includes LOAD DATA into a table with foreign keys and foreign_key_checks enabled, where the legacy planner handles Parquet DECIMAL256 input. A failed ordinary import does not prove that historical indexes are unaffected.

Syntax

ALTER TABLE database_name.table_name DROP INDEX index_name;
CREATE [UNIQUE] INDEX index_name ON database_name.table_name(column_list);

Arguments

  • index_name and column_list must come from the saved original index definition, including column order and options.

  • Rebuild from the base table. Copying entries from the old hidden index table preserves the damaged keys.

Upgrade procedure

  1. Take a recoverable backup and stop user reads and writes, imports, CDC, tasks, and other background writers across all accounts. Wait for in-flight transactions to finish.

  2. In every account, inventory tables containing DECIMAL256 and their ordinary, UNIQUE, and RTREE indexes. MatrixOne source provides the read-only etc/decimal256_index_inventory.sql; its output is conservative and may include unaffected indexes. Save SHOW CREATE TABLE and SHOW INDEX output for each table.

  3. Stop all old CN instances. Start the fixed version as a maintenance CN while user and background writes remain stopped. Drop and recreate each affected index using its saved definition. DROP and CREATE are separate DDL operations; keep the maintenance window open if a rebuild fails.

  4. Check definitions and compare complete ordered results with FORCE INDEX and IGNORE INDEX for equality, supported ranges, large positive and negative values, and NULL. Use EXPLAIN to confirm the index path. Check UPDATE, DELETE, and UNIQUE rejection on a reversible test table or transaction.

  5. Reopen traffic only after inventory, rebuild, and verification have completed for every account. On failure, restore the complete pre-upgrade backup and matching old binaries; do not return a partially rebuilt data directory to old writers.

Example

The following SQL shows the DDL sequence on a disposable table. Replace it with the saved original definition for a real index.

DROP DATABASE IF EXISTS decimal256_upgrade_demo;
CREATE DATABASE decimal256_upgrade_demo;
USE decimal256_upgrade_demo;
CREATE TABLE amounts (id INT PRIMARY KEY, amount DECIMAL(50,0));
INSERT INTO amounts VALUES (1, 1234567890123456789012345678901234567890);
CREATE INDEX amount_idx ON amounts(amount);
ALTER TABLE amounts DROP INDEX amount_idx;
CREATE INDEX amount_idx ON amounts(amount);
SELECT id FROM amounts WHERE amount = 1234567890123456789012345678901234567890;
DROP DATABASE decimal256_upgrade_demo;