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.

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.

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.