1

I am aware innodb imposes a limit on key size, but an INT?

    CREATE TABLE IF NOT EXISTS  `db`.`TAGS` (

 `tag_id` INT( 11 ) NOT NULL ,
 `tag_text` VARCHAR( 700 ) NULL ,
 `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
 `xxx_id` INT( 11 ) NOT NULL COMMENT  'This is for ease of reference lookup later',
 `user_id` INT( 11 ) NOT NULL ,
 `TAG_FACT_RELATION_relation_id` INT NOT NULL ,
PRIMARY KEY (  `tag_id` ) ,
UNIQUE INDEX  `tag_UNIQUE` (  `tag_text` ASC ) ,
UNIQUE INDEX  `tag_id_UNIQUE` (  `tag_id` ASC ) ,
INDEX  `fk_TAGS_TAG_FACT_RELATION1_idx` (  `TAG_FACT_RELATION_relation_id` ASC ) ,
CONSTRAINT  `fk_TAGS_TAG_FACT_RELATION1` FOREIGN KEY (  `TAG_FACT_RELATION_relation_id` ) REFERENCES  `meepl`.`TAG_FACT_RELATION` (
`relation_id`
) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB;

There error says:

#1709 - Index column size too large. The maximum column size is 767 bytes.

Any ideas? I have been muching around with data types for ages.

ddoor
  • 5,819
  • 9
  • 34
  • 41
  • 2
    What about `tag_text` – juergen d Jul 20 '14 at 18:59
  • In addition to @juergend following excerpt of the manual to CREATE INDEX: *Note Prefix limits are measured in bytes, whereas the prefix length in CREATE INDEX statements is interpreted as number of characters for nonbinary data types (CHAR, VARCHAR, TEXT). Take this into account when specifying a prefix length for a column that uses a multi-byte character set.* – VMai Jul 20 '14 at 19:02
  • Thanks, yes it looks like tag_text. How do I fix this without making me VARCHAR(100) ? Should I just remove it as an index? – ddoor Jul 20 '14 at 19:06
  • 1
    You could set a prefix length, i.e. `UNIQUE INDEX tag_UNIQUE ( tag_text (150) ), ...` The first 150 characters of the column have to be unique, this could take up to 600 Bytes. – VMai Jul 20 '14 at 19:09
  • Possible duplicate of [MySQL error: The maximum column size is 767 bytes](https://stackoverflow.com/questions/30761867/mysql-error-the-maximum-column-size-is-767-bytes) – Ruslan Jul 08 '17 at 04:37

1 Answers1

4

A bit late but I had a similar issue.

Your tag_text column has a UNIQUE_INDEX and that is what is causing the issue, not the INT datatype.

Change your MySql installation to enable larger index columns by enabling the innodb_large_prefix setting.

See http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

lordg
  • 520
  • 5
  • 25