SQL creating a Database throws 'MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes'

Question

when i try to create a database with the following statement

            c.prepareStatement("CREATE TABLE IF NOT EXISTS `verify` (" +
                    "`id` INT NOT NULL AUTO_INCREMENT UNIQUE," +
                    "`uuid` VARCHAR(255) NOT NULL UNIQUE," +
                    "`ts` INT NOT NULL UNIQUE," +
                    "PRIMARY KEY (`id`)" +
                    ");").execute();

i get this Error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

https://stackoverflow.com/questions/13397038/uuid-max-character-length — jarlh, Nov 11 '20 at 19:15

score 1 · Answer 1 · answered Nov 11 '20 at 19:05

You want the uuid to be UNIQUE but for string columns:

indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length

and

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables that use the REDUNDANT or COMPACT row format. The prefix length limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. For MyISAM tables, the prefix length limit is 1000 bytes.

So, you can create your table without this index and then to set the index to get the subset of your column:

ALTER TABLE `verify` ADD UNIQUE ( uuid(191) );

as 767 / 4 ~= 191.

SQL creating a Database throws 'MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes'

1 Answers1