1

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.
meh
  • 23
  • 1
  • 5
  • Have you tried accepted answer at: https://dba.stackexchange.com/questions/231219/mariadb-10-1-38-specified-key-was-too-long-max-key-length-is-767-bytes ? – Lennart - Slava Ukraini Jan 20 '21 at 15:11
  • 1
    Updated the question, thanks for the attempt! – meh Jan 21 '21 at 08:21
  • Can you add a create table statement for the table (including the key) where it fails. Just the columns for the failing key will be sufficient. – Lennart - Slava Ukraini Jan 21 '21 at 08:28
  • Sure, it's done. – meh Jan 21 '21 at 10:10
  • I may have mis-read your question, is it during the creation of the table or during insert you get the error? Itried your table at: https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=a1d34b89e19d73321a9ad1a59864a495 and it seems to work. If it is during insert, provide the failing insert – Lennart - Slava Ukraini Jan 21 '21 at 10:16
  • As much as I can be sure without digging into the code itself it is the above statement, the CREATE TABLE, that triggers the error. I would assume that given the charset and collation my mariadb is using 4 bytes per character while dbfiddle is not. – meh Jan 21 '21 at 10:24
  • I created the table with utf8: https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=c68940a22b4322414bc3f821b773db96 . You need to provide enough information to recreate the error. – Lennart - Slava Ukraini Jan 21 '21 at 10:50
  • Thank you very much for your help @Lennart, I have made progress but the problem has shifted to a later query. – meh Jan 21 '21 at 11:57

1 Answers1

1

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.

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • You were right, most tables were created as innodb (probably because they were declared with constraints) so I missed that detail. Also, not my application, so I'd rather not mess with field length. – meh Feb 01 '21 at 08:08
  • The engine comes from either explicitly declaring it when the table is created, or from a default in the config. Constraints, FKs, etc are simply ignored by MyISAM. I mentioned the 255 because it factors into the 1000 limit, and you could still hit a different limit in InnoDB. Do something like SELECT MAX(CHAR_LENGTH(col)) FROM tbl; to see what the longest text for col. Then use ALTER to adjust it down to some max that is somewhere between the max and 255. For example, hardly any kind of "name" (person, city, etc) is longer than 70. – Rick James Feb 01 '21 at 16:51