MySQL error: key specification without a key length

I got this error when adding an index to a table with text type columns. You need to declare the size amount you want to use for each text type.

Put the size amount within the parenthesis ( )

If too many bytes are used you can declare a size in the brackets for varchar to decrease the amount used for indexing. This is even if you declared a size for a type already like varchar(1000). You don't need to create a new table like others have said.

Adding index

alter table test add index index_name(col1(255),col2(255));

Adding unique index

alter table test add unique index_name(col1(255),col2(255));

I know it's quite late, but removing the Unique Key Constraint solved the problem. I didn't use the TEXT or LONGTEXT column as PK , but I was trying to make it unique. I got the 1170 error, but when I removed UK, the error was removed too.

I don't fully understand why.

The solution to the problem is that in your CREATE TABLE statement, you may add the constraint UNIQUE ( problemtextfield(300) ) after the column create definitions to specify a key length of 300 characters for a TEXT field, for example. Then the first 300 characters of the problemtextfield TEXT field would need to be unique, and any differences after that would be disregarded.

Nobody mentioned it so far... with utf8mb4 which is 4-byte and can also store emoticons (we should never more use 3-byte utf8) and we can avoid errors like Incorrect string value: \xF0\x9F\x98\... we should not use typical VARCHAR(255) but rather VARCHAR(191) because in case utf8mb4 and VARCHAR(255) same part of data are stored off-page and you can not create index for column VARCHAR(255) but for VARCHAR(191) you can. It is because the maximum indexed column size is 767 bytes for ROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT.

For newer row formats ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED (which requires newer file format innodb_file_format=Barracuda not older Antelope) maximum indexed column size is 3072. It is available since MySQL >= 5.6.3 when innodb_large_prefix=1 (disabled by default for MySQL <= 5.7.6 and enabled by default for MySQL >= 5.7.7). So in this case we can use VARCHAR(768) for utf8mb4 (or VARCHAR(1024) for old utf8) for indexed column. Option innodb_large_prefix is deprecated since 5.7.7 because its behavior is built-in MySQL 8 (in this version is option removed).

In case your data type is TEXT - you will have to change it to VARCHAR
solution 1: Query

ALTER TABLE table_name MODIFY COLUMN col_name datatype;
ALTER TABLE my_table MODIFY COLUMN my_col VARCHAR(255);

solution 2: GUI (MySQL workbench)
step1 - write in the text box

step2 - edit data type, apply

I used to use this one with the same error you mentioned:

CREATE INDEX idx_col1 ON my_table (col1);

Then I replaced it with this one and solved:

CREATE INDEX idx_col1 ON my_table (col1(255));

You have to change column type to varchar or integer for indexing.

Go to mysql edit table-> change column type to varchar(45).

DROP that table and again run Spring Project. That might help. Sometime you are overriding foreignKey.

Use like this

@Id
@Column(name = "userEmailId", length=100)
private String userEmailId;