MySQL truncates composed unique index to 64 characters

Question

I'm having problems with MySQL composed unique key.

It consists of URL, integer value and date field.

But when I try to insert row, I get an exception:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'http://cars.auto.ru/cars/used/sale/16152870-c13f1.html-2012-02-1' for key 'one_a_day_idx'

As you can see, composed index was truncated by 64 characters, and because of this it is not an unique any more (I'm retrieving data from external source once a day)

But the most confusing that the record was inserted, though an exception about constraint violation was thrown

There was a similar question here, but the only advise was to use SHOW CREATE TABLE to find out the actual length of the index.

Show create tables shows this:

| auto_ru_sale | CREATE TABLE `auto_ru_sale` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `template` int(11) NOT NULL,
  `region` varchar(128) NOT NULL,
  `URI` varchar(128) NOT NULL,
  `subType` varchar(128) NOT NULL,
  `cost` int(11) NOT NULL,
  `productionYear` int(11) NOT NULL,
  `engineVolume` int(11) NOT NULL,
  `transmitionType` varchar(1) NOT NULL,
  `run` int(11) NOT NULL,
  `evaluationDate` date NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `one_a_day_idx` (`template`,`URI`,`evaluationDate`),
  KEY `prodyear_idx` (`productionYear`),
  KEY `evdate_idx` (`evaluationDate`),
  CONSTRAINT `auto_ru_sale_ibfk_1` FOREIGN KEY (`template`) REFERENCES `auto_ru_
datatemplate` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=127012 DEFAULT CHARSET=utf8 |

So, I don't see any limitations to index length.

Many thanks to everyone, who can give some help about this issue.

Solitta

Did you ever figure out a solution to your problem? We have a similar issue with a unique index on a VARCHAR(255) field. Apparently JDBC/JPA throws a MySQLIntegrityConstraintViolationException including the first 64 characters of the string value we are trying to insert. However, inserting the same value directly via MySQL Workbench works fine without problem. This leads me to believe it might be related to JPA or the JDBC driver. – Doriandoric

We found a solution to our problem. By default the MySQL collation is case insensitive. The values we tried to insert in our table had the same characters and sometimes they just differed by one letter being uppercase, the other lowercase. Like 'abcd' vs. 'aBcd'. With the default collation it will cause a unique index violation (I guess same for primary keys). We changed it to utf8_bin and then it worked for us. – Doriandoric

Answer 1

I experienced the same problem today.

The problem in my case was that the unique index actually was longer than 64 characters, but the error message only shows the first 64 characters. I think the it is the same case here.

So what I encountered was an actual duplicate key, which was caused by some wrong date-format.

Infuriate

Answer 2

I haven't an answer to your exact question however a suggestion which should fix it while improving database times.

Create a table i.e. URIs (URI_id INT UNSIGNED PK AI, URI VARCHAR(128) NOT NULL, UNIQUE KEY (URI)) and change auto_ru_sale to store uri_id instead of URI

This will mean that key becomes a key of INT,INT,DATE which will be more efficient. Your table size will reduce due to less duplicates. All in all normalisation is beneficial.

I would also advise the same applied to the "region" field as I would assume that has a lot of duplicate data in also.

Schmitt