Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error when deleting from table #79

Open
kboniadi opened this issue Jun 13, 2023 · 0 comments
Open

Error when deleting from table #79

kboniadi opened this issue Jun 13, 2023 · 0 comments

Comments

@kboniadi
Copy link

kboniadi commented Jun 13, 2023

I'm currently on the latest version of clickhouse 23.5. I'm having some trouble deleting.
for reference this is my ReplicatedReplacingMergeTree table + Distributed table:

create table Test
(
    Id             Int32,
    IdNull Nullable(Int32),
    smallInt       Int16,
    smallIntNull Nullable(Int16),
    Name           String,
    NameNull Nullable(String),
    DateOnly       Date,
    TimeAndDate Nullable(DateTime64(7, 'UTC')),
    IsOk           Bool,
    IsOkNull Nullable(Bool),
    TimeDateOffset DateTime64(7, 'UTC'),
    TimeDateOffsetNull Nullable(DateTime64(7, 'UTC')),
    decimalVal     Decimal(14, 4),
    decimalValNull Nullable(Decimal(14, 4))
)
    engine = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
        PARTITION BY toYYYYMMDD(DateOnly)
        PRIMARY KEY Id
        ORDER BY Id

CREATE TABLE tm.Test_distr ON CLUSTER poccluster AS tm.Test ENGINE = Distributed(poccluster, tm, Test, Id);

when running this query DELETE FROM {tableName} WHERE {primaryKey}=@Id I get this error:

DB::Exception: ALTER UPDATE/ALTER DELETE statement with subquery may be nondeterministic, see allow_nondeterministic_mutations setting

when using this query DELETE FROM {tableName} WHERE {primaryKey}=@Id SETTINGS allow_nondeterministic_mutations=1 I get this error:

DB::Exception: Exception happened during execution of mutation '0000000000' with part '20230213_0_0_0' reason: 'Code: 60. DB::Exception: Table default._ba66fb07cc06412a8e22e2798cecff4e doesn't exist: While processing (SELECT _ba66fb07cc06412a8e22e2798cecff4e.Id FROM _ba66fb07cc06412a8e22e2798cecff4e) AS _subquery983172: While processing Id = ((SELECT _ba66fb07cc06412a8e22e2798cecff4e.Id FROM _ba66fb07cc06412a8e22e2798cecff4e) AS _subquery983172). (UNKNOWN_TABLE) (version 23.5.1.3174 (official build))'. This error maybe retryable or not. In case of unretryable error, mutation can be killed with KILL MUTATION query

The table is confirmed created, and primaryKeys are integers. I'm using dapper to execute this delete command. Is Delete operation currently supported? Or perhaps someone has a fix or knows what is causing this error?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant