[bug] BulkInsert intermittently fails

[Da-Teach]

Since we upgraded to v7 BulkInsert intermittently fails, most likely due to InitAsync mixing up column order [assumption].

The exception we're getting is:

ClickHouse.Client.Copy.ClickHouseBulkCopySerializationException: Error when serializing data
 ---> System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.DateTime'.
   at ClickHouse.Client.Types.DateTimeType.Write(ExtendedBinaryWriter writer, Object value)
   at ClickHouse.Client.Copy.ClickHouseBulkCopy.BulkCopyHttpContent.SerializeBatchAsync(Stream stream)
   --- End of inner exception stack trace ---

The table we're inserting into starts with a DateTime field, followed by a UUID/Guid field. Before v7 (and before InitAsync was required), the inserting worked fine. Now the BulkInsert intermittenly fails with the above exception. Now the weird thing is that it truly is random, as the next BulkInsert (same data, as we retry on storage failure) works fine.

[DarkWanderer]

Thank you for report. This is a second major bug report after merge of #464. I will investigate a bit more and likely revert that PR.

Is there more data you can provide? (table structure, settings you use, approximate data composition)

[Da-Teach]

Here's the table:

CREATE TABLE default.rewards
(
    `created_on` DateTime,
    `battle_id` UUID,
    `user_id` Int32,
    `account_id` Int64,
    `stage_id` Int32,
    `resource_id` Nullable(Int32),
    `resource_quantity` Nullable(Float32),
    `bmi_id` Nullable(Int32),
    `bmi_quantity` Nullable(Int32),
    `artifact_id` Nullable(Int32),
    `artifact_set` Nullable(Int32),
    `artifact_rarity` Nullable(Int32),
    `artifact_rank` Nullable(Int32),
    `artifact_kind` Nullable(Int32),
    `artifact_required_faction` Nullable(Int32),
    `artifact_primary_kind` Nullable(Int32),
    `artifact_primary_is_absolute` Nullable(Bool),
    `artifact_primary_value` Nullable(Int32),
    `artifact_primary_power_up_value` Nullable(Int32),
    `artifact_secondary_1_kind` Nullable(Int32),
    `artifact_secondary_1_is_absolute` Nullable(Bool),
    `artifact_secondary_1_value` Nullable(Int32),
    `artifact_secondary_1_power_up_value` Nullable(Int32),
    `artifact_secondary_2_kind` Nullable(Int32),
    `artifact_secondary_2_is_absolute` Nullable(Bool),
    `artifact_secondary_2_value` Nullable(Int32),
    `artifact_secondary_2_power_up_value` Nullable(Int32),
    `artifact_secondary_3_kind` Nullable(Int32),
    `artifact_secondary_3_is_absolute` Nullable(Bool),
    `artifact_secondary_3_value` Nullable(Int32),
    `artifact_secondary_3_power_up_value` Nullable(Int32),
    `artifact_secondary_4_kind` Nullable(Int32),
    `artifact_secondary_4_is_absolute` Nullable(Bool),
    `artifact_secondary_4_value` Nullable(Int32),
    `artifact_secondary_4_power_up_value` Nullable(Int32),
    `hero_type_id` Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY (stage_id, created_on, account_id)
SETTINGS index_granularity = 8192

Note that it fails on other tables too.

The function that does the BulkInsert:

        private async Task StoreAsync(List<Reward> batch)
        {
            Log.ForContext<RewardRepository>().Verbose("Storing {count} rewards", batch.Count);

            await using var scope = _serviceProvider.CreateAsyncScope(); ;
            await using var clickHouse = scope.ServiceProvider.GetRequiredService<ClickHouseConnection>();

            using var bulk = new ClickHouseBulkCopy(clickHouse)
            {
                DestinationTableName = "rewards",
            };

            await bulk.InitAsync();
            await bulk.WriteToServerAsync(batch.Select(reward => reward.ToObjects()));
        }

The 'ToObjects' function is as follows:

            public object[] ToObjects()
            {
#pragma warning disable CS8601 // Possible null reference assignment.
                return new object[]
                {
                    CreatedOn,
                    BattleId,
                    UserId,
                    AccountId,
                    StageId,
                    ResourceId,
                    ResourceQuantity,
                    BmiId,
                    BmiQuantity,
                    ArtifactId,
                    ArtifactSet,
                    ArtifactRarity,
                    ArtifactRank,
                    ArtifactKind,
                    ArtifactRequiredFaction,
                    ArtifactPrimaryKind,
                    ArtifactPrimaryIsAbsolute,
                    ArtifactPrimaryValue,
                    ArtifactPrimaryPowerUpValue,
                    ArtifactSecondary1Kind,
                    ArtifactSecondary1IsAbsolute,
                    ArtifactSecondary1Value,
                    ArtifactSecondary1PowerUpValue,
                    ArtifactSecondary2Kind,
                    ArtifactSecondary2IsAbsolute,
                    ArtifactSecondary2Value,
                    ArtifactSecondary2PowerUpValue,
                    ArtifactSecondary3Kind,
                    ArtifactSecondary3IsAbsolute,
                    ArtifactSecondary3Value,
                    ArtifactSecondary3PowerUpValue,
                    ArtifactSecondary4Kind,
                    ArtifactSecondary4IsAbsolute,
                    ArtifactSecondary4Value,
                    ArtifactSecondary4PowerUpValue,
                    HeroTypeId,
                };
#pragma warning restore CS8601 // Possible null reference assignment.
            }

All of the BulkInserts follow pretty much this structure. The insert data isn't actually "that high" , but we try to buffer as much as possible.

[DarkWanderer]

Thank you for information. I'll try to investigate the error more this week.

I have also released 7.4.1-hotfix version which contains a rollback of the change I suspect is responsible - if possible, please try it to see if it fixes the issue

[Da-Teach]

The hotfix resolves the BulkInsert issue.

[DarkWanderer]

Thank you for confirmation - I've released the fix as 7.5.0 and delisted 7.4.*. Sorry for inconvenience

[Da-Teach]

No worries, all part of software development.

[MJEdwin]

did you have a demo for this bug? i want a test case and find the reason @Da-Teach