1

I'm getting a duplicate entry error when trying to create a new UNIQUE KEY in a situation that should be impossible. I currently have a UNIQUE KEY mykey (column_a, column_b) and I'm now trying to run ALTER TABLE mytable ADD UNIQUE KEY mykey2 (column_a, column_b, column_c) and getting a duplicate entry error. How could this possibly be?

It's a very active table, so my first instinct was a race condition, but the original key should still prevent any duplicates.

Kyle Tunis
  • 11
  • 2
  • 2
    @ErgestBasha No it should not be possible. The values in `column_c` are completely irrelevant. If `(column_a, column_b)` fulfills the unique constraint, `(column_a, column_b, column_c)` can never violate it (under the condition that no new rows are created) – derpirscher Jul 29 '22 at 09:09
  • We're indeed _relaxing_ the conditions. Perhaps it'll help to see the actual commands and error messages (albeit with personal data redacted). – Álvaro González Jul 29 '22 at 09:15
  • @ÁlvaroGonzález The query in the question is the exact query I'm trying to run, just with table/column names changed – Kyle Tunis Jul 29 '22 at 09:22
  • 1
    You get the duplicate entry error when you try creating the new key or when adding new rows to the table? And please also add the *exact* error message (you can of course adapt the column/table/key names to match the anonymized names above) – derpirscher Jul 29 '22 at 09:26
  • I get a duplicate key error on mkey as expected see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b9ffd84a5b7c1965c66d1aae7300d3af if your model is not like this then publish sample data. – P.Salmon Jul 29 '22 at 09:29
  • @P.Salmon Well of course, if you create the unique key on already existing data, you may get errors. But as I understand the question, the unique key `(col_a, col_b)` already exists. So creating an additional key `(col_a, col_b, col_c)` should not lead to an error ... – derpirscher Jul 29 '22 at 09:31
  • If you get duplicate error then there exists some duplicated data really. In this case ensure that unique index by 2 columns is present in the table and that it was not altered to common, non-unique index. – Akina Jul 29 '22 at 09:37
  • @derpirscher - you could prove your assertion by posting a fiddle.. – P.Salmon Jul 29 '22 at 09:42
  • @P.Salmon Why would you need a fiddle for this? If in the currently existing data every combination of `(col_a, col_b)` is unique (and if we believe OP, that `mykey` already exists, this must be true), then how can any combination of `(col_a, col_b, col_c)` suddenly be not unique anymore? – derpirscher Jul 29 '22 at 09:47
  • @derpirscher the exact error is: ```Error:Error 1062: Duplicate entry 'val1-val2-val3' for key 'mykey2'``` – Kyle Tunis Jul 29 '22 at 09:57
  • @KyleTunis Are you really sure `mykey` is created on the very same table as a `uniqe key` on `(column_a, column_b`)? I'd say that can't be. If it really is, you have a massive problem with inconsistent indexes in your DB engine. – derpirscher Jul 29 '22 at 10:04
  • @KyleTunis Can you duplicate this table into a testing environment where no data updates happen and then try creating that key? – derpirscher Jul 29 '22 at 10:08
  • @derpirscher yeah it worked fine in my testing env, I guess there's something weird with my initial index. I can try rebuilding that one and see if that gives any sort of insight/solution – Kyle Tunis Jul 29 '22 at 10:31

1 Answers1

0

Discovered this issue which seems to explain what I was seeing, locking the table resolves the problem :)

Kyle Tunis
  • 11
  • 2