CODEWITHSUNDEEP
mysqldatabasevarchardatabase-table
spiderwebdev
spiderwebdev

Reputation: 95

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

I have a MySQL table and I have the need of increasing the capacity of two columns. One column resized fine with this command: 

ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME varchar(7000);

The other column returned the error message:

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

Neither column is the Primary Key.

I am at a loss as to why one column would change in size without an issue while the other throws the error

Here is the table from the "DESCRIBE TABLE_NAME"; command:

+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
|              | int(11)       | NO   | PRI | NULL    | auto_increment |
|              | int(11)       | YES  | MUL | NULL    |                |
| changed col  | varchar(7000) | YES  |     | NULL    |                |
|              | varchar(1023) | YES  |     | NULL    |                |
|need to change| varchar(255)  | YES  |     | NULL    |                |
|              | varchar(128)  | YES  |     | NULL    |                |
|              | varchar(128)  | YES  |     | NULL    |                |
|              | varchar(64)   | YES  |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+

Here is my configuration from the SHOW VARIABLES LIKE '%char%'; command:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Upvotes: 3

Views: 5676

Answers (1)

Rick James
Rick James

Reputation: 142208

Switch from ENGINE=MyISAM to InnoDB -- for a lot of reasons. The relevant reason is that the limit is 3072, not 1000 in 5.7.

In general, it is not reasonable to index long string columns.

VARCHAR(7000) says truncate to 7000 characters. You may as well simply declare it TEXT. That has a limit of 64K bytes. It, also, cannot be indexed.

In some rare situations, you can use a "prefix" index: INDEX(foo(99))

For computing sizes "3 bytes per character" is used for utf8.

You may as well go all the way to utf8mb4. (up to 4 bytes/char).

More on limits: http://mysql.rjweb.org/doc.php/limits

Upvotes: 3

Related Questions