2

I have a field (link) that is varchar (1500) and that I want to make unique. I applied changes to mysql configuaration and increased length to 3072 bytes 

ROW_FORMAT=DYNAMIC, innodb_file_format = Barracuda, innodb_large_prefix = true

But when I apply unique to my field, I got next error:

"#1071 - Specified key was too long; max key length is 3072 bytes"

My field is varchar(1500) that is 3000 bytes.

What's wrong?

Update (1) Table data:

CREATE TABLE IF NOT EXISTS `pages` ( `link` varchar(1500) NOT NULL, `domain` varchar(255) NOT NULL, `lastvisited` datetime DEFAULT NULL, `id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), KEY `link` (`link`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC;

Update (2) Alter command (done via PHPMYADMIN)

ALTER TABLE `pages` ADD UNIQUE ( `link` )
Improve this question
22
  • What is the character set of table and column ? Commented Feb 25, 2014 at 9:20
  • utf8. I updated the question. Commented Feb 25, 2014 at 9:24
  • 3
    If you're using UTF-8 each character can be 3 bytes - that's 4500 bytes and exceeds the maximum key length. I'd suggest that in any case a key that long is going to be unwieldy at best. Commented Feb 25, 2014 at 9:24
  • you can make your column link as TEXT type if you want to insert a long string in it. Commented Feb 25, 2014 at 9:25
  • 1
    @Tigran: Your CREATE statement doesn't show the UNIQUE constraint that you talk about in your question, just a non-unique KEY Commented Feb 25, 2014 at 9:29

1 Answer 1

Reset to default
3

Since you will be storing URLs in the link column, you don't actually need to use UTF8 for it, because URLs can contain only ASCII characters. Specifying a plain ASCII character encoding for your link column will even allow you to raise its max length to 3072 characters.

CREATE TABLE IF NOT EXISTS `pages` ( `link` varchar(1500) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `domain` varchar(255) NOT NULL, `lastvisited` datetime DEFAULT NULL, `id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), UNIQUE KEY `link` (`link`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC;

(Updated as per @eggyal's suggestion for the ascii_bin collation)

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

+1 I was just about hit "post" with exactly the same answer, albeit recommending a limit of 2000 characters per What is the maximum length of a URL in different browsers? Also, I would recommend specifying the ascii_bin collation for the column, since URL paths can be case-sensitive.
Are you sure with 'URLs can contain only ASCII characters'.What about Russia and China zones? As far as I know, they are using non ASCII symbols in domain names.
@Tigran: Non-ASCII characters in URLs need to be percent-encoded. Reference: RFC 3986
@lanzz: More explicitly, they must be percent-encoded in the path, or Puncycode-encoded in the hostname: but wherever they are placed, most browsers will perform the encoding "behind the scenes" whilst displaying non-ASCII characters to the user.
I have a problem with your solution, when I create table, I get error that #1709 - Index column size too large. The maximum column size is 767 bytes. However, if I create initial table error is "#1071 - Specified key was too long; max key length is 3072 bytes".
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.