MariaDB, Delete with condition not working

Question

I'm building an App and using MariaDB as my database. I have a table "kick_votes". Its primary key consits of three fields:

user_id
group_id
vote_id

I need to delete rows where user_id AND group_id fulfill my conditions or just the vote_id. I enabled the config, that I have to use a key column in my WHERE clause for security issues.

This one is working correctly:

 DELETE FROM kick_votes WHERE (user_id=86 AND group_id=10); DELETE FROM kick_votes WHERE vote_id=2;

But I don't want to use two statements, but the following doesn't work:

DELETE FROM kick_votes WHERE (user_id=86 AND group_id=10) OR vote_id=2;

I get the error:

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

Why isn't it working?

Possible duplicate of MySQL error code: 1175 during UPDATE in MySQL Workbench — treyBake
– treyBake, Commented Nov 30, 2018 at 10:48

Nikita Malyavin · Accepted Answer · 2018-11-30 18:50:29Z

Seems like key existence doesn't actually affect the error. From mariadb sources, mysql_delete:

const_cond= (!conds || conds->const_item()); safe_update= MY_TEST(thd->variables.option_bits & OPTION_SAFE_UPDATES); if (safe_update && const_cond) { my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE, ER_THD(thd, ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0)); DBUG_RETURN(TRUE); }

So the only variant is to turn off safe mode:

set @@sql_safe_updates= 0;

Rick James · Accepted Answer · 2018-11-30 22:53:19Z

Try this Kludge:

DELETE FROM kick_votes WHERE id > 0 -- assuming this is your PRIMARY KEY AND ( (user_id=86 AND group_id=10) OR vote_id=2 );

Collectives™ on Stack Overflow

MariaDB, Delete with condition not working

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related