Specified key was too long; max key length is 1000 bytes in mysql 5.6

Question

one of the application server is internally creating the database on my mysql but when ever the following create table command gets executed

 CREATE TABLE ofRoster (   

    rosterID              BIGINT          NOT NULL,   
    username              VARCHAR(64)     NOT NULL,   
    jid                   VARCHAR(1024)   NOT NULL,   
    sub                   TINYINT         NOT NULL,   

    ask                   TINYINT         NOT NULL,   
    recv                  TINYINT         NOT NULL,   
    nick                  VARCHAR(255),   
    PRIMARY KEY (rosterID),   
    INDEX ofRoster_unameid_idx (username),   
    INDEX ofRoster_jid_idx (jid) 

 )

i am getting following error

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

i have set my default engine to MyISAM because i was getting following error in InnoDB

specified key was too long max key length is 767 bytes

my current engines are as follows

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

now i really don't know how would i get rid off it as the application server itself is creating database automatically in mysql so i dont have control over it.

i am using Server version: 5.6.10 MySQL Community Server (GPL) version

character set of column

+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | latin1                                                 |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | latin1                                                 |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.6.10-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+

Answer 1

You can increase the maximum InnoDB index prefix size in MySQL 5.6 to 3072 bytes by setting innodb_large_prefix to ON along with other settings that you'll also need in order to enable that one, discussed here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

These changes should allow these indexes to be valid for InnoDB tables.

With a character set of (I assume) utf8, a VARCHAR(1024) would need 1024 x 3 = 3072 bytes for its index.

---

Updates:

I incorrectly showed the value of the variable as Yes when it should have been ON.

But also:

I think it needs 3072+2 – ypercube

When I thought about this, it sounded correct, because 1 byte is needed to store the size of the value when the value is 255 bytes or less, and 2 bytes are needed otherwise.

However, testing reveals that this isn't the case, in this case -- InnoDB using the COMPRESSED row format from Barracuda can actually index the full size of a VARCHAR(1024)... so either they've documented it strangely or the COMPRESSED row format stores the length out-of-band along with another block of metadata, so it doesn't count in the total bytes in this format.

COMPRESSED doesn't truncate the index into a prefix index and throw a warning until you go to VARCHAR(1025).

+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 3072 bytes |
+-------+------+----------------------------------------------------------+

It's nice the way it throws a warning instead of throwing an error, but that doesn't help us here because this still requires the explicit ROW_FORMAT declaration to trigger this behavior.

So, my initial answer is still wrong, because you have to explicitly add ROW_FORMAT=COMPRESSED to the end of the table definition. Otherwise you still get the same old error.

Actually, you get two different errors in 5.6.10. If you try to create a table with a fully-indexed VARCHAR(1024) you get this:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

...but if you try with a fully-indexed VARCHAR(1025) you get this:

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

That's sloppy code but the bottom line is that my answer doesn't actually fix this problem.

I don't see a way to use ROW_FORMAT=COMPRESSED by default, nor does it seem like a good idea if it were possible, and I'm inclined to suggest that the answer is...

...there's not a readily available workaround here. My other thought was "character sets" but the difference between latin1 and utf8 still isn't sufficient to explain 1,024 vs 1000 or 767. I'll happily get behind a better idea but at the moment, I can't think of a version of MySQL Server that this code would work properly on.

Answer 2

For mariadb, update your *my.cnf file with following configuration,

innodb_default_row_format = dynamic
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_large_prefix=true

Then, you have to restart mariadb service for updated configuration to take effect.

Answer 3

Michael - sqlbot is correct that there seems no setting to set ROW_FORMAT=COMPRESSED automatically however one can ALTER TABLE then add required indexes (MySQL 5.6 of course). Here's the sample for Laravel, but the idea is similar to any language / framework used:

    Schema::create('triplet', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->bigInteger('owner_id')->default(0);
        $table->string('name', 1000);
        $table->string('type', 8);
        $table->mediumText('value')->nullable();
        // indexes
        $table->index('owner_id');
        // $table->index('name');
        $table->index('type');
        // $table->unique(['owner_id', 'type', 'name']);
    });
    DB::statement('alter table `triplet` ROW_FORMAT=COMPRESSED');
    DB::statement('alter table `triplet` add index triplet_name_index(`name`)');
    DB::statement('alter table `triplet` add UNIQUE KEY `triplet_owner_id_type_name_unique` (`owner_id`,`type`,`name`)');

Without 'alter table triplet ROW_FORMAT=COMPRESSED' it was throwing errors even with MySQL 5.6 my.cnf

innodb_large_prefix = on
innodb_file_format=barracuda
innodb_file_per_table=true

When importing SQL dump one has obviousely pre-process it to use ROW_FORMAT=COMPRESSED implicitely.

Answer 4

I have the same problem. You trying setup Openfire (in my case to Amazon)... My solving of this problem is:

1. edit database schema from: */opt/openfire/resources/database/openfire_mysql.sql*
   • find: CREATE TABLE ofRoster(... (line 57)
   • after the closing parenthesis add: ) ROW_FORMAT=DYNAMIC;
2. create database: create database 'name_of_your_database';
3. apply edited schema to your database: cat /tmp/openfire_mysql.sql | mysql -P 3306 -h your_db_host -u your_user -p name_of_your_database
   • or just input in your browser: openfire_host:9090 and follow for the recomended steps (if you directly changed */opt/openfire/resources/database/openfire_mysql.sql*)

Answer 5

Use InnoDB 5.6+ and MariaDB 10.0+

set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;

then you can

CREATE TABLE `tab1` (
 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `fullname` VARCHAR(1700) DEFAULT NULL,
 PRIMARY KEY (`id_client`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

 ALTER TABLE `tab1` ADD INDEX `fullname` (`fullname`(1700));

Answer 6

Try using partial index

ALTER TABLE ofRoster ADD INDEX (jid(32));

You would want to investigate the size of the index, the improvement, and then the amount of additional reads necessary.