0

I have a table that serves as a foreign key lookup from another table. The table is very simple, containing a ID column with is the primary key and a JSON column. I wish to remove abandoned entries from this table.

I tried running this script:

DELETE 
FROM `ate`.`test_configuration`
WHERE `ate`.`test_configuration`.`ID` NOT IN (SELECT DISTINCT `ate`.`index`.`TestID` from `ate`.`index`);

But encountered an error stating my I wasn't using a where clause that uses the key column:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

This is confusing as my where clause does use the primary key column. I am aware that I can disable safe mode as part of my script as a workaround, but would still like to understand why I'm getting this error. I'd like to avoid unsafe updates if possible.

Community
  • 1
  • 1
Fr33dan
  • 4,227
  • 3
  • 35
  • 62
  • Maybe this answer can help you to understand http://stackoverflow.com/a/18767430/2294163 – Alex Slipknot Mar 22 '17 at 14:02
  • I understand that that is the problem, but I am trying to implement the first solution rather than the second. My where clause uses an index as they suggest. – Fr33dan Mar 22 '17 at 14:06

1 Answers1

0

I believe Optimizer just unable to use index effectively for such query - so it does full table scan.

How many rows are in the test_configuration and how many of them will be deleted?

(You might try to use index hints to force optimizer to use index for the query, just not sure if they are supported in your version of mysql).

noonex
  • 1,975
  • 1
  • 16
  • 18
  • This table is tiny. There are about 600 rows in the table, 400 of which I'm trying to delete (mostly converted versions of the same data). While I'm using MySql 5.7.11 which does support index hints, they only seem to work for select statements. Is the safe query error about optimization or data integrity? I was under the impression the primary reason for requiring a key is to verify that you are changing the row(s) you intended to (which will be uniquely identified by the key). – Fr33dan Mar 22 '17 at 15:10
  • I believe it is implemented very simple - if no index was used - generate the warning. And from optimizer point of view - it is simpler to just do table scan if most of rows will be deleted. I am 100% sure that ignoring this particular warning for small tables is safe. – noonex Mar 22 '17 at 15:43
  • Sorry for the long delay in reply, I was SO did not notify me about these comments. `ID` is the primary key and as such has a primary index. It is a [UUID in binary(16) format](http://stackoverflow.com/a/7168916/1111886) My question is less about how I can get around the problem and more why it is happening. As I'm comparing against the primary key of my table the error shouldn't exist at all. – Fr33dan Mar 22 '17 at 17:50
  • I can only repeat that the warning triggers as well in cases when optimizer decides that table scan is more effective than index lookups. Not sure if it is good, but it is the way it was implemented. You can EXPLAIN your command and I expect that it will reveal that the index is not used. I'd just ignore it as there is no point to solve puzzles why it was implemented that way. – noonex Mar 22 '17 at 19:36
  • 1
    I guess I wasn't understanding your statement that not using the index was directly causing the error because that simply isn't what the error says. It gives no mention of an index. I don't understand why it wouldn't use the index as all the data need for this query should be there, but more importantly I don't understand why the index is related to this error at all. If you're right there may be no solution but the workaround I already found, but I really really hate implementing such things without understanding the situation entirely, – Fr33dan Mar 22 '17 at 20:00
  • 1
    I can think about two reasons why "safe updates mode" was introduced: 1. updates without index can cause major performance and locking impact in various situations on huge tables. 2. It can be a sign that user doesn't understand what they do. Neither of that relates to your situation. And yes - I think Server is not smart enough to distinguish situation where index present, but not used because of optimizer choice. (Maybe just wording in the message confuses). So yes - I think you should just work around it in this case and forget – noonex Mar 22 '17 at 20:19