troubleshooting.md

Troubleshooting & FAQ

UInt64 from System Columns

Problem: Reading numeric system columns (e.g., from system.tables, system.columns) throws an InvalidCastException.

Cause: ClickHouse.Driver returns UInt64 for system catalog numeric columns, even when the logical value fits in Int32.

Solution:

// Wrong -- throws InvalidCastException
var granularity = reader.GetInt32(n);

// Correct
var granularity = Convert.ToInt32(reader.GetValue(n));

This applies throughout the Introspection package when reading system table metadata.

DateTime vs DateTime64 for TTL

Problem: TTL rules fail or produce unexpected behavior with DateTime64 columns.

Cause: ClickHouse requires TTL expressions to use DateTime (not DateTime64). The now() function returns DateTime, while now64(3) returns DateTime64 -- and mixing these in TTL comparisons can cause issues.

Solution: Use DateTime for columns referenced in TTL expressions:

.Column(e => e.CreatedAt)
    .AsType(new ChDateTime())       // Not DateTime64
    .Ttl("created_at + INTERVAL 90 DAY")
    .Table

The migration lock table follows this pattern: it uses DateTime64(3) for the heartbeat column but now() (not now64(3)) in the TTL expression since TTL evaluation requires DateTime.

system.columns Differences Across Versions

Problem: Schema introspection fails on certain ClickHouse versions.

Cause: The system.columns table schema varies between ClickHouse versions. Notably, CH 24.8 does not include a ttl_expression column that exists in newer versions.

Solution: The introspection layer handles this by querying available columns dynamically. If you're extending introspection, don't assume all system columns exist -- check availability first.

Integration tests target clickhouse/clickhouse-server:24.8 to ensure compatibility.

Floating Versions Not Allowed

Problem: Build fails with a version error after adding a NuGet package.

Cause: The project uses Central Package Management (CPM) via Directory.Packages.props. CPM does not support floating version ranges like 7.*.

Solution:

1. Add exact versions in Directory.Packages.props:

   <PackageVersion Include="SomePackage" Version="7.0.0" />

2. Reference without version in .csproj:

   <PackageReference Include="SomePackage" />

3. Never use floating versions (7.*, [7.0,8.0), etc.).

Common Migration Errors

Checksum Mismatch

Error: Checksum mismatch for migration '{id}'. Expected: '{expected}', actual: '{actual}'.

Cause: A migration file was modified after it was already applied to the database.

Resolution:

• If the change was intentional, update the checksum in the _chsharp_migrations table
• If accidental, revert the migration file to its original state
• Never modify applied migrations in production -- create a new migration instead

Safety Policy Block

Error: Migration blocked by safety policy.

Cause: The operation is destructive (DROP TABLE, DROP COLUMN, etc.) and the corresponding safety flag is disabled.

Resolution:

// Enable specific destructive operations
SafetyPolicy = SafetyPolicy.Default with
{
    AllowDropColumn = true,
    AllowDropTable = true,
    AllowTypeNarrowing = true,
    AllowEngineChange = true,
    AllowDropMaterializedView = true,
    AllowDropDictionary = true
}

Only enable flags you actually need. Review the blocked operations list in the error message to understand exactly what's being prevented.

Engine Change Not Supported

Error: Cannot alter EngineName on table '{table}' (current: '{current}', desired: '{desired}'). This change requires a manual table rebuild.

Cause: ClickHouse doesn't support ALTER TABLE ... ENGINE = .... Changing a table's engine, partition key, primary key, or sample key requires a full rebuild.

Resolution:

1. Create a new table with the desired engine
2. INSERT INTO new_table SELECT * FROM old_table
3. Drop the old table
4. Rename the new table

Write this as a raw SQL migration:

public override void Up(MigrationBuilder builder)
{
    builder.Sql("CREATE TABLE events_new (...) ENGINE = ReplacingMergeTree(version) ...");
    builder.Sql("INSERT INTO events_new SELECT * FROM events");
    builder.Sql("DROP TABLE events");
    builder.Sql("RENAME TABLE events_new TO events");
}

Lock Acquisition Timeout

Error: Failed to acquire migration lock after {n} retries.

Cause: Another process holds the migration lock, or a stale lock wasn't cleaned up.

Resolution:

• Check if another migration is running
• Wait for it to complete (stale locks auto-expire after 2 minutes by default)
• If stuck, check the __migration_lock table and manually clean up
• Adjust lock options if your migrations take longer than expected:

LockOptions = new MigrationLockOptions
{
    MaxRetries = 20,
    RetryDelay = TimeSpan.FromSeconds(10),
    StalenessThreshold = TimeSpan.FromMinutes(5)
}

TreatWarningsAsErrors

Problem: Build fails with warnings treated as errors.

Cause: TreatWarningsAsErrors is enabled globally in Directory.Build.props.

Solution: Fix all warnings. Common ones:

• Nullable reference type warnings -- add ? annotations or null checks
• Unused variables -- remove or prefix with _
• Missing XML docs (if enabled) -- add <summary> comments

Integration Test Setup

Problem: Integration tests fail to start.

Requirements:

• Docker must be running
• Tests use Testcontainers to spin up clickhouse/clickhouse-server:24.8
• Ensure Docker has sufficient resources (the ClickHouse container needs ~512MB RAM)

Running integration tests:

# Make sure Docker is running first
dotnet test tests/CH.Toolkit.IntegrationTests/CH.Toolkit.IntegrationTests.csproj

Integration tests are excluded from the default test filter:

# This skips integration tests
dotnet test CH.Toolkit.sln --filter "FullyQualifiedName!~IntegrationTests"

Multi-Targeting Issues

Problem: Build errors specific to one target framework.

Cause: The project targets both net8.0 and net10.0 (CLI targets net8.0 only). Some APIs may differ between frameworks.

Solution: Use #if directives sparingly for framework-specific code, or prefer APIs available in both targets. Check Directory.Build.props for the target framework configuration.