http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Is it possible to switch default st...
# questions-and-troubleshooting
b
@Rocky Is it possible to switch default storage volumes? I have no data created but tables are still being referenced probably starrocks metadata stuff.
r
Yes, it is possible to switch the default storage volume in StarRocks (starting from version 3.1). You can do this even if you have already created tables, as the change primarily affects which storage volume is used for newly created tables and system metadata moving forward. How to switch the default storage volume You can use the following SQL command:
Copy code
sql
SET <storage_volume_name> AS DEFAULT STORAGE VOLUME;
Key points to consider: 1. Effect on existing tables: Switching the default storage volume does not automatically migrate existing data or existing tables to the new volume. Existing tables will continue to reference the storage volume they were originally created on. 2. System Metadata: In shared-data mode, StarRocks stores system statistics and certain metadata in the default storage volume. When you switch the default, new statistics and system info will be directed to the new default volume. 3. Privileges: You must have the 
USAGE
privilege on the specific storage volume you are trying to set as default. 4. Restrictions: The current default storage volume cannot be dropped or disabled. If you want to delete an old volume that is currently the default, you must first set a different volume as the default. Example If you have created a new S3 volume named 
new_s3_volume
and want to make it the default:
Copy code
sql
-- 1. Create the volume if you haven't already
CREATE STORAGE VOLUME new_s3_volume
TYPE = S3
LOCATIONS = ("<s3://your-bucket/path/>")
PROPERTIES ("enabled" = "true");

-- 2. Set it as the default
SET new_s3_volume AS DEFAULT STORAGE VOLUME;

-- 3. Verify the change
SHOW STORAGE VOLUMES;
If you need to move "no data" tables that are still referencing the old volume, you would typically need to drop and recreate them (since you mentioned there is no data yet) or specify the storage volume explicitly during table creation if you don't want to rely on the default. References • sql-reference/sql-statements/cluster-management/storage_volume/SET_DEFAULT_STORAGE_VOLUME.md • quick_start/shared-data.md • faq/operation_maintenance_faq.md • deployment/shared_data/azure.md • administration/management/BE_configuration.md • deployment/shared_data/gcs.md • deployment/shared_data/s3.md
b
@Rocky I cant drop the old volume because tables are on it, but I would like to. I created no data on my side yet
r
If you are trying to drop a storage volume in a StarRocks shared-data cluster but are being blocked because "tables are on it," follow these steps to clear the references. Even if you haven't added data yet, the metadata of existing databases or tables (including those in the recycle bin) will prevent the volume from being dropped. 1. Check if it is the Default Volume A storage volume designated as the default cannot be dropped. Run the following command to check the 
IsDefault
status:
Copy code
sql
DESC STORAGE VOLUME your_volume_name;
If 
IsDefault
is 
true
, you must first set a different volume as the default before dropping this one:
Copy code
sql
SET your_other_volume_name DEFAULT STORAGE VOLUME;
2. Identify and Remove Referencing Tables/Databases Storage volumes are referenced by databases or tables via the 
storage_volume
property. Since you have no data yet, the easiest path is to drop these entities. * To find referencing Databases: Check the creation properties of your databases:
Copy code
sql
    SHOW CREATE DATABASE your_db_name;
Look for 
"storage_volume" = "your_volume_name"
. * To find referencing Tables: Similarly, check table properties:
Copy code
sql
    SHOW CREATE TABLE your_table_name;
3. Use 
FORCE
to Clear the Recycle Bin When you run a standard 
DROP TABLE
or 
DROP DATABASE
, StarRocks moves the metadata to a recycle bin (controlled by the FE config 
catalog_trash_expire_second
). As long as the metadata is in the trash, the storage volume remains "in use." To bypass the trash and remove the references immediately, use the 
FORCE
 keyword:
Copy code
sql
DROP TABLE your_table_name FORCE;
DROP DATABASE your_db_name FORCE;
4. Drop the Volume Once all referencing databases and tables (including those in the trash) are removed, you can drop the volume:
Copy code
sql
DROP STORAGE VOLUME your_volume_name;
Rocky's Tip: If you've already dropped the tables without the 
FORCE
keyword and don't want to wait for the recycle bin to expire, you can check the status of your volumes using 
SHOW STORAGE VOLUMES
. If the volume still won't drop after using 
FORCE
, ensure no other databases in the cluster were created using that volume as their base storage. References • sql-reference/sql-statements/cluster-management/storage_volume/SHOW_STORAGE_VOLUMES.md • sql-reference/sql-statements/cluster-management/storage_volume/DESC_STORAGE_VOLUME.md • deployment/shared_data/feature-support-shared-data.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • sql-reference/information_schema/tables.md • knowledge/admin_and_operation/shared_data_compaction.md
b
@Rocky How do I find tables by table id to change who owns them
r
To find a table by its ID and manage its privileges (or ownership) in StarRocks, follow these steps: 1. Find Table Name by Table ID The easiest way to find a table name and its database using a table ID is to query the 
information_schema.tables_config
table:
Copy code
sql
SELECT TABLE_SCHEMA, TABLE_NAME 
FROM information_schema.tables_config 
WHERE TABLE_ID = <your_table_id>;
Alternatively, you can use the 
SHOW PROC
diagnostic command: 1. Run 
SHOW PROC "/dbs";
to list all databases and their IDs. 2. Find the database ID that likely contains your table. 3. Run 
SHOW PROC "/dbs/<db_id>";
to list all tables in that database, which will show both the 
TableId
and 
TableName
. 2. Change Table Ownership/Privileges In StarRocks (especially version 3.x), "ownership" is tied to the Role-Based Access Control (RBAC) system. The user or role that creates a table is considered its owner and has full privileges by default. To "change the owner" (transfer full control) to a different user or role, you grant them 
ALL PRIVILEGES
and, if necessary, revoke privileges from the old user. Grant full control to a new user:
Copy code
sql
GRANT ALL PRIVILEGES ON TABLE <db_name>.<table_name> TO USER <new_user_identity>;
-- Example: GRANT ALL PRIVILEGES ON TABLE my_db.my_table TO USER 'jack'@'%';
Grant full control to a role:
Copy code
sql
GRANT ALL PRIVILEGES ON TABLE <db_name>.<table_name> TO ROLE <role_name>;
Revoke control from the old user (optional):
Copy code
sql
REVOKE ALL PRIVILEGES ON TABLE <db_name>.<table_name> FROM USER <old_user_identity>;
Note: Only users with the 
user_admin
role or the 
GRANT
privilege on the target object can perform these operations. If you are using an older version (v2.x), privileges are managed similarly but the system-defined roles may differ. References • administration/user_privs/authorization/privilege_item.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/user_privs.md • administration/user_privs/authorization/privilege_faq.md • sql-reference/sql-statements/account-management/GRANT.md
b
@Rocky The table ids do not exist how do I cleanup the trash/deleted tables now
r
In StarRocks, when you drop a table, the data and metadata are not immediately deleted. They are moved to a "trash" or "recycle bin" state for a period (default 24 hours) to allow for recovery using the 
RECOVER
command. If you are seeing table/tablet files on disk or in object storage but the IDs no longer exist in your FE metadata (e.g., they aren't in 
information_schema.recyclebin_catalogs
), here is how you can clean them up: 1. Shared-nothing (Data on BE Local Disks) If you are using the standard shared-nothing architecture, the deleted data is moved to a 
trash
directory within each BE's storage root path. * Automatic Cleanup: You can shorten the retention period by adjusting these configurations (they default to 86400 seconds / 1 day): * FE (
fe.conf
): 
catalog_trash_expire_second
(Controls how long metadata stays in the FE recycle bin). * BE (
be.conf
): 
trash_file_expire_time_sec
(Controls how long data files stay in the BE trash directory). * Manual Cleanup: If you need to free up space immediately and the FE no longer tracks these IDs, you can safely navigate to the 
storage/trash
directory on each BE node and delete the contents manually:
Copy code
bash
    # Go to your BE storage path
    cd /path/to/be/storage/trash/
    rm -rf *
2. Shared-data (Cloud-native / Object Storage) If you are using the Shared-data architecture (data in S3/OSS/HDFS), dropping a table moves it to the FE recycle bin. If the IDs are already gone from the FE, but files remain in object storage, this is considered "garbage data." * Drop Table Force: In the future, to bypass the trash entirely, use:
Copy code
sql
    DROP TABLE <table_name> FORCE;
* Garbage Collection Tool: For shared-data clusters, StarRocks provides a tool called 
meta_tool.sh
to clean up unreferenced data in object storage. This tool compares the metadata in your tablets with the files in object storage to find "orphaned" files. * Operation: 
lake_datafile_gc
* You typically run this via the BE binary:
Copy code
bash
        ./bin/meta_tool --operation=lake_datafile_gc --root_path="<s3://your-bucket/path>" --conf_file=./conf/be.conf --do_delete=true
_(Note: It is recommended to run with 
--do_delete=false
first to see what will be removed.)_ 3. Verification You can check if the FE still has any record of these tables by querying the information schema:
Copy code
sql
SELECT * FROM information_schema.recyclebin_catalogs;
If the table is listed here, you can wait for the expiration or (if supported in your version) let the system clean it up naturally after the 
catalog_trash_expire_second
period. Summary for immediate relief: If you are on-prem/local disks and the IDs are already missing from the metadata, deleting the contents of the 
trash
folder on the BEs is the most direct way to reclaim space. References • faq/operation_maintenance_faq.md • administration/Data_recovery.md • faq/shared_data_faq.md • sql-reference/information_schema/recyclebin_catalogs.md • faq/Others.md • knowledge/admin_and_operation/shared_data_garbage_cleanup.md • using_starrocks/Cost_based_optimizer.md
b
I see tables is there a way to clean them up or force the catalog trash expire seconds to 0 @Rocky?
r
Yes, you can clean up tables immediately or adjust the trash expiration settings in StarRocks. Here are the two primary ways to handle this: 1. Use the 
FORCE
keyword (Recommended) If you want to delete a table and skip the recycle bin (trash) entirely, you can use the 
FORCE
keyword in your 
DROP
statement. This will remove the metadata immediately and you won't be able to recover it using the 
RECOVER
command.
Copy code
sql
DROP TABLE my_table FORCE;
-- This also works for databases and partitions:
DROP DATABASE my_db FORCE;
ALTER TABLE my_table DROP PARTITION p1 FORCE;
2. Configure Trash Expiration Settings If you want to change the global behavior for all drop operations (without the 
FORCE
keyword), you can modify the FE (Frontend) and BE (Backend) configurations. FE Configuration: Metadata Trash The parameter 
catalog_trash_expire_second
(default: 86400, or 24 hours) determines how long the metadata for dropped items is kept in the FE recycle bin. You can set this to 0 or a very low value.
Copy code
sql
-- Set the metadata trash expiration to 0
ADMIN SET FRONTEND CONFIG ("catalog_trash_expire_second" = "0");
_Note: There is a minimum latency parameter 
catalog_recycle_bin_erase_min_latency_ms
(default: 600,000ms / 10 mins) that might still cause a slight delay before the erase operation is triggered._ BE Configuration: Physical Data Trash Even after metadata is removed, the physical files on the BE (Backend) remain in a 
trash
directory for a period defined by 
trash_file_expire_time_sec
(default: 86400). To speed up physical disk space release:
Copy code
bash
# This is a BE config, typically set in be.conf or via curl
curl -X POST http://<be_ip>:<be_http_port>/api/update_config?trash_file_expire_time_sec=0
Summary of Actions * For immediate cleanup of specific tables: Use 
DROP TABLE <name> FORCE
. * To reduce the global "waiting" time for all dropped tables: Set 
catalog_trash_expire_second
to 
0
on the FE. * To clear disk space faster: Set 
trash_file_expire_time_sec
to a lower value on the BEs. Warning: Setting these values to 0 means you will no longer be able to use the 
RECOVER
command to restore accidentally dropped tables. Always use these settings with caution. References • sql-reference/sql-statements/table_bucket_part_index/DROP_TABLE.md • faq/Others.md • sql-reference/sql-statements/Database/DROP_DATABASE.md • data_source/catalog/hive_catalog.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • data_source/catalog/iceberg/DDL.md • sql-reference/sql-statements/loading_unloading/ETL/DROP_TASK.md
Open in Slack
PreviousNext
Powered by