Dropping old Cassandra keyspaces

I have Cassandra instance that is use for running end-to-end tests by GitLab runner after each commit. Each run creates temporary keyspace. Usually test performs cleanup but sometimes job can fail. As a result, dangling Cassandra keyspace appears. To solve this problem, I wrote a script that deletes keyspaces that are 1day+ old. Cron runs this script daily. While writing this script I encountered several pitfalls.

How to get age of keyspace?
I did not find column that reflects time of KS creation. But there is system_schema.keyspaces table with all keyspaces.
I decided to use writetime function.

SELECT writetime(durable_writes) as time, keyspace_name FROM system_schema.keyspaces;

This works ok besides we need to trim header and footer. This can be done with command

query_result=$(cqlsh -e "SELECT writetime(durable_writes) as time, keyspace_name FROM system_schema.keyspaces;" --no-color 2>&1)

Full script

#!/bin/bash

# Default values
DRY_RUN=false
KEYSPACE_PREFIX="cqgm_"
EXPIRATION_TIME=$((2*24*60*60))  # 2 days in seconds

# Process command-line arguments
while [[ $# -gt 0 ]]; do
    case "$1" in
        --dry-run)
            DRY_RUN=true
            shift
            ;;
        --prefix)
            if [[ -z "$2" || "$2" == --* ]]; then
                echo "Error: --prefix requires a value"
                exit 1
            fi
            KEYSPACE_PREFIX="$2"
            shift 2
            ;;
        --expiration-time)
            if [[ -z "$2" || "$2" == --* ]]; then
                echo "Error: --expiration-time requires a value"
                exit 1
            fi
            if ! [[ "$2" =~ ^[0-9]+$ ]]; then
                echo "Error: --expiration-time must be a positive integer (seconds)"
                exit 1
            fi
            EXPIRATION_TIME="$2"
            shift 2
            ;;
        -h|--help)
            echo "Usage: $0 [OPTIONS]"
            echo "Options:"
            echo "  --dry-run              Show what would be done without actually dropping keyspaces"
            echo "  --prefix PREFIX        Specify keyspace prefix to match (default: cqgm_)"
            echo "  --expiration-time SEC  Specify expiration time in seconds (default: 172800 = 2 days)"
            echo "  -h, --help             Show this help message"
            exit 0
            ;;
        *)
            echo "Unknown option: $1"
            echo "Use -h or --help for usage information"
            exit 1
            ;;
    esac
done

# Get current timestamp in seconds
current_timestamp=$(date +%s)

# Calculate cutoff timestamp using the expiration time
cutoff_timestamp=$((current_timestamp - EXPIRATION_TIME))

# Calculate human-readable time period for display
days=$((EXPIRATION_TIME / 86400))
hours=$(( (EXPIRATION_TIME % 86400) / 3600 ))
minutes=$(( (EXPIRATION_TIME % 3600) / 60 ))
seconds=$((EXPIRATION_TIME % 60))
time_period=""
[[ $days -gt 0 ]] && time_period+="$days days "
[[ $hours -gt 0 ]] && time_period+="$hours hours "
[[ $minutes -gt 0 ]] && time_period+="$minutes minutes "
[[ $seconds -gt 0 ]] && time_period+="$seconds seconds"
time_period=${time_period:-"0 seconds"}

if [ "$DRY_RUN" = true ]; then
    echo "Running in DRY-RUN mode - No keyspaces will be dropped"
else
    echo "Running in NORMAL mode - Keyspaces will be dropped"
fi
echo "Settings:"
echo "  Keyspace prefix: '$KEYSPACE_PREFIX'"
echo "  Expiration time: $EXPIRATION_TIME seconds ($time_period)"

echo "Checking for keyspaces with prefix '$KEYSPACE_PREFIX' that are older than $time_period..."

query_result=$(cqlsh -e "SELECT writetime(durable_writes) as time, keyspace_name FROM system_schema.keyspaces;" --no-color 2>&1)

if [[ $? -ne 0 ]]; then
    echo "Error executing CQL query: $query_result"
    exit 1
fi

# Process the query results
echo "$query_result" | tail -n +4 | head -n -2 | while read -r line; do
    [ -z "$line" ] && continue

    # Parse the line to extract timestamp and keyspace name
    time_str=$(echo "$line" | awk -F'|' '{print $1}' | tr -d ' ')
    keyspace_str=$(echo "$line" | awk -F'|' '{print $2}' | tr -d ' ')

    # Skip if either field is empty
    [ -z "$time_str" ] || [ -z "$keyspace_str" ] && continue

    # Check if keyspace starts with the specified prefix
    if [[ "$keyspace_str" == ${KEYSPACE_PREFIX}* ]]; then
        # Convert timestamp from microseconds to seconds
        timestamp_seconds=$((time_str / 1000000))

        # Format the date for display
        created_date=$(date -d @$timestamp_seconds "+%Y-%m-%d %H:%M:%S")

        # Compare with cutoff timestamp
        if [ "$timestamp_seconds" -lt "$cutoff_timestamp" ]; then
            if [ "$DRY_RUN" = true ]; then
                echo "[DRY-RUN] Would drop keyspace $keyspace_str (created $created_date)"
            else
                echo "Dropping keyspace $keyspace_str (created $created_date)"
                drop_result=$(cqlsh -e "DROP KEYSPACE $keyspace_str;" 2>&1)
                if [[ $? -ne 0 ]]; then
                    echo "Error dropping keyspace $keyspace_str: $drop_result"
                fi
            fi
        else
            echo "Keyspace $keyspace_str is newer than $time_period (created $created_date)"
        fi
    fi
done

if [ "$DRY_RUN" = true ]; then
    echo "Dry run completed. No keyspaces were dropped."
else
    echo "Done."
fi