Troubleshooting Tip: Resolving Upgrade Failure in ClickHouse Summary Table

Important: Before proceeding, take a snapshot of the VM for recovery purposes.

The following message typically occurs due to a misconfiguration in the storage_policy.xml file or incorrect settings in the summary.sql table:

Code: 36. DB::Exception: Received from localhost:9000. DB::Exception: New storage policy `default` shall contain volumes of old one. (BAD_ARGUMENTS)

Follow the steps below to resolve the issue:

1. Identify the Cause. Check the storage_policy.xml file for existing storage policies such as fsiem_storage_policy, moving_from_hot_to_warm, or triple_tier.
   Run:

cat /etc/clickhouse-server/config.d/storage_policy.xml 

2. Compare the Table Configurations.
   Locate the summary.sql file within the ClickHouse directory:

cd /data-clickhouse-hot-1/clickhouse/store/ 

find . -name summary.sql 

Observe the output of the 'find' command and navigate to the directory to inspect both summary.sql and events_replicated.sql:

cd <directory_path>/ 

cat summary.sql 

cat events_replicated.sql 

Expected Configuration:

For FortiSIEM v7.x+, the table ENGINE should be ReplicatedMergeTree.

The storage_policy should be one of fsiem_storage_policy, moving_from_hot_to_warm, or triple_tier.

3. Modify the Table.
   If the storage_policy is missing or the ENGINE is configured as MergeTree, run the upgrade script:

cd /opt/phoenix/deployment/clickhouse_upgrade/ 

./clickhouse_db_up_6.6.0_to_6.7.0.sh 

Note: This process will involve downtime ranging from 15 minutes to 1 hour, depending on storage size.

Example Output:

[root@Supervisor clickhouse_upgrade]# ./clickhouse_db_up_6.6.0_to_6.7.0.sh

Creating fsiem.summary_replicated with storage policy moving_from_hot_to_warm

fsiem.summary table exists..
Attaching summary table partitions to replicated merge tree
Removing merge tree summary table.
Renaming fsiem.summary_replicated to fsiem.summary
Creating distributed summary table.
[root@Supervisor clickhouse_upgrade]#

4. Verify the Changes:
   After running the script, verify the updates with the following commands:
   
   

cd /data-clickhouse-hot-1/clickhouse/store/<directory_path>/summary.sql 

cd /data-clickhouse-hot-1/clickhouse/store/<directory_path>/events_replicated.sql 

clickhouse-client -q "SHOW CREATE TABLE fsiem.summary FORMAT Vertical" 

clickhouse-client -q "SHOW CREATE TABLE fsiem.events_replicated FORMAT Vertical" 

Ensure the ENGINE is set to ReplicatedMergeTree and the storage_policy matches the configuration in /etc/clickhouse-server/config.d/storage_policy.xml.