0

First time posting here although I am fairly sure this specific situation hasn't already been addressed. The closest one I have found has been where someone was using their primary keys in a JOIN statement which was causing their issue, however this is not the case here.

Luckily I can keep this fairly simple as the database is still under development. If it matters I am doing all the work in MySQL Workbench.

I have a table projects, which among other information has project_id as the primary key, into which I have inserted a load of test projects and now have tried deleting one of them:

DELETE FROM `database_name`.`projects` WHERE (`project_id` = '38');

Only to get the below:

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1175: 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

But of course, project_id is quite literally the primary key.

What I have attempted so far:

  1. removing the parentheses from the WHERE clause before attempting to execute it
  2. adding projects. before the column name in the WHERE clause
  3. using a different column in the WHERE clause (project_name which is a unique key)
  4. Every combination of the above (so 1 and 2, 1 and 3, 2 and 3, and all 3)

What on earth could possibly be causing this? My MariaDB version is 10.4.25.

In the comments it has been mentioned that it could in fact be my foreign keys causing this issue, as deleting data in this table then attempts to delete data in other tables not by their primary key. So now to figure out a way around that!

Final edit, solution found!

Well, more of a workaround than a solution in my mind, but I'll leave that up to you.

So whilst it was indeed SQL safe mode preventing me from deleting data from other tables by columns that are not keys in that table (it was in the BEFORE DELETE trigger rather than anything to do with foreign keys), SQL safe mode itself is not set at the server level - it can be set at the server level, but generally isn't - it was set in my Workbench.

So the solution/hack/workaround within that trigger is below:

SET SQL_SAFE_UPDATES = 0;
DELETE FROM projects_change_log WHERE project_id = OLD.project_id;
SET SQL_SAFE_UPDATES = 1;

Of course, always make sure to reactivate safe mode when you're done needing it to not be activated, as it's still a good safety mechanism!

5
  • 1
    Are there any foreign keys with ON DELETE CASCADE enabled, referring to that key? Or triggers that might try removing other data in response to the row being removed? In either case the error might be referring to the cascaded actions. Sep 20 at 11:04
  • @DavidSpillett as it happens yes, there is a BEFORE DELETE trigger on the table (however it doesn't try to remove any other data, just copy the project to a table of deleted projects) and 2 foreign keys with ON DELETE CASCADE
    – Gavin
    Sep 20 at 11:09
  • you should edit that extra detail into the question, it may get “lost” in comments and not seen by others who may be able to help. As the FKs are set to cascade, does your delete work if removing something that is not referenced by those keys? If so then they are where the error is coming from (unless pg will complain because in some circumstances the keys will cause deletes, try removing the FKs in a test copy of the DB as another test). Sep 20 at 20:57
  • Please provide SHOW CREATE TABLE projects. And show us the Cascading tables, too.
    – Rick James
    Sep 20 at 22:33
  • Thanks @DavidSpillett I've updated the question now, and I'm going to get to work on investigating that today, although I suspect you're right as the cascading tables attempt to delete rows not via a primary key. Going to also try and work around that with maybe a subquery or something.
    – Gavin
    Sep 22 at 8:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.