http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> In the RESTORE command process on ...
# questions-and-troubleshooting
s
@Rocky In the RESTORE command process on a Shared-Nothing architecture, if the target database already has some partitions created as part of a table, and the snapshot has more partitions in it, will it fail the restore? For example, the current database state has snapshots from September 15th to September 20th. The backup we're trying to restore has partitions from September 12th to September 20th. Would this cause issues like 
mismatched schema hash
when running the 
RESTORE SNAPSHOT
process?
r
In a Shared-Nothing architecture, the 
RESTORE
process behavior for existing tables and partitions is as follows: 1. Will the restore fail? It depends on whether you are restoring the whole table or specific partitions. * Whole Table Restore: If you restore the entire table (e.g., 
ON (TABLE my_table)
), StarRocks will attempt to overwrite the existing table. * If the column schemas match (same columns, types, keys, etc.), the restore will succeed. * The Result: The target table's existing partitions (Sept 15–20) will be entirely replaced by the partitions in the snapshot (Sept 12–20). You will lose any data in the Sept 15–20 range that was not part of that specific backup. * Partition-Level Restore: If you attempt to restore only the "missing" partitions (Sept 12–14) into the existing table: * In StarRocks, a 
RESTORE
to an existing table typically requires the target partition to already exist if you are overwriting. * If the partitions (Sept 12–14) do not exist in the target table, the 
RESTORE
job may fail because it cannot find a matching metadata destination within the existing table structure. You would usually need to 
ALTER TABLE ... ADD PARTITION
first with the exact same range/name before restoring into them, or restore the whole table. 2. The "Mismatched Schema Hash" Error The 
mismatched schema hash
error is specifically related to the table's column definition, not the number of partitions. * What causes it: This error occurs when the internal 
schema_hash
of the table in the snapshot does not match the 
schema_hash
of the existing table in the target database. * Why it happens: This usually means the table structure was altered between the time the backup was taken and the time of the restore (e.g., a column was added, dropped, or a data type was changed). * Partition Influence: Simply having more or fewer partitions does not change the 
schema_hash
. However, if you changed the partitioning column or the bucketing column, that would cause a metadata inconsistency and potentially a failure (though usually a different error than schema hash). Summary & Recommendations * If you want to replace the current data with the backup: Ensure the column schemas are identical. The restore will succeed but will overwrite all existing partitions in that table. * If you want to "merge" the data (keep your current Sept 15–20 and add Sept 12–14): 1. Restore the snapshot to a new table alias (e.g., 
ON (TABLE my_table AS my_table_backup)
). 2. Use 
INSERT INTO my_table SELECT * FROM my_table_backup WHERE ...
to pull the missing data into your current table. 3. Alternatively, 
ALTER TABLE my_table ADD PARTITION ...
for the missing dates, and then try a 
RESTORE
of only those specific partitions. * If you see `mismatched schema hash`: You must either drop the existing table in the target database before restoring or restore it with a different name (
AS alias
). This error confirms that the table structure in your cluster is different from what is in the backup. For more details, you can refer to the StarRocks Restore Documentation. References • sql-reference/sql-statements/backup_restore/RESTORE.md • administration/management/Backup_and_restore.md • table_design/data_distribution/expression_partitioning.md
s
@Rocky The table definitions are the same between the restore and the current table state.
r
If the table definitions (columns, keys, properties, etc.) are identical, the 
RESTORE
process for a table in a Shared-Nothing architecture will not fail simply because of a difference in the number or range of partitions. However, there are critical behaviors regarding how the data is handled: 1. The Restore is an "Overwrite," Not a "Merge" When you run 
RESTORE ... ON (TABLE my_table)
, StarRocks treats the operation as a replacement of the existing table's metadata and data. * Result: The table in your database (Sept 15–20) will be replaced entirely by the table in the snapshot (Sept 12–20). * What happens to existing data: The data for Sept 15–20 that currently exists in your cluster will be overwritten by the versions of those same partitions found in the snapshot. Any partitions that existed in the snapshot but not in the current table (Sept 12–14) will be created. 2. Does "Mismatched Schema Hash" apply here? No, a difference in the number of partitions does not trigger a 
mismatched schema hash
error. * The Schema Hash is a checksum of the table's structural metadata (column types, column order, keys, bucketing columns, etc.). * As long as the table schema (the 
CREATE TABLE
definition) is the same, the schema hash check will pass. * This error only occurs if the live table has undergone a structural change (e.g., 
ADD COLUMN
, 
MODIFY COLUMN
) that isn't reflected in the backup. 3. Scenario Analysis * Case A: You restore the whole table. The operation will succeed. Your table will now have partitions from Sept 12 to Sept 20. Note that the data for Sept 15–20 will be replaced by whatever was in the backup. * Case B: You want to keep your current Sept 15–20 data and only "add" Sept 12–14. If you restore the whole table, you will lose any new data written to the Sept 15–20 partitions since the backup was taken. To "merge" them instead: 1. Restore the snapshot to a new table name: 
ON (TABLE my_table AS my_table_temp)
. 2. Check the data in 
my_table_temp
. 3. Manually add the missing partitions to your original table. 4. Use 
INSERT INTO my_table SELECT * FROM my_table_temp WHERE date < '2024-09-15'
to fill the gap. Summary * Will it fail? No. * Will it merge? No, it will overwrite the entire table structure and data with the state in the snapshot. * Mismatched schema hash? Not triggered by partitions; only triggered by changes to columns or table properties. Recommendation: If you only need the missing days and want to preserve the current state of the existing partitions, restore the table under an alias (
AS alias_name
) first to avoid overwriting your live data. References • sql-reference/sql-statements/backup_restore/RESTORE.md • administration/management/Backup_and_restore.md References • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/RESTORE.md
s
@Rocky There was no structural change between the backup and the restore. How can I see what mismatches?
r
If your table definitions appear identical but you are still receiving a 
mismatched schema hash
error, it means the internal metadata checksum of the existing table does not match the checksum recorded in the snapshot. Even when the 
CREATE TABLE
syntax is the same, certain internal attributes can cause the schema hash to differ. Here is how you can identify and see the mismatches: 1. View the Schema Hash of the Existing Table You can view the current schema hash for your live table by inspecting its tablets. The schema hash is consistent across all tablets of the same table (unless a schema change is in progress). Run the following command:
Copy code
sql
SHOW TABLETS FROM target_db.my_table LIMIT 1;
Look for the 
SchemaHash
 column in the output. This is the value StarRocks is comparing against the backup. 2. View the Schema Hash in the Backup There is no direct SQL command to "peek" into the snapshot's schema hash while it is in the repository. To find the hash of the backup: 1. Restore with an Alias: Restore the table under a different name so the schema check is bypassed (since it's a "new" table).
Copy code
sql
    RESTORE SNAPSHOT my_backup FROM my_repo
    ON (TABLE my_table AS my_table_check)
    PROPERTIES ("backup_timestamp" = "...");
2. Check the Alias Hash: Once restored, run the same tablet check on the new table:
Copy code
sql
    SHOW TABLETS FROM target_db.my_table_check LIMIT 1;
3. Compare: Compare the 
SchemaHash
from 
my_table
and 
my_table_check
. If they are different, StarRocks considers them incompatible for a direct overwrite. 3. Common Causes for Mismatches (When SQL looks the same) If the hashes are different, it is usually due to one of the following "hidden" differences: * Column IDs: If you dropped and recreated the table, the internal IDs assigned to columns might have changed, even if the names and types are identical. * Bloom Filter Columns: If one table has 
bloom_filter_columns
defined and the other doesn't (or they differ), the hash will change. * Default Values or Nullability: Ensure 
NULL
vs 
NOT NULL
and default value strings are character-for-character identical. * Index Differences: Differences in bitmap indexes or other secondary indexes. * Version Mismatch: If the backup was taken on a much older version of StarRocks, the way the hash is calculated might have changed. In the 
RESTORE
command, you can sometimes use the property 
"meta_version" = "..."
to force compatibility, though this is rare. Recommendation If you need to move forward immediately and the schemas are logically the same, the safest path is: 1. Drop the existing table in the target database (after backing it up if necessary). 2. Run the Restore. Because the table doesn't exist, StarRocks will create it using the metadata from the snapshot, avoiding the hash check. 3. Alternatively, continue using the Alias Restore method and then use 
INSERT INTO ... SELECT
to move the data. References • sql-reference/sql-statements/backup_restore/RESTORE.md • administration/management/Backup_and_restore.md References • sql-reference/sql-statements/backup_restore/SHOW_BACKUP.md • knowledge/admin_and_operation/shared_data_compaction.md
s
@Rocky There's no "SchemaHash". There's VersionHash (it's 0), CheckVersionHash (0), Checksum (-1)
r
If the 
SchemaHash
column is missing from your 
SHOW TABLETS
output, you are likely on a newer version of StarRocks where certain tablet-level metadata fields have been streamlined or hidden in the standard view. To find the actual schema hash that StarRocks is complaining about, use the 
SHOW PROC
 command, which provides the raw metadata used by the Frontends (FEs) and Backends (BEs). 1. Locate the Schema Hash via 
SHOW PROC
 Since the error is a mismatch between the current table and the backup, you need to find the ID of the table first: 1. Get Table ID:
Copy code
sql
    SHOW CREATE TABLE target_db.my_table;
    -- Or check the TableId in SHOW TABLET ... LIMIT 1
2. Navigate the PROC path: Find the path to a specific tablet. You can get this path from the 
DetailCmd
column of `SHOW TABLET <tablet_id>`:
Copy code
sql
    SHOW PROC '/dbs/<db_id>/<table_id>/partitions/<partition_id>/<index_id>';
In the output of this command, you will see a list of tablets. Look for the 
SchemaHash
 column here. 2. If the Schemas "Look" Identical but the Hash Differs If the 
SHOW CREATE TABLE
output is identical, the mismatch is almost always caused by internal metadata markers that aren't visible in the SQL syntax. Common culprits include: * Internal Column IDs: Even if column names are the same, if the table was dropped and recreated, StarRocks assigns new internal `ColumnId`s. A backup from the "old" table will have different IDs than the "new" table, triggering a mismatch. * Property Order or Defaults: Small differences in 
PROPERTIES
(like 
replication_num
, 
storage_medium
, or 
bloom_filter_columns
) can change the hash if they were explicitly set in one environment but left as defaults in another. * Table Evolution: If you added a column and then dropped it, the "Schema Version" or the list of historical column IDs in the metadata might still exist, making it different from a fresh table created with the final schema. How to Resolve the Mismatch Since you cannot manually change a table's schema hash to match a backup, the standard workarounds are: 1. The Alias Method (Safest): Restore the table with a different name:
Copy code
sql
    RESTORE SNAPSHOT my_backup FROM my_repo ON (TABLE my_table AS my_table_temp);
Because it's a new table name, StarRocks creates it from scratch using the backup's metadata. You can then 
INSERT INTO my_table SELECT * FROM my_table_temp
to merge the data. 2. The Drop-and-Restore Method: If you don't need the data currently in `my_table`: * 
DROP TABLE my_table;
* Run the 
RESTORE
command. StarRocks will recreate the table using the exact metadata (and schema hash) stored in the snapshot. Summary If the 
SchemaHash
column is not in your 
SHOW TABLETS
output, use 
SHOW PROC
 on the tablet path to see the internal hash. However, identifying the exact byte that differs is difficult; if the error persists, using the 
AS
alias to restore to a temporary table is the most reliable way to bypass the check and inspect the data. References • sql-reference/sql-statements/backup_restore/RESTORE.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md • administration/management/Backup_and_restore.md References • knowledge/admin_and_operation/shared_data_compaction.md
s
@Rocky I've noticed it happens for a table that we specifically do a ALTER TABLE ... SWAP WITH. Might this cause issue at the checksum level? The migrations are run by an automated tool such as Flyway/Goose. There's nothing manually done apart from the migration files, so the ops are standardized in all clusters we have.
r
Yes, your suspicion is correct. The 
ALTER TABLE ... SWAP WITH
 operation is the most likely cause of the 
mismatched schema hash
error, even if your DDL (the 
CREATE TABLE
syntax) remains identical across clusters. Why 
SWAP WITH
causes a Mismatch In StarRocks, a "Schema" is not just the list of columns and types you see in a SQL query. The Schema Hash is a checksum of the internal metadata, which includes: 1. Internal Column IDs: When you create a table, StarRocks assigns an internal integer ID to every column. If you create a "shadow" table for a migration and then 
SWAP
, that new table has its own set of Column IDs. Even if the column names are the same, 
Col_1
in your backup might have ID 
1001
, while 
Col_1
in your live table (after a swap) might have ID 
5005
. 2. Table/Index IDs: The hash calculation often incorporates internal structural markers that change when a table is recreated or swapped from a temporary table. 3. Migration Tool Behavior: Tools like Flyway or Goose often implement certain migrations by creating a new table with the "new" schema, copying data, and then swapping. This process guarantees that the internal IDs and the metadata "lineage" of the live table will diverge from any backup taken before that swap occurred. How to verify the mismatch Since you didn't see 
SchemaHash
in the tablet list, you can find the raw value using the internal process view: 1. Get the Table ID: 
SHOW CREATE TABLE my_table;
(Look for the ID in the comments or metadata). 2. Navigate the PROC tree:
Copy code
sql
    -- Replace <db_id> and <table_id> with your actual IDs
    SHOW PROC '/dbs/<db_id>/<table_id>/index_schema';
This will return the internal schema versions and hashes. If you do this for both your live table and a table restored to an alias, you will see the hashes differ. The Solution for Automated Environments Because your migration tool is changing the internal metadata lineage via 
SWAP
, you cannot perform a direct "Overwrite Restore." You have two reliable options: * Option A: Restore to an Alias (Recommended) Restore the backup to a temporary name, then move the data. This bypasses the hash check because it's a new table.
Copy code
sql
    RESTORE SNAPSHOT my_repo.my_backup
    ON (TABLE live_table AS temp_table_for_restore);

    -- Then manually sync data or swap again if appropriate
    INSERT INTO live_table SELECT * FROM temp_table_for_restore;
* Option B: Drop and Restore If you don't mind the table being offline for a few seconds: 1. 
DROP TABLE live_table;
2. 
RESTORE SNAPSHOT ... ON (TABLE live_table);
Without an existing table to "compare" against, StarRocks will simply recreate the table using the exact metadata and IDs stored in the snapshot. Summary Your automated migrations are creating "structurally identical" but "internally different" tables. StarRocks' safety check sees the different internal Column IDs/Hashes and blocks the restore to prevent data corruption. Use the Alias method to safely inspect or merge the data. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/backup_restore/RESTORE.md • administration/management/Backup_and_restore.md References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md
s
@Rocky What would be a way of corroborating this? Checking if that's really the issue.
r
To corroborate that the 
SWAP WITH
operation is the cause, you can compare the internal Schema Hash and Column IDs of your live table against a "clean" version of the table restored from your backup. Since you couldn't find the 
SchemaHash
in 
SHOW TABLETS
, it is likely tucked away in the Frontend's internal process metadata (
SHOW PROC
). Step 1: Restore the Backup to an Alias First, restore the table from your backup repository to a temporary name. This bypasses the structural check because it is a "new" table.
Copy code
sql
RESTORE SNAPSHOT my_repo.my_backup 
ON (TABLE my_table AS my_table_temp);
Step 2: Compare Internal Metadata Now, use the 
SHOW PROC
command to look at the "brain" of both tables. You will need the Database ID and Table IDs. 1. Find the IDs:
Copy code
sql
    -- Find your DB ID
    SHOW PROC '/dbs';

    -- Find the Table IDs for both the live and temp tables
    SHOW PROC '/dbs/<your_db_id>';
2. Compare the Index Schema (Where the Hash lives): Run these two commands and compare the outputs:
Copy code
sql
    -- For your live table
    SHOW PROC '/dbs/<db_id>/<live_table_id>/index_schema';

    -- For your restored temp table
    SHOW PROC '/dbs/<db_id>/<temp_table_id>/index_schema';
What to look for: Look at the 
SchemaHash
 column in the results. If they are different, that is exactly why your restore is failing. Step 3: Compare Column IDs (The "Smoking Gun") If the 
SchemaHash
is different, it is almost certainly because the internal IDs assigned to the columns don't match. * Live Table: Because of the 
SWAP WITH
, this table was likely created as a "shadow" table during a migration. StarRocks increments a global counter for Column IDs. * Backup Table: This table preserves the Column IDs from the moment it was originally created (likely months or years ago). You can see these IDs by looking at the details of a single tablet: 1. Pick one tablet ID from each table: 
SHOW TABLETS FROM my_table LIMIT 1;
2. Run: 
SHOW TABLET <tablet_id>;
3. Scroll down to the 
DetailCmd
 column. It will provide a long 
SHOW PROC
path like: 
SHOW PROC '/dbs/1001/1002/partitions/1003/1004/1005';
4. Execute that 
DetailCmd
. In the output, look for the 
ColumnId
 or 
IndexId
 fields. Why this confirms the 
SWAP
theory If your DDL (the 
CREATE TABLE
text) is identical but the 
SchemaHash
or `ColumnId`s differ, it confirms that the physical lineage of the tables has diverged. Migration tools that use 
SWAP WITH
effectively replace the table's "DNA" (Internal IDs) while keeping the "Face" (SQL Schema) the same. StarRocks 
RESTORE
requires the DNA to match exactly to perform an overwrite. Recommendation: To stay "in sync" with your automated migrations, the best practice is to Drop and Restore rather than overwriting an existing table, as this allows the restored table to bring its original physical IDs with it. References • knowledge/usage/best-practice.md • table_design/table_design.md • knowledge/admin_and_operation/shared_data_compaction.md
2 Views
Open in Slack
PreviousNext
Powered by