@Rocky How to implement sync table data between two cluster(different version)

To synchronize table data between two StarRocks clusters, especially when they are of different versions, you have two primary recommended methods: using the Cross-cluster Data Migration Tool or using StarRocks External Tables. Method 1: Cross-cluster Data Migration Tool (

starrocks-cluster-sync

) This is the official tool designed for migrating or synchronizing data between two StarRocks clusters. It is particularly useful when dealing with different versions because it includes a compatibility setting. 1. Preparations on the Target Cluster To ensure the target cluster can receive data from an older or different version, you must enable Legacy Compatibility: * Check status:

sql
    ADMIN SHOW FRONTEND CONFIG LIKE 'enable_legacy_compatibility_for_replication';

* Enable dynamically:

sql
    ADMIN SET FRONTEND CONFIG("enable_legacy_compatibility_for_replication" = "true");

* Permanent setting: Add

enable_legacy_compatibility_for_replication = true

to your

fe.conf

to prevent it from resetting on restart. 2. Run the Migration Tool 1. Download and install the

starrocks-cluster-sync

tool on the target cluster server. 2. Configure the tool: Set the source and target cluster FE addresses, ports, and user credentials. 3. Execute the sync: The tool will create synchronization tasks for the specified tables. ────────── Method 2: StarRocks External Table If you want to manually trigger synchronization or handle specific tables, you can use the StarRocks External Table feature (supported since v1.19). This allows one cluster to read from or write to another. Option A: Pull Data (Execute on Target Cluster) 1. Create an External Table in the Target Cluster that points to the source table:

sql
    CREATE EXTERNAL TABLE source_ext_table (
        column1 type,
        column2 type
    )
    ENGINE=StarRocks
    PROPERTIES (
        "host" = "source_fe_ip",
        "port" = "9030",
        "user" = "user",
        "password" = "password",
        "database" = "source_db",
        "table" = "source_table"
    );

2. Synchronize data:

sql
    INSERT INTO target_local_table SELECT * FROM source_ext_table;

Option B: Push Data (Execute on Source Cluster) Alternatively, you can create an external table in the Source Cluster that points to the target and use

INSERT INTO external_table SELECT...

to push data. ────────── Which method should you choose? * Use the Data Migration Tool if you need to sync many tables, large volumes of data, or if there is a significant version gap. It handles the heavy lifting and provides better reliability for full-cluster or multi-table migrations. * Use External Tables for simple, ad-hoc synchronization of specific tables or for scheduled incremental updates via

INSERT INTO SELECT

. Important Note: After completing a migration between different versions using the migration tool, remember to disable

enable_legacy_compatibility_for_replication

to return the cluster to its standard operating mode. References • administration/data_migration_tool.md • integrations/loading_tools/SMT.md • data_source/External_table.md

@Rocky I can't see the Pull Data (Execute on Target Cluster) From doc, But just push data

Based on the StarRocks documentation, the "Pull Data" approach (where the operation is executed on the target cluster to fetch data from a source) is supported through several methods, even if they aren't labeled exactly as "Pull Data" in every section. The document you likely found (StarRocks External Table) focuses on the Push model (executing

INSERT INTO external_table

on the source cluster). For the Pull model (executing on the Target Cluster), here are the recommended methods: 1. Cross-cluster Data Migration Tool (

starrocks-cluster-sync

) This is the official tool for migrating or synchronizing data between two StarRocks clusters. * How it works (Pull-like): It is recommended to install and run this tool on the *target cluster*'s side. It regularly checks the source cluster for updates and initiates synchronization tasks to "pull" the data into the target. * Documentation: Cross-cluster Data Migration Tool * Key Benefit: Handles metadata (DDL) and data synchronization automatically and incrementally. 2. StarRocks External Catalog (SQL Pull) Starting from StarRocks 3.0, you can create a catalog that points to another StarRocks cluster. This allows you to perform a SQL-based pull. * How it works: On the target cluster, create a StarRocks catalog:

sql
    CREATE EXTERNAL CATALOG source_cluster
    PROPERTIES (
        "type" = "starrocks",
        "starrocks.host" = "<source_fe_ip>",
        "starrocks.port" = "9030",
        "starrocks.user" = "root",
        "starrocks.password" = "password"
    );

* Execution: You can then "pull" data into your local table using:

sql
    INSERT INTO target_db.target_table
    SELECT * FROM source_cluster.source_db.source_table;

3. Backup and Restore (Snapshot Pull) For large-scale, one-time migrations: * How it works: You back up the data from the source cluster to a shared remote storage (like S3 or HDFS) and then execute a

RESTORE

command on the target cluster to pull that data in. * Documentation: Backup and Restore Summary of Push vs. Pull * Push Data: Use a StarRocks External Table (ENGINE=olap) on the Source Cluster. You execute

INSERT INTO external_table SELECT...

on the source. * Pull Data: Use a StarRocks External Catalog or the Cross-cluster Data Migration Tool on the Target Cluster. You execute the command or run the tool on the destination side. If you are using a specific management platform (like StarRocks Cloud or Manager) that specifically lists "Pull Data (Execute on Target Cluster)," please let me know, as that refers to a managed migration service with its own interface! References • administration/data_migration_tool.md • data_source/External_table.md

@Rocky Error io.cloudbeaver.DBWebException: Error executing query: SQL Error [1064] [42000]: Cannot invoke "com.starrocks.catalog.MaterializedIndex.getId()" because "index" is null at io.cloudbeaver.service.sql.WebSQLProcessor.processQuery(WebSQLProcessor.java:273) at io.cloudbeaver.service.sql.impl.WebServiceSQL$2.run(WebServiceSQL.java:472) at io.cloudbeaver.model.session.WebSession$1.run(WebSession.java:571) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:119) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [1064] [42000]: Cannot invoke "com.starrocks.catalog.MaterializedIndex.getId()" because "index" is null at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130) at io.cloudbeaver.service.sql.WebSQLProcessor.lambda$1(WebSQLProcessor.java:257) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:192) at io.cloudbeaver.service.sql.WebSQLProcessor.processQuery(WebSQLProcessor.java:214) ... 4 more Caused by: java.sql.SQLSyntaxErrorException: Cannot invoke "com.starrocks.catalog.MaterializedIndex.getId()" because "index" is null at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:770) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:653) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:344) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:128) ... 7 more

Hello! I'm Rocky. This error is a NullPointerException (NPE) occurring within the StarRocks Frontend (FE) metadata catalog. The error

Cannot invoke "com.starrocks.catalog.MaterializedIndex.getId()" because "index" is null

indicates that the StarRocks FE is trying to access a table or a materialized view's index structure, but finding a null reference instead of the expected metadata. This typically happens in the following scenarios: 1. Inconsistent Metadata This is the most common cause. A table or materialized view might have been created or altered, but the operation failed midway, leaving the metadata in an inconsistent state. * Check building status: Run

SHOW ALTER TABLE COLUMN

or

SHOW ALTER MATERIALIZED VIEW

to see if there are any failed or stuck schema change/MV creation jobs. * Identify the table: If this happens when browsing a specific database in CloudBeaver, try to identify which table triggers the error by running

DESC <table_name> ALL

on each table in that database. 2. Client Metadata Queries Tools like CloudBeaver often run background queries to

information_schema

or

SHOW CREATE TABLE

to populate the UI. If a single table in your database has corrupted metadata, the entire "refresh" or "expand" operation in the UI might fail with this error. * Action: Check the CloudBeaver Query Manager to see the exact SQL it was executing when the error occurred. 3. Query Planner Bug In some older versions of StarRocks, the query optimizer might encounter an NPE when trying to choose between the base table and a synchronous materialized view (rollup). * Action: If you are running a specific query, try to simplify it or check if it involves tables with many rollups. Recommended Troubleshooting Steps: 1. Check FE Logs: Look into your StarRocks Leader FE's

fe.log

and

fe.warn.log

. Search for the string

MaterializedIndex.getId()

. The logs will provide a full Java stack trace which often contains the name or ID of the problematic table. 2. Verify Tables: Run

SHOW TABLES

and then

CHECK TABLE <table_name>

(if available in your version) or simply

DESC <table_name> ALL

for the tables you recently modified. 3. Upgrade: Many of these metadata-related NPEs have been fixed in more recent versions. If you are on an older version (e.g., 2.4.x or early 3.x), consider upgrading to the latest maintenance release of your major version. If you can provide the specific SQL query or the version of StarRocks you are using, I can help you investigate further! References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • release_notes/release-2.4.md • sql-reference/information_schema/materialized_views.md

@Rocky SQL Error [1286] [42000]: Getting analyzing error. Detail message: Unknown storage engine 'OLAP_EXTERNAL'.

The error message

Unknown storage engine 'OLAP_EXTERNAL'

indicates that you are attempting to create or access a table with a storage engine name that is not recognized by StarRocks. In StarRocks, the standard engine name for native tables is

olap

. If you are trying to create an external table to connect to another StarRocks cluster, you should still use

ENGINE=olap

along with the

EXTERNAL

keyword and specific properties. Correct Syntax for StarRocks External Tables If your intention was to create an external table to link to another StarRocks cluster, the correct syntax is:

sql
CREATE EXTERNAL TABLE external_t
(
    k1 DATE,
    k2 INT
)
ENGINE=olap  -- Use 'olap' here, not 'OLAP_EXTERNAL'
PROPERTIES
(
    "host" = "127.0.0.1",
    "port" = "9020",
    "user" = "user",
    "password" = "password",
    "database" = "db_test",
    "table" = "destination_table"
);

Supported Storage Engines in StarRocks StarRocks supports the following engine names: *

olap

: The default engine for internal tables and StarRocks-to-StarRocks external tables. *

mysql

: For MySQL external tables. *

elasticsearch

(or

es

): For Elasticsearch external tables. *

hive

: For Hive external tables. *

iceberg

: For Iceberg external tables. *

hudi

: For Hudi external tables. *

jdbc

: For external tables via JDBC. Recommendation 1. Check for typos: Ensure you haven't typed

OLAP_EXTERNAL

by mistake in your DDL statement. 2. Use External Catalogs: If you are using StarRocks v2.3 or later, it is highly recommended to use External Catalogs instead of the

CREATE EXTERNAL TABLE

syntax for better performance and easier management. For example, to query another StarRocks cluster or a data lake, you can set up a StarRocks or Hive catalog. 3. Check Source of SQL: If this SQL was generated by a migration tool or a GUI, you may need to manually update the

ENGINE

parameter to

olap

. References • sql-reference/System_variable.md • sql-reference/information_schema/engines.md • administration/Meta_recovery.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/FE_configuration.md

@Rocky Whether need the version same ？

In a StarRocks cluster, the general rule is that while nodes can have different versions temporarily during an upgrade, they should all be on the same version for stable long-term operation. Here is the breakdown of how versioning works in StarRocks: 1. In a Stable Cluster For a production environment, all components (FE, BE, and CN) should be running the same version. This ensures feature consistency, metadata compatibility, and overall system stability. 2. During Upgrades (Rolling Upgrade) StarRocks supports rolling upgrades, which means you do not have to shut down the entire cluster. During this process, your cluster will temporarily have mixed versions. To do this safely, you must follow a specific order: * Upgrade BEs and CNs first: They are designed to be backward compatible with the FEs. * Upgrade FEs last: Within the FE group, you must upgrade all Follower FEs first, and finally the Leader FE. * Why this order? If you upgrade the FE to a newer version while BEs are still on an older version, the FE might send requests or metadata formats that the older BEs cannot understand, potentially causing a crash. 3. Version Compatibility Rules * Patch versions: You can generally upgrade directly (e.g., from v3.1.2 to v3.1.5). * Minor versions: It is strongly recommended to upgrade consecutively (e.g., v2.5 -> v3.0 -> v3.1 -> v3.2). Skipping minor versions can lead to metadata migration failures. * Major versions: To move to a new major version (like 3.x), you usually need to be on the latest minor version of the previous major release (e.g., upgrade to v2.5 before moving to v3.0). Summary * Do they need to be the same? Yes, for a healthy stable state. * Can they be different? Yes, but only temporarily during the upgrade process, following the BE/CN -> FE sequence. References • deployment/upgrade.md • deployment/downgrade.md