Load table error

[agoncear-mwb]

(you don't have to strictly follow this form)

Describe the unexpected behaviour When using chdb for inserting data into a table (classic MergeTree table) in the local filesystem, sometimes it give the following error: Code: 722. DB::Exception: Waited job failed: Code: 695. DB::Exception: Load job 'load table default.TEST' failed: Code: 49. DB::Exception: Part all_835_861_2_859 intersects next part all_841_858_2_859. It is a bug or a result of manual intervention in the server or ZooKeeper data: Cannot attach tabledefault.TESTfrom metadata file /tmp/chdb/store/5fa/5faa5bad-0a65-4892-8d70-e9c0e3e6c0e1/TEST.sql from query ATTACH TABLE default.TEST

How to reproduce

• Which ClickHouse server version to use : 24.5

• Which interface to use, if matters

• Non-default settings, if any

• CREATE TABLE statements for all tables involved: create TABLE IF NOT EXISTS default.TEST ( accessrights Int64 CODEC(T64,ZSTD(1)), accountid String NOT NULL CODEC(ZSTD(2)), hwid String NOT NULL CODEC(ZSTD(2)), ruleidx Array(Int64) CODEC(T64,ZSTD(1)), eventid String CODEC(ZSTD(2)), fileingestiontimestamp DateTime64(9), ) ENGINE= MergeTree() ORDER BY (accountid,hwid,eventid,ruleidx,fileingestiontimestamp)

• Sample data for all these tables, use clickhouse-obfuscator if necessary

• Queries to run that lead to unexpected result

Expected behavior Since the engine is used for insert only, i would not expect it to fail in this way. Also, the writer process is single threaded and doesn't have any interference. The bug appear to be pretty random and with no correlation with the load of the inserts.

The insert process, insert a fixed 10240 records at time with async_insert=1 in each query.

Error message and/or stacktrace Code: 722. DB::Exception: Waited job failed: Code: 695. DB::Exception: Load job 'load table default.TEST' failed: Code: 49. DB::Exception: Part all_835_861_2_859 intersects next part all_841_858_2_859. It is a bug or a result of manual intervention in the server or ZooKeeper data: Cannot attach tabledefault.TESTfrom metadata file /tmp/chdb/store/5fa/5faa5bad-0a65-4892-8d70-e9c0e3e6c0e1/TEST.sql from query ATTACH TABLE default.TEST

Additional context Add any other context about the problem here.

[auxten]

Are you using the session mode to do the insert? It will be super helpful if you can show me some code of how you do that

[agoncear-mwb]

Yes, i'm using session mode to insert the data, since i need it for later access.

I'm actually using the go wrapper of chdb, but since the library is dynamically linked you should be able to replicate.

func (s *chdb) FlushEvents(events []*events) error {
    //NOTE: the bucket of events is ALWAYS of 10240 elements, no matter how much real events are in there
    b := sqlbuilder.ClickHouse.NewInsertBuilder().InsertInto(s.fullTableName).SQL(" (*) SETTINGS async_insert=1 ")
    currTime := time.Now().UTC().UnixNano()
    for _, evt := range events {
        if evt == nil {
            continue
        }

        b.Values(
            evt.Accessrights, evt.Accountid, evt.HwId, evt.RuleIdx, evt.EventId,
        )

    }

    q, args := b.Build()
    if len(args) == 0 {
        return nil
    }
    fq, err := sqlbuilder.ClickHouse.Interpolate(q, args)
    if err != nil {
        return err
    }

    _, err = s.dbConnection.Query(fq)
    if err != nil {
        if !(err == sql.ErrNoRows || err.Error() == "result is nil") {
            return err
        }
    }
    return nil
}

[agoncear-mwb]

@auxten i noticed this log in the same time of the bug:

Code: 82. DB::Exception: Database _temporary_and_external_tables already exists. (DATABASE_ALREADY_EXISTS)