MariaDB Error 1071 – Specified Key Too Long; Max Key Length is 1000 Bytes

innodbmariadb

A bit of context: I am trying out flowable, however tomcat chokes at a certain point while populating the database, the usual exception wall of text boils down to the title of this post. First time I put my hands on tomcat in a long time, I may be missing the obvious.

Update: this query now works.

CREATE TABLE flowable.FLW_EVENT_DEPLOYMENT (
ID_ VARCHAR(255) NOT NULL,
NAME_ VARCHAR(255) NULL,
CATEGORY_ VARCHAR(255) NULL,
DEPLOY_TIME_ datetime(3) NULL,
TENANT_ID_ VARCHAR(255) NULL,
PARENT_DEPLOYMENT_ID_ VARCHAR(255) NULL,
CONSTRAINT PK_FLW_EVENT_DEPLOYMENT PRIMARY KEY (ID_)
)

What I did: I switched from utf8mb4 to plain old utf8 thanks to this bit "I know UTF8mb4 can only support up to VARCHAR(191)" (thanks @Lennart for making me think harder about this). So there is progress but I have the same error later on another query:

CREATE UNIQUE INDEX ACT_IDX_EVENT_DEF_UNIQ ON flowable.FLW_EVENT_DEFINITION(KEY_, VERSION_, TENANT_ID_)

Those three columns are respectively varchar(255) utf8_general_ci, int(11), varchar(255) utf8_general_ci.

tomcat 8.5.61

mariadb 10.4.17

I found quite a number of suggested solutions, but none seem to apply to my mariadb version, innodb-large-prefix=ON in particular has been deprecated, tried a few different collations without success. I have spent quite a bit of time on this already, tried to swap mariadb connector for mysql connector too to rule out a bug in the library itself.

For example I tried the accepted solution here to no avail, probably because:

  • innodb_file_format is deprecated and has no effect. It may be removed in future releases.
  • innodb_file_format_max surprisingly causes an error on config check, Antelope is deprecated anyway and mariadb wouldn't use it for a new table
  • innodb_large_prefix was removed in 10.3.1 and restored as a deprecated and unused variable in 10.4.3 for compatibility purposes.

Best Answer

Use ENGINE=InnoDB, not MyISAM. Your attempts at changing settings were useless because you are not using InnoDB for the table in question.

That can be specified as the default in my.cnf before creating tables. Else do it explicitly on CREATE TABLE.

Also, don't blindly use 255, instead use sensible limits.

Related Question