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.
Improve this question
8
  • Have you tried accepted answer at: dba.stackexchange.com/questions/231219/… ?
    – Lennart - Slava Ukraini
    Commented Jan 20, 2021 at 15:11
  • 1
    Updated the question, thanks for the attempt!
    – meh
    Commented Jan 21, 2021 at 8: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
    Commented Jan 21, 2021 at 8:28
  • Sure, it's done.
    – meh
    Commented Jan 21, 2021 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: dbfiddle.uk/… and it seems to work. If it is during insert, provide the failing insert
    – Lennart - Slava Ukraini
    Commented Jan 21, 2021 at 10:16

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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
    Commented Feb 1, 2021 at 8: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
    Commented Feb 1, 2021 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.