http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> how to change from NOT NULL to NULL...
# questions-and-troubleshooting
l
@Rocky how to change from NOT NULL to NULL able colume CREATE TABLE 
dtm_card_account_info
( 
card_account_no
varchar(255) NOT NULL COMMENT "Số tài khoản thẻ - Khóa chính", 
create_date
datetime NOT NULL COMMENT "Ngày tạo",
r
To change a column from 
NOT NULL
to 
NULL
(nullable) in StarRocks, you use the 
ALTER TABLE ... MODIFY COLUMN
statement. Syntax
Copy code
sql
ALTER TABLE <table_name> MODIFY COLUMN <column_name> <column_type> NULL;
Examples Based on your table definition: 1. To make 
create_date
nullable:
Copy code
sql
   ALTER TABLE dtm_card_account_info MODIFY COLUMN create_date datetime NULL;
2. To make 
card_account_no
nullable: Note: Your comment indicates this is the Primary Key (
Khóa chính
). * In StarRocks, columns that are part of a Primary Key (or a Sort Key in some models) cannot be nullable. They must remain 
NOT NULL
. * If it is a regular column (not a key), you can change it using:
Copy code
sql
     ALTER TABLE dtm_card_account_info MODIFY COLUMN card_account_no varchar(255) NULL;
Important Considerations * NULL to NOT NULL: While you can change a column from 
NOT NULL
to 
NULL
, the reverse operation (changing a 
NULL
column to 
NOT NULL
) is not supported in StarRocks. * Key Columns: Columns defined in the 
PRIMARY KEY
or 
DUPLICATE/UNIQUE/AGGREGATE KEY
clauses must generally be 
NOT NULL
. * Data Types: You must specify the column type in the 
MODIFY COLUMN
statement even if you are only changing the nullability. * Asynchronous Operation: Schema changes in StarRocks are typically asynchronous. You can check the status of the change using:
Copy code
sql
    SHOW ALTER TABLE COLUMN;
References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
l
@Rocky can 
DUPLICATE colume have null value
r
Yes, columns in a Duplicate Key table can have 
NULL
values. This applies to both the key columns (used for sorting) and the value columns. Unlike the Primary Key table model, which requires all key columns to be 
NOT NULL
, the Duplicate Key model is more flexible because it does not enforce a uniqueness constraint. Key points: * Key Columns: You can define the columns in the 
DUPLICATE KEY
or 
ORDER BY
clause as 
NULL
. * Value Columns: These can also be 
NULL
. * Default Behavior: If you do not specify 
NOT NULL
when creating the table, columns in a Duplicate Key table are nullable by default. Example In the example below, 
user_id
is a key column but is allowed to contain 
NULL
values:
Copy code
sql
CREATE TABLE example_table (
    user_id INT NULL,           -- Key column allowing NULL
    event_time DATETIME NOT NULL,
    city VARCHAR(20) NULL       -- Value column allowing NULL
)
DUPLICATE KEY(user_id);
If you check the table structure using 
DESCRIBE example_table;
, you will see that the 
Null
field for these columns is marked as 
YES
. Comparison with other models: * Duplicate Key: Key columns can be 
NULL
. * Primary Key: Key columns must be 
NOT NULL
. * Unique Key: Key columns can be 
NULL
(one 
NULL
value is treated as a unique value). * Aggregate Key: Key columns can be 
NULL
. References • table_design/table_types/duplicate_key_table.md • table_design/StarRocks_table_design.md References • table_design/table_types/duplicate_key_table.md • table_design/StarRocks_table_design.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/table_types.md
l
@Rocky ALTER TABLE dmapp.dtm_card_account_info MODIFY COLUMN card_account_no varchar(255) NULL; SQL Error [1064] [42000]: Invalid column order. value should be after key. index[dtm_card_account_info] Invalid column order. value should be after key. index[dtm_card_account_info]
r
The error 
Invalid column order. value should be after key
occurs because StarRocks requires all Key columns to be positioned before Value columns. When you use 
MODIFY COLUMN
on a Key column without explicitly including the 
KEY
keyword, the system treats it as a "Value" column, which violates the required column order. To fix this, you need to identify your table model and adjust the command accordingly: 1. If it is a Duplicate, Unique, or Aggregate Key Table If 
card_account_no
is a Key column (defined in the 
DUPLICATE/UNIQUE/AGGREGATE KEY
clause), you must include the 
KEY
keyword in your 
ALTER
statement. Corrected Command:
Copy code
sql
ALTER TABLE dmapp.dtm_card_account_info 
MODIFY COLUMN card_account_no varchar(255) KEY NULL;
2. If it is a Primary Key Table If your table was created with the 
PRIMARY KEY
model (which your comment "Khóa chính" suggests), you cannot change a Primary Key column to 
NULL
. * Restriction: In the Primary Key model, all columns belonging to the Primary Key must be 
NOT NULL
. This is a hard requirement for data integrity in this specific model. * Verification: Run 
SHOW CREATE TABLE dtm_card_account_info;
. If you see 
PRIMARY KEY (card_account_no)
, then it is impossible to make this column nullable. Summary of Requirements * Missing Keyword: Always add 
KEY
after the data type if you are modifying a key column in non-Primary Key tables. * Nullability: You can change 
NOT NULL
to 
NULL
, but you cannot change 
NULL
to 
NOT NULL
via 
MODIFY COLUMN
. * Primary Key Model: Primary Key columns cannot be nullable. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/table_types/primary_key_table.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
l
@Rocky
Copy code
ALTER TABLE dmapp.dtm_card_account_info 
MODIFY COLUMN card_account_no varchar(255) KEY NULL;
SQL Error [1064] [42000]: Can not modify distribution column[card_account_no]. index[dtm_card_account_info] Can not modify distribution column[card_account_no]. index[dtm_card_account_info]
r
The error 
Can not modify distribution column
occurs because the column 
card_account_no
is used in the table's distribution key (the 
DISTRIBUTED BY HASH(...)
clause). In StarRocks, you cannot directly modify the properties (like nullability or data type) of a column that is currently part of the distribution key. How to resolve this To change this column to 
NULL
, you must first move it out of the distribution key, modify it, and then (if desired) move it back. Note: These operations are asynchronous and involve data redistribution, so they may take time depending on your data volume. You can check progress with 
SHOW ALTER TABLE COLUMN;
. Step 1: Change the distribution key to a different column Temporarily distribute the table by a different column (e.g., 
create_date
).
Copy code
sql
ALTER TABLE dmapp.dtm_card_account_info 
DISTRIBUTED BY HASH(create_date);
Wait for this operation to finish before proceeding. Step 2: Modify the column to be NULL Now that the column is no longer a distribution column, you can change it:
Copy code
sql
ALTER TABLE dmapp.dtm_card_account_info 
MODIFY COLUMN card_account_no varchar(255) KEY NULL;
Wait for this operation to finish. Step 3: (Optional) Change the distribution key back If you want 
card_account_no
to be the distribution key again:
Copy code
sql
ALTER TABLE dmapp.dtm_card_account_info 
DISTRIBUTED BY HASH(card_account_no);
Alternative: Random Distribution If your StarRocks version is 3.2 or later and it is a Duplicate Key table, you can switch to random distribution to avoid tying the schema to specific columns:
Copy code
sql
ALTER TABLE dmapp.dtm_card_account_info DISTRIBUTED BY RANDOM;
-- Then modify the column
ALTER TABLE dmapp.dtm_card_account_info MODIFY COLUMN card_account_no varchar(255) KEY NULL;
Important Restrictions 1. Primary Key Tables: If this is a Primary Key table (check 
SHOW CREATE TABLE
), you cannot make the primary key column 
NULL
. Primary keys must always be 
NOT NULL
. 2. Partition Columns: If 
card_account_no
is used in 
PARTITION BY
, it cannot be modified at all. You would need to recreate the table. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/Data_distribution.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/Data_distribution.md • best_practices/query_tuning/schema_tuning.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
l
@Rocky SQL Error [1064] [42000]: The table dtm_card_account_info is currently in state OPTIMIZE The table dtm_card_account_info is currently in state OPTIMIZE
r
This error occurs because StarRocks only allows one asynchronous schema change or optimization job to run on a table at a time. The state 
OPTIMIZE
indicates that a background process—likely triggered by your previous attempt to change the distribution key or bucketing—is still in progress. 1. Check the current status You can see the progress of the ongoing optimization by running:
Copy code
sql
SHOW ALTER TABLE OPTIMIZE WHERE TableName = "dtm_card_account_info" ORDER BY CreateTime DESC LIMIT 1;
Look at the 
State
column. You must wait until it shows 
FINISHED
 before you can run another 
ALTER TABLE
command. 2. Check for other schema changes If the above returns nothing, the table might be busy with a column modification instead:
Copy code
sql
SHOW ALTER TABLE COLUMN WHERE TableName = "dtm_card_account_info" ORDER BY CreateTime DESC LIMIT 1;
3. How to proceed * Recommended (Wait): Monitor the status using the commands above. Depending on the data volume, this can take anywhere from a few seconds to several minutes. * Force Stop (Cancel): If you believe the job is stuck or you need to stop it immediately to run a different command, you can cancel it:
Copy code
sql
    CANCEL ALTER TABLE OPTIMIZE FROM dmapp.dtm_card_account_info;
Note: After canceling, you may still need to wait a few moments for the table state to return to 
NORMAL
. Summary of your workflow: Once the table is back in the 
NORMAL
state, you can resume the steps to modify your column: 1. Wait for current 
OPTIMIZE
to finish. 2. Change Distribution: 
ALTER TABLE ... DISTRIBUTED BY HASH(other_column);
(Wait for finish). 3. Modify Column: 
ALTER TABLE ... MODIFY COLUMN card_account_no varchar(255) KEY NULL;
References • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md • sql-reference/sql-statements/table_bucket_part_index/CANCEL_ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • sql-reference/sql-statements/table_bucket_part_index/CANCEL_ALTER_TABLE.md • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_CANCEL_REPAIR.md • administration/management/compaction.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/KILL_ANALYZE.md • sql-reference/sql-statements/backup_restore/CANCEL_RESTORE.md • sql-reference/sql-statements/backup_restore/CANCEL_BACKUP.md • sql-reference/sql-statements/materialized_view/CANCEL_REFRESH_MATERIALIZED_VIEW.md
l
@Rocky -- losrep.application_data_his definition CREATE TABLE 
application_data_his
( 
ID
bigint(20) NOT NULL COMMENT "", 
APP_CODE
varchar(50) NOT NULL COMMENT "", 
STEP_CODE
varchar(50) NOT NULL COMMENT "", 
API_URL
varchar(250) NULL COMMENT "", 
API_METHOD
varchar(50) NULL COMMENT "", 
CREATED_BY
varchar(50) NULL COMMENT "", 
CREATED_DATE
datetime NULL COMMENT "", 
APP_DATA
json NULL COMMENT "", INDEX idx_app_code (
APP_CODE
) USING BITMAP COMMENT '' ) ENGINE=OLAP PRIMARY KEY(
ID
, 
APP_CODE
) DISTRIBUTED BY HASH(
APP_CODE
) BUCKETS 4 ORDER BY(
APP_CODE
, 
ID
) PROPERTIES ( "compression" = "LZ4", "enable_persistent_index" = "true", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" ); update this table to able to store APP_CODE is null
r
In StarRocks, you cannot have a 
NULL
value in any column that is part of a Primary Key. Because your table is defined with 
PRIMARY KEY(ID, APP_CODE)
, the column 
APP_CODE
must remain 
NOT NULL
. To allow 
APP_CODE
to be 
NULL
, you must redefine your table so that 
APP_CODE
is no longer part of the Primary Key. Since StarRocks does not support modifying the Primary Key definition of an existing table, you will need to recreate the table. Recommended Solution: Table Migration Follow these steps to migrate your data to a new schema where 
APP_CODE
is nullable: 1. Create a new temporary table In this version, we move 
APP_CODE
out of the Primary Key and use only 
ID
as the unique identifier. We also change the distribution to 
ID
to prevent data skew from 
NULL
values.
Copy code
sql
CREATE TABLE `application_data_his_new` (
  `ID` bigint(20) NOT NULL COMMENT "",
  `APP_CODE` varchar(50) NULL COMMENT "", -- Changed to NULL
  `STEP_CODE` varchar(50) NOT NULL COMMENT "",
  `API_URL` varchar(250) NULL COMMENT "",
  `API_METHOD` varchar(50) NULL COMMENT "",
  `CREATED_BY` varchar(50) NULL COMMENT "",
  `CREATED_DATE` datetime NULL COMMENT "",
  `APP_DATA` json NULL COMMENT "",
  INDEX idx_app_code (`APP_CODE`) USING BITMAP COMMENT ''
) ENGINE=OLAP
PRIMARY KEY(`ID`) -- Removed APP_CODE from PK
DISTRIBUTED BY HASH(`ID`) BUCKETS 4 -- Distributed by ID instead
ORDER BY(`ID`, `APP_CODE`)
PROPERTIES (
"compression" = "LZ4",
"enable_persistent_index" = "true",
"fast_schema_evolution" = "true",
"replicated_storage" = "true",
"replication_num" = "2"
);
2. Migrate the data Copy the data from the old table to the new one.
Copy code
sql
INSERT INTO application_data_his_new SELECT * FROM application_data_his;
3. Swap the tables Rename the tables to put the new schema into production.
Copy code
sql
ALTER TABLE application_data_his RENAME application_data_his_old;
ALTER TABLE application_data_his_new RENAME application_data_his;
Why this is necessary: 1. Primary Key Integrity: In the Primary Key model, all key columns are used to determine uniqueness and perform upserts. Allowing 
NULL
in these columns would break the indexing logic. 2. Distribution Key: Your original table was 
DISTRIBUTED BY HASH(APP_CODE)
. If you allow many 
NULL
values in a distribution column, all those rows will go to the same bucket, causing data skew and significantly slowing down your cluster. It is better to distribute by a high-cardinality non-null column like 
ID
. Can I just use 
MODIFY COLUMN
? No. Even if you tried 
ALTER TABLE ... MODIFY COLUMN APP_CODE varchar(50) NULL
, StarRocks would return an error: 
Primary key column cannot be null
. Recreating the table is the standard way to change the Primary Key structure.
l
@Rocky is this possible and good proccess if i want to backup starrocks cluster daily, and set backup retention for 7 days, and delete over 7 day backup snapshot. how to do this, detail step by step
r
Yes, setting up a daily backup with a 7-day retention period is a recommended best practice for disaster recovery in StarRocks. It provides a point-in-time recovery (RPO) of 24 hours while keeping storage costs under control. However, StarRocks does not have a built-in "TTL" for backups. You must automate the triggering of the backup and the deletion of old snapshots using external tools (like a Python/Bash script and Crontab). Step-by-Step Implementation Guide 1. Prepare Your Storage (S3 or HDFS) StarRocks supports S3-compatible storage (MinIO, AWS S3, etc.) or HDFS. * For Cloud (S3/GCS): The easiest way to handle retention is to set a Lifecycle Policy on your bucket prefix to automatically delete objects older than 7 days. * For HDFS: You will need a script to run 
hdfs dfs -rm
on old directories. 2. Create a Repository in StarRocks This mapping only needs to be created once. It tells StarRocks where to send the data.
Copy code
sql
-- Example for S3
CREATE REPOSITORY my_backup_repo
WITH BROKER -- Broker is optional for S3 in newer versions
ON LOCATION "<s3a://my-bucket/starrocks_backups>"
PROPERTIES(
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY",
    "aws.s3.region" = "us-east-1"
);
3. Automate the Daily Backup Since StarRocks cannot schedule itself, use a cron job. Create a script (e.g., 
backup_sr.sh
) that generates a unique snapshot name based on the date.
Copy code
bash
#!/bin/bash
# Generate a name like snapshot_20231027
SNAPSHOT_NAME="snapshot_$(date +%Y%m%d)"
DB_NAME="your_db"

# Execute the backup command
mysql -h <FE_IP> -P 9030 -u root -e "BACKUP SNAPSHOT $DB_NAME.$SNAPSHOT_NAME TO my_backup_repo;"
4. Handle 7-Day Retention (Deletion) As noted in the documentation, StarRocks cannot delete snapshots from the remote storage via SQL. You must delete the files directly. Option A: AWS S3 Lifecycle (Best Practice) Configure your S3 bucket to expire objects with the prefix 
starrocks_backups/
after 7 days. This is the most reliable method as it requires no manual scripts. Option B: Manual Script (HDFS or S3) Add a cleanup step to your bash script.
Copy code
bash
# For HDFS: Delete directories older than 7 days
OLD_DATE=$(date -d "7 days ago" +%Y%m%d)
hdfs dfs -rm -r /repo_dir/backup/snapshot_$OLD_DATE
Important Considerations 1. Serialized Backups: StarRocks only allows one backup job to run at a time per database. Ensure your script checks the status of the previous backup before starting a new one using 
SHOW BACKUP FROM your_db;
. 2. Full Backups: Currently, StarRocks only supports 
FULL
backups. Every daily backup will be a complete copy of the data (though StarRocks optimizes the upload of underlying files). 3. Cloud-Native (Shared-Data): If you are using the Shared-Data (Cloud-native) architecture, the standard 
BACKUP
command is not supported, as data is already persisted in object storage. This process applies to the Shared-Nothing (standard OLAP) architecture. 4. Wait for Finish: If your database is large, the backup might take hours. Ensure your automation doesn't try to trigger a second backup if the first one is still running. References • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • sql-reference/sql-statements/backup_restore/BACKUP.md References • sql-reference/sql-statements/backup_restore/DROP_REPOSITORY.md • administration/management/Backup_and_restore.md
l
@Rocky SQL Error [5064] [42000]: Failed to create repository: failed to list remote path: s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info. msg: Fail to get file status: getFileStatus on s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info: software.amazon.awssdk.core.exception.SdkClientException: Received an UnknownHostException when attempting to interact with a service. See cause for the exact endpoint that is failing to resolve. If this is happening on an endpoint that previously worked, there may be a network connectivity issue or your DNS cache could be storing endpoints for too long.: software.amazon.awssdk.core.exception.SdkClientException: Received an UnknownHostException when attempting to interact with a service. See cause for the exact endpoint that is failing to resolve. If this is happening on an endpoint that previously worked, there may be a network connectivity issue or your DNS cache could be storing endpoints for too long.: uat-backup-starrock.uat-minio-office-api.shbfinance.com.vn Failed to create repository: failed to list remote path: s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info. msg: Fail to get file status: getFileStatus on s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info: software.amazon.awssdk.core.exception.SdkClientException: Received an UnknownHostException when attempting to interact with a service. See cause for the exact endpoint that is failing to resolve. If this is happening on an endpoint that previously worked, there may be a network connectivity issue or your DNS cache could be storing endpoints for too long.: software.amazon.awssdk.core.exception.SdkClientException: Received an UnknownHostException when attempting to interact with a service. See cause for the exact endpoint that is failing to resolve. If this is happening on an endpoint that previously worked, there may be a network connectivity issue or your DNS cache could be storing endpoints for too long.: uat-backup-starrock.uat-minio-office-api.shbfinance.com.vn
r
The error 
UnknownHostException
for 
<http://uat-backup-starrock.uat-minio-office-api.shbfinance.com.vn|uat-backup-starrock.uat-minio-office-api.shbfinance.com.vn>
indicates that the AWS SDK is trying to use Virtual-hosted style access. It prepends your bucket name (
uat-backup-starrock
) to your MinIO endpoint, which your DNS cannot resolve. For MinIO and other S3-compatible storage, you must explicitly enable Path-style access so the SDK uses the format 
endpoint/bucket
instead of 
bucket.endpoint
. How to Fix Update your 
CREATE REPOSITORY
statement to include 
aws.s3.enable_path_style_access
and ensure the endpoint is set correctly:
Copy code
sql
CREATE REPOSITORY your_repo_name
-- WITH BROKER -- Remove this if you are not using a separate Broker process
ON LOCATION "<s3a://uat-backup-starrock/backup_folder>" -- Use s3a:// for better compatibility
PROPERTIES(
    "aws.s3.endpoint" = "<http://uat-minio-office-api.shbfinance.com.vn>",
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY",
    "aws.s3.region" = "us-east-1", -- Region is often required as a placeholder
    "aws.s3.enable_path_style_access" = "true", -- CRITICAL: Fixes UnknownHostException
    "aws.s3.enable_ssl" = "false" -- Set to false if using http instead of https
);
Key Changes Explained: 1. 
"aws.s3.enable_path_style_access" = "true"
: This tells StarRocks to access the bucket via 
<http://endpoint/bucket>
rather than 
<http://bucket.endpoint>
. This is the standard requirement for MinIO. 2. 
"aws.s3.enable_ssl" = "false"
: Since your endpoint starts with 
http://
, you must explicitly disable SSL in the properties. 3. 
"aws.s3.region"
: Even for MinIO, the SDK often requires a region string (like 
us-east-1
) to initialize correctly. 4. 
s3a://
protocol: It is recommended to use the 
s3a://
prefix in the 
ON LOCATION
path when working with the built-in S3 client for repositories. Network Troubleshooting If the error persists after adding the properties: * Log in to one of your StarRocks FE and BE nodes. * Run: 
curl <http://uat-minio-office-api.shbfinance.com.vn/minio/health/live>
* If this 
curl
fails, your StarRocks nodes have a network or DNS issue reaching the MinIO server. References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • data_source/file_external_table.md (Section: S3-compatible storage) • data_source/catalog/unified_catalog.md (Section: S3-compatible storage system) References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • data_source/file_external_table.md • data_source/catalog/unified_catalog.md • deployment/shared_data/minio.md
l
@Rocky SQL Error [1064] [42000]: Getting syntax error at line 4, column 0. Detail message: Unexpected input 'ON', the most similar input is {'WITH'}. Getting syntax error at line 4, column 0. Detail message: Unexpected input 'ON', the most similar input is {'WITH'}.
r
The syntax error occurred because the 
WITH BROKER
clause is required by the StarRocks parser, even when you are using the built-in S3/MinIO client (broker-less). In my previous response, I mistakenly suggested that the line could be removed. To fix this, you must include 
WITH BROKER
as a literal part of the command. Corrected SQL Please use the following structure. Note the addition of 
WITH BROKER
and the use of 
s3a://
for the location:
Copy code
sql
CREATE REPOSITORY starrocks_minio_repo
WITH BROKER -- This keyword is mandatory for the syntax
ON LOCATION "<s3a://uat-backup-starrock/backup_folder>" -- Use s3a:// protocol
PROPERTIES(
    "aws.s3.endpoint" = "<http://uat-minio-office-api.shbfinance.com.vn>",
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY",
    "aws.s3.region" = "us-east-1", 
    "aws.s3.enable_path_style_access" = "true",
    "aws.s3.enable_ssl" = "false"
);
Why this is happening: 1. Syntax Requirement: Even if you do not have a separate "Broker" process installed, the SQL parser in StarRocks requires the 
WITH BROKER
keywords to distinguish the storage location clause. 2. 
s3a://
Protocol: For the built-in StarRocks repository client, it is highly recommended to use the 
s3a://
prefix. Using 
s3://
can sometimes lead to compatibility issues with specific S3-compatible providers like MinIO. 3. Path-Style Access: As mentioned before, ensuring 
"aws.s3.enable_path_style_access" = "true"
is critical for MinIO to prevent the 
UnknownHostException
you saw previously. Troubleshooting If you receive an error saying 
Broker '...' does not exist
, it means your StarRocks cluster expects a specific broker name. In that case, you can check your existing brokers using:
Copy code
sql
SHOW BROKER;
If that list is empty, then the literal 
WITH BROKER
(without a name) is the correct syntax for the internal client. References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • administration/management/Backup_and_restore.md • deployment/shared_data/minio.md • sql-reference/sql-statements/backup_restore/SHOW_BROKER.md References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • sql-reference/sql-statements/dictionary/CREATE_DICTIONARY.md • administration/management/Backup_and_restore.md
l
r
The "Failed to list path" error with the 
s3a://
prefix often occurs because 
s3a
is a Hadoop-specific protocol. While StarRocks uses it for AWS/GCS, for MinIO specifically, the internal repository client typically expects the standard 
s3://
 prefix. Additionally, the error often stems from the cluster BE nodes being unable to reach the MinIO endpoint or a lack of specific permissions (like 
ListBucket
) on the MinIO side. Recommended Fix Please try the following SQL, changing the prefix to 
s3://
and ensuring all properties are string-quoted:
Copy code
sql
CREATE REPOSITORY starrocks_minio_repo
WITH BROKER 
ON LOCATION "<s3://uat-backup-starrock/backup_folder>" -- Use s3:// for MinIO
PROPERTIES(
    "aws.s3.endpoint" = "<http://uat-minio-office-api.shbfinance.com.vn>",
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY",
    "aws.s3.region" = "us-east-1", 
    "aws.s3.enable_path_style_access" = "true",
    "aws.s3.enable_ssl" = "false"
);
Checklist for Resolution: 1. Protocol: Use 
s3://
for the location when connecting to MinIO. 2. Permissions: Ensure your MinIO user has 
s3:ListBucket
, 
s3:GetObject
, and 
s3:PutObject
permissions for the bucket 
uat-backup-starrock
. 3. Network Visibility: * StarRocks BE nodes perform the actual data transfer. * Log into a BE node and run: 
curl -I <http://uat-minio-office-api.shbfinance.com.vn>
. * If the BE cannot reach this URL, the 
CREATE REPOSITORY
command will fail because it cannot validate the path. 4. Bucket Existence: Ensure the bucket 
uat-backup-starrock
exists in MinIO. StarRocks will not create the bucket for you; it only creates the sub-folders. 5. Clean Up: If a previous attempt partially created metadata, try changing the 
ON LOCATION
folder name slightly (e.g., 
backup_folder_v2
) to see if it bypasses a corrupted state. Why is StarRocks listing 
__repo_info
? When you create a repository, StarRocks automatically checks for a file named 
*repo_info
inside a hidden folder (
*starrocks_repository_<repo_name>
). It does this to see if you are connecting to an existing repository or creating a new one. The "Failed to list" error means the initial "check if folder exists" request to MinIO failed. References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • administration/management/Backup_and_restore.md • data_source/file_external_table.md (S3-compatible storage section) • deployment/shared_data/minio.md
l
r
The "Failed to list path" error after applying the previous fixes usually indicates that the StarRocks Backend (BE) nodes are encountering a low-level network error, a credential issue, or a configuration mismatch when attempting the actual S3 
ListObjects
call. Since the original 
UnknownHostException
is gone, the "Path-Style" configuration is likely working, but the connection is still failing. 1. Update the SQL with missing properties For MinIO, StarRocks often requires a dummy region and explicitly disabling the default AWS SDK behavior to ensure it communicates correctly with non-AWS endpoints. Please try this exact version:
Copy code
sql
CREATE REPOSITORY starrocks_minio_repo
WITH BROKER 
ON LOCATION "<s3://uat-backup-starrock/backup_folder>"
PROPERTIES(
    "aws.s3.endpoint" = "<http://uat-minio-office-api.shbfinance.com.vn>",
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY",
    "aws.s3.region" = "us-east-1", -- Dummy region often required by SDK
    "aws.s3.enable_path_style_access" = "true",
    "aws.s3.enable_ssl" = "false",
    "aws.s3.use_aws_sdk_default_behavior" = "false" -- Prevents AWS-specific logic
);
2. Mandatory Network Verification from BE Nodes The 
CREATE REPOSITORY
command is initiated by the FE, but the validation (listing the path) is performed by the BE nodes. If your StarRocks cluster is in a private network (e.g., Kubernetes or a VPC), the BE nodes might not have a route to your MinIO URL. Run this command on every BE node in your cluster:
Copy code
bash
# Check if the BE can reach the MinIO API
curl -I <http://uat-minio-office-api.shbfinance.com.vn/minio/health/live>
* If this returns a 
404
or 
200
, the network is fine. * If it returns 
Could not resolve host
or 
Connection timed out
, you have a DNS or Firewall issue on your BE servers. 3. Check for Permission Issues Ensure the MinIO user associated with your 
access_key
has the following permissions on the bucket `uat-backup-starrock`: * 
s3:ListBucket
* 
s3:GetObject
* 
s3:PutObject
(required for creating the repository metadata) 4. Investigate the Logs If the error persists, the most detailed information will be in the FE and BE logs. 1. Check `fe/log/fe.warn.log`: Look for the stack trace associated with 
Failed to create repository
. 2. Check 
be/log/be.INFO
or `be/log/be.warn.log`: Look for 
S3
or 
aws-sdk
related errors. Often the log will contain a more specific error code like 
403 Forbidden
, 
400 Bad Request
, or 
Connection Refused
. Summary of Protocols * Use 
s3://
: This uses the native AWS SDK and is recommended for MinIO when using 
aws.s3.*
properties. * Avoid 
s3a://
: While supported, it uses the Hadoop S3A client which requires different property keys (e.g., 
fs.s3a.endpoint
). Which StarRocks version are you using? (Some older versions have specific bugs with S3-compatible repositories). References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • administration/management/Backup_and_restore.md • data_source/file_external_table.md • deployment/shared_data/minio.md References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • data_source/file_external_table.md • data_source/catalog/unified_catalog.md
l
@Rocky SQL Error [5064] [42000]: Failed to create repository: failed to list remote path: s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info. msg: Fail to get file status: getFileStatus on s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info: software.amazon.awssdk.core.exception.SdkClientException: Received an UnknownHostException when attempting to interact with a service. See cause for the exact endpoint that is failing to resolve. If this is happening on an endpoint that previously worked, there may be a network connectivity issue or your DNS cache could be storing endpoints for too long.: software.amazon.awssdk.core.exception.SdkClientException: Received an UnknownHostException when attempting to interact with a service. See cause for the exact endpoint that is failing to resolve. If this is happening on an endpoint that previously worked, there may be a network connectivity issue or your DNS cache could be storing endpoints for too long.: uat-backup-starrock.uat-minio-office-api.shbfinance.com.vn Failed to create repository: failed to list remote path: s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info. msg: Fail to get file status: getFileStatus on s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info: software.amazon.awssdk.core.exception.SdkClientException: Received an UnknownHostException when attempting to interact with a service. See cause for the exact endpoint that is failing to resolve. If this is happening on an endpoint that previously worked, there may be a network connectivity issue or your DNS cache could be storing endpoints for too long.: software.amazon.awssdk.core.exception.SdkClientException: Received an UnknownHostException when attempting to interact with a service. See cause for the exact endpoint that is failing to resolve. If this is happening on an endpoint that previously worked, there may be a network connectivity issue or your DNS cache could be storing endpoints for too long.: uat-backup-starrock.uat-minio-office-api.shbfinance.com.vn
r
The 
UnknownHostException
for the address 
<http://uat-backup-starrock.uat-minio-office-api.shbfinance.com.vn|uat-backup-starrock.uat-minio-office-api.shbfinance.com.vn>
confirms that StarRocks is attempting Virtual-Host Style addressing (prepending the bucket name to the host). MinIO typically requires Path-Style addressing. The error persists because the 
aws.s3.enable_path_style_access
property might not be correctly recognized by the 
CREATE REPOSITORY
internal client in your specific StarRocks version, or the client is defaulting to the Hadoop S3A protocol. Recommended Fix: Use the S3A Protocol StarRocks documentation notes that for repositories, the S3A protocol is the most compatible method. When using 
s3a://
, you must use properties prefixed with 
fs.s3a.
. Try this SQL:
Copy code
sql
CREATE REPOSITORY starrocks_minio_repo
WITH BROKER 
ON LOCATION "<s3a://uat-backup-starrock/backup_folder>" -- Use s3a:// prefix
PROPERTIES(
    "fs.s3a.endpoint" = "<http://uat-minio-office-api.shbfinance.com.vn>",
    "fs.s3a.access.key" = "YOUR_ACCESS_KEY",
    "fs.s3a.secret.key" = "YOUR_SECRET_KEY",
    "fs.s3a.path.style.access" = "true", -- S3A specific path-style property
    "fs.s3a.connection.ssl.enabled" = "false"
);
Why this should work: 1. Protocol Switch: By using 
s3a://
, you trigger the Hadoop S3A connector, which is the standard backend for the StarRocks 
CREATE REPOSITORY
command. 2. Explicit Path Style: 
fs.s3a.path.style.access
= 
true
explicitly tells the connector to use 
host/bucket
instead of 
bucket.host
. 3. Property Mapping: The 
fs.s3a.*
keys are the native configuration for the S3A client. If you still want to use `s3://`: If your StarRocks version is 3.x and you prefer the native SDK, ensure there are no typos in the property name. However, some versions of StarRocks only support the native 
aws.s3.enable_path_style_access
for Storage Volumes or External Catalogs, and not yet for the older 
CREATE REPOSITORY
command. Troubleshooting Step Check your StarRocks version by running:
Copy code
sql
SELECT VERSION();
If you are on a version earlier than 3.0, the S3A protocol (the first block of code above) is the only supported method for repositories. References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • administration/management/Backup_and_restore.md • data_source/file_external_table.md (See S3-compatible storage/MinIO section) • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • sql-reference/sql-statements/Resource/CREATE_RESOURCE.md • administration/management/Backup_and_restore.md
l
@Rocky rewrite this to starrocks: INSERT INTO LOSREP.APPLICATION_STATUS (ID, APP_UID, APP_NUMBER, PROCESS_ID, PROCESS_NAME, CREATED_DATE, LAST_UPDATED_DATE, FINISHED_DATE, CREATED_BY, LAST_UPDATED_BY, STEP_CODE, CURRENT_DECISION, CURRENT_DECISION_LBL, CURRENT_REASON, CURRENT_REASON_LBL, CURRENT_REASON_DETAIL, CURRENT_REASON_DETAIL_LBL, CURRENT_COMMENT, APP_CODE, PARTNER_CALLBACK_STATUS_CODE, PARTNER_CALLBACK_STATUS_DES) VALUES(7485666, NULL, NULL, NULL, NULL, TIMESTAMP '2026-05-27 125735.000000', TIMESTAMP '2026-05-27 131034.000000', TIMESTAMP '2026-05-27 131034.000000', NULL, 'systemUser', 'DONE', 'DISBURSED', 'Đồng ý cho vay.', NULL, NULL, NULL, NULL, NULL, 'DG2259E2605278862168', NULL, NULL);
r
In StarRocks, the syntax for 
INSERT
is almost identical to standard SQL. The main adjustment is that StarRocks typically uses standard string literals for date/time values rather than the 
TIMESTAMP
keyword prefix. Here is the rewritten query:
Copy code
sql
INSERT INTO LOSREP.APPLICATION_STATUS
(
    ID, APP_UID, APP_NUMBER, PROCESS_ID, PROCESS_NAME, 
    CREATED_DATE, LAST_UPDATED_DATE, FINISHED_DATE, 
    CREATED_BY, LAST_UPDATED_BY, STEP_CODE, 
    CURRENT_DECISION, CURRENT_DECISION_LBL, 
    CURRENT_REASON, CURRENT_REASON_LBL, 
    CURRENT_REASON_DETAIL, CURRENT_REASON_DETAIL_LBL, 
    CURRENT_COMMENT, APP_CODE, 
    PARTNER_CALLBACK_STATUS_CODE, PARTNER_CALLBACK_STATUS_DES
)
VALUES
(
    7485666, NULL, NULL, NULL, NULL, 
    '2026-05-27 12:57:35', '2026-05-27 13:10:34', '2026-05-27 13:10:34', 
    NULL, 'systemUser', 'DONE', 
    'DISBURSED', 'Đồng ý cho vay.', 
    NULL, NULL, 
    NULL, NULL, 
    NULL, 'DG2259E2605278862168', 
    NULL, NULL
);
Key Differences & Tips: 1. Timestamp Literals: StarRocks automatically parses strings in the format 
'YYYY-MM-DD HH:MM:SS'
into its 
DATETIME
type. You do not need the 
TIMESTAMP
keyword. 2. Microsecond Precision: If your table columns are defined as 
DATETIME(6)
, you can include the fractional seconds (e.g., 
'2026-05-27 12:57:35.000000'
). If they are standard 
DATETIME
, the fractional part will be ignored. 3. Performance Note: In StarRocks, 
INSERT INTO ... VALUES
is suitable for manual testing or small batches. If you are planning to load large volumes of data (thousands of rows per second), it is recommended to use Stream Load or Routine Load instead of individual 
INSERT
statements to maximize performance.
l
@Rocky update dmapp.af_extract_mapping a set dest_pre_sql = null WHERE a.PROJECT_NAME ='DMAPP_DATA_02' and a.SRC_SCHEMA ='modelling'; correct
r
In StarRocks, the 
UPDATE
statement has a few specific rules, most notably that you cannot use a table name or alias prefix in the 
SET
clause. Here is the corrected SQL:
Copy code
sql
UPDATE dmapp.af_extract_mapping
SET dest_pre_sql = NULL 
WHERE PROJECT_NAME = 'DMAPP_DATA_02' 
  AND SRC_SCHEMA = 'modelling';
Key Requirements for StarRocks: 1. No Column Prefixes: The 
SET
clause must use the raw column name (e.g., 
SET dest_pre_sql = ...
). Writing 
SET a.dest_pre_sql = ...
will result in a syntax error. 2. Primary Key Tables Only: The 
UPDATE
statement is only supported on tables using the Primary Key storage engine. If your table is a Duplicate Key or Aggregate Key table, you cannot use 
UPDATE
. 3. WHERE Clause is Mandatory: StarRocks requires a 
WHERE
clause to prevent accidental full-table updates. If you truly want to update everything, you must use 
WHERE true
. 4. Database/Table Format: Ensure 
dmapp
is the database name and 
af_extract_mapping
is the table name. If you need an alias (for complex joins): If you are joining with another table (supported in StarRocks 3.0+), the syntax uses a 
FROM
clause:
Copy code
sql
UPDATE dmapp.af_extract_mapping
SET dest_pre_sql = NULL
FROM some_other_table b
WHERE dmapp.af_extract_mapping.id = b.id  -- Join condition
  AND PROJECT_NAME = 'DMAPP_DATA_02';
References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • table_design/table_types/primary_key_table.md References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • faq/Others.md • loading/Load_to_Primary_Key_tables.md
Open in Slack
PreviousNext
Powered by