MySQL Duplicate entry for key unique key

Question

I have a table with a unique key which is a combination of 4 columns. Column A,B,C and D.

The values in the columns are like this:

Column A	Column B	Column C	Column D
111000	150100_XYZ_XY02	150100-A2_WXYZ_XY02	150100-A2_17090000_WXYZ_XY02B
222111	150122_XYZ_XY02	150122-A2_WXYZ_XY02	150122-A2_17090000_WXYZ_XY02B

When I insert the following record in the table,

Column A	Column B	Column C	Column D
222111	150122_XYZ_XY02	150122-A2_WXYZ_XY02	150122-A2_17090000_WXYZ_XY02

Notice that the only difference is last character of the Column D. It throws the exception:

org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Duplicate entry '222111-150122_XYZ_XY02-150122-A2_WXYZ_XY02-150122-A2_17090000_WXYZ_XY02' for key 'my_table_unique_idx1' 

Even though there's a difference in the value of one column, why there would be an exception?

Update: Here's the schema:

 CREATE TABLE mytable
(
 my_uuid VARCHAR(100) NOT NULL,
 s_id VARCHAR(100) NOT NULL,
 p_id VARCHAR(100) NOT NULL,
 pl_id VARCHAR(100) NOT NULL,
 ac_id VARCHAR(100),
 m_seg VARCHAR(100),
 et_date DATETIME,
 etr_date DATETIME,
 refreshed_date TIMESTAMP,
 created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL,
 deleted_date TIMESTAMP NULL DEFAULT NULL,
 PRIMARY KEY (`p_id`, `my_uuid`),
UNIQUE KEY `really_really_long_name_for_unique_idx1` (`p_id`,`s_id`,`pl_id`,`ac_id`),
 KEY `really_really_long_name_for_updated_date` (`updated_date`),
 KEY `really_really_long_name_for_seg_idx_1` (`m_seg`),
 KEY `really_really_long_name_for_et_date_idx_1` (`et_date`),
 KEY `really_really_long_name_for_etr_date_idx_1`(`etr_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!50100 PARTITION BY KEY (p_id)
PARTITIONS 100 */

Here's the fiddle - I am unable to reproduce the same in fiddle though: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=59ed46385738617c8b456f8b661a96fd

• Nick S: I would suggest posting the definition of my_table_unique_idx1. While you may think it is on columns A,B,C,D perhaps it is only on columns A,B,C?
• Vishal_Kotecha: I am preparing a fiddle and also adding the definition here in a few minutes
• Vishal_Kotecha: @ErgestBasha I have updated the description with fiddle
• Vishal_Kotecha: @NickSI have added the definition
• Vishal_Kotecha: @JohnEisbrener No, I am not -

Answer 1

UNIQUE indexes do not work with PARTITIONed tables.

Furthermore, having the partition key be the first column in an index is almost always not useful.

• Vishal_Kotecha: thank you for looking into this, could you please what should be the approach then? Also, could you share the documentation link supporting your answer, if that's okay? Thanks again!
• Rick James: @Vishal_Kotecha - Much experimentation, summarized in blog on Partitioning. So far, I see no benefit in Partitioning; what other queries do you have? The lack of UNIQUE and FK is mentioned in the main docs, plus in multiple feature requests (aka bug reports).
• J. Gwinner: Wow, that's a killer - good to know!