#1071 - Specified key was too long; max key length

Question

I this SQL query to create a table:

CREATE TABLE IF NOT EXISTS `local_sysDB`.`hashtags` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `hashtag` VARCHAR(255) NOT NULL COMMENT 'hashtag must be unique. Must be saved without #',
  `accountId` INT NULL,
  `startTracking` DATETIME NOT NULL COMMENT 'When tracking of the hashtag start',
  `endTracking` DATETIME NOT NULL COMMENT 'When tracking of the hashtag ends',
  `channelInstagram` TINYINT(1) NOT NULL DEFAULT 1,
  `channelTwitter` TINYINT(1) NOT NULL DEFAULT 1,
  `channelYoutube` TINYINT(1) NOT NULL DEFAULT 1,
  `postLimit` INT NOT NULL,
  `suspendOnLimit` TINYINT(1) NOT NULL DEFAULT 1,
  `created` TIMESTAMP NOT NULL DEFAULT NOW(),
  `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  `approveBeforeView` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'If account should approve posts before being displayed public',
  `suspended` TINYINT(1) NOT NULL DEFAULT 0,
  `InstagramSubscriptionId` INT(10) UNSIGNED NULL,
  `deleted` TINYINT(1) NULL DEFAULT 0 COMMENT 'if hashtag is marked for deletion',
  `collectedPosts` BIGINT(50) UNSIGNED NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `hashtags_hashtag` (`hashtag` ASC)  KEY_BLOCK_SIZE=255,
  INDEX `hashtags_accounts_accountId` (`accountId` ASC),
  INDEX `hashtag_trackingDate` (`startTracking` ASC, `endTracking` ASC),
  INDEX `hashtag_collectedPosts` (`collectedPosts` ASC),
  INDEX `hashtag_updated` (`updated` ASC),
  FULLTEXT INDEX `hashtag_search` (`hashtag` ASC),
  CONSTRAINT `hashtags_accounts_accountId`
    FOREIGN KEY (`accountId`)
    REFERENCES `local_sysDB`.`accounts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
ROW_FORMAT = COMPRESSED
KEY_BLOCK_SIZE = 16;

When I try to run this, I get the following error:

SQL-query:

CREATE TABLE IF NOT EXISTS `local_sysDB`.`hashtags` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `hashtag` VARCHAR(255) NOT NULL COMMENT 'hashtag must be unique. Must be saved without #',
  `accountId` INT NULL,
  `startTracking` DATETIME NOT NULL COMMENT 'When tracking of the hashtag start',
  `endTracking` DATETIME NOT NULL COMMENT 'When tracking of the hashtag ends',
  `channelInstagram` TINYINT(1) NOT NULL DEFAULT 1,
  `channelTwitter` TINYINT(1) NOT NULL DEFAULT 1,
  `channelYoutube` TINYINT(1) NOT NULL DEFAULT 1,
  `postLimit` INT NOT NULL,
  `suspendOnLimit` TINYINT(1) NOT NULL DEFAULT 1,
  `created` TIMESTAMP NOT NULL DEFAULT NOW(),
  `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  `approveBeforeView` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'If account should approve posts before being displayed public',
  `suspended` TINYINT(1) NOT NULL DEFAULT 0,
  `InstagramSubscriptionId` INT(10) UNSIGNED NULL,
  `deleted` TINYINT(1) NULL DEFAULT 0 COMMENT 'if hashtag is [...]

MySQL meldt: Documentatie

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

I already found out it has something to do with this:

767 bytes is the stated prefix limitation for InnoDB tables - its 1,000 bytes long for MyISAM tables.

According to the response to this issue, you can get the key to apply by specifying a subset of the column rather than the entire amount. IE:

ALTER TABLE mytable ADD UNIQUE ( column1(15), column2(200) ); Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements that would allow you to implement the intended business rules without hitting the MySQL limitation.

I tried adding a length to my indexes, but MySQL Workbench keeps resetting them to 0. I'd like to know if there could be another cause of this problem, or another way to solve this problem.

Answer 1

I just learned a workaround... Get 5.5.14 or 5.6.3 (or later), do the SETs indicated here, and use DYNAMIC or COMPRESSED:

SET GLOBAL innodb_file_per_table = ON,
           innodb_file_format = Barracuda,
           innodb_large_prefix = ON;
CREATE TABLE so29676724 (
  `id` INT NOT NULL AUTO_INCREMENT,
  `hashtag` VARCHAR(255) NOT NULL COMMENT 'hashtag must be unique. Must be saved without #',
   PRIMARY KEY (`id`),
  UNIQUE INDEX `hashtags_hashtag` (`hashtag` ASC)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET  utf8mb4
ROW_FORMAT = COMPRESSED;

SHOW CREATE TABLE so29676724\G

mysql> CREATE TABLE so29676724 (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `hashtag` VARCHAR(255) NOT NULL COMMENT 'hashtag must be unique. Must be saved without #',
    ->    PRIMARY KEY (`id`),
    ->   UNIQUE INDEX `hashtags_hashtag` (`hashtag` ASC)
    -> )
    -> ENGINE = InnoDB
    -> DEFAULT CHARACTER SET  utf8mb4
    -> ROW_FORMAT = COMPRESSED;
Query OK, 0 rows affected (0.09 sec)

Answer 2

"Hashes" are usually hex, not UTF-8. Hashes are usually much shorter than 255.

If either applies, then...

`hashtag`
       VARCHAR(160)           -- this
       CHARACTER SET ascii    -- and/or this

would be a solution that would work on any version without any of the innodb settings indicated.

(Note: 191 is the cutoff for VARCHAR with utf8mb4, but few standard hashes need that much.)