Re: Problem with long unique index
Posted by: Todd Farmer
Date: August 14, 2013 08:39AM

Hi Jochen,

The problem isn't truncation of the values - the error message formatting truncates values, but the actual values used for the INSERT operation aren't truncated (and must have a collision with existing data). Here's a quick example of the error message truncation at work:

mysql> CREATE TABLE utest (
-> res1 VARCHAR(255) NULL,
-> res2 VARCHAR(255) NULL,
-> id INT NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> UNIQUE KEY bothres (res1, res2)
-> );
Query OK, 0 rows affected (0.53 sec)

mysql> INSERT INTO utest (res1, res2)
-> VALUES ('a', 'b');
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO utest (res1, res2)
-> VALUES ('a', 'b');
ERROR 1062 (23000): Duplicate entry 'a-b' for key 'bothres'
mysql> INSERT INTO utest (res1, res2)
-> VALUES (REPEAT('a', 50), REPEAT('b',50));
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO utest (res1, res2)
-> VALUES (REPEAT('a', 50), REPEAT('b',50));
ERROR 1062 (23000): Duplicate entry 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa-bbbbbbbbbbbbb' for key 'bothres'
mysql> INSERT INTO utest (res1, res2)
-> VALUES (REPEAT('a', 75), REPEAT('b',75));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO utest (res1, res2)
-> VALUES (REPEAT('a', 75), REPEAT('b',75));
ERROR 1062 (23000): Duplicate entry 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaa' for key 'bothres'
mysql>


Looks like the error message won't exceed 65 characters total in reporting values - which matches exactly what you provided.

http://reference.data.gov.uk/def/intervals/Minute-http://bio2rdf
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa-bbbbbbbbbbbbb

Unfortunately, that means you'll need to investigate how it's possible duplicate values are being sent. I suggest you enable the general query log (or Enterprise Audit log plugin, if you are a customer) to track sequence of operations as seen from the MySQL Server side.

Hope that helps!

--
Todd Farmer
MySQL @ Oracle
http://www.oracle.com/mysql/

Options: ReplyQuote


Subject
Written By
Posted
Re: Problem with long unique index
August 14, 2013 08:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.