升级Orbeon Forms 2017.2 CE至2019.1 CE遇MariaDB索引长度错误求助

Got it, let's tackle this index length issue you're hitting—your hunch about utf8mb4 and multi-column VARCHAR(255) indexes is completely correct. MariaDB 10.1's InnoDB engine has a default maximum index key length of 767 bytes, and since utf8mb4 uses 4 bytes per character, a single VARCHAR(255) field already takes up 1020 bytes on its own. Combine multiple such fields in an index, and you hit the limit immediately. Here are actionable fixes you can try:

1. Shorten the Length of Affected VARCHAR Fields #

• Locate the upgrade script statement that's creating the problematic index. Identify which VARCHAR(255) fields are part of it.
• Modify those fields to use VARCHAR(191) instead. Since 191 * 4 = 764 bytes, this stays just under the 767 byte limit even for multi-column indexes (as long as the total sum of their adjusted lengths doesn't exceed 767).
• For example, if the failing index is:

  CREATE INDEX idx_form_data_app_form_created ON orbeon_form_data (app, form, created);
  

  First alter the fields:

  ALTER TABLE orbeon_form_data MODIFY COLUMN app VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  ALTER TABLE orbeon_form_data MODIFY COLUMN form VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

  Then run the index creation statement again.
• Note: This works because Orbeon's application and form names rarely exceed 191 characters in practice, so it won't break your existing workflow.

2. Enable Large Index Prefixes in MariaDB #

MariaDB 10.1.2 and later support increasing the index key length limit to 3072 bytes for tables using the Barracuda file format. Here's how to set it up:

• Edit your MariaDB configuration file (usually my.cnf on Linux or my.ini on Windows) and add these lines:

  innodb_large_prefix = 1
  innodb_file_format = Barracuda
  innodb_file_per_table = 1
  

• Restart your MariaDB service to apply the changes.
• Convert the affected tables to use the DYNAMIC row format (required for large prefixes):

  ALTER TABLE orbeon_form_data ROW_FORMAT=DYNAMIC;
  

• Now run the Orbeon upgrade script again—this should allow the multi-column VARCHAR(255) index to be created without hitting the length limit.

3. Create a Prefix Index #

If you can't modify field lengths or database configuration, you can create an index that only uses the first N characters of each VARCHAR field:

• Instead of indexing the full field, specify a prefix length for each VARCHAR column in the index. For example:

  CREATE INDEX idx_form_data_app_form_created ON orbeon_form_data (app(191), form(191), created);
  

• This keeps the total index key length under 767 bytes while still maintaining most of the index's utility. The tradeoff is slightly reduced query efficiency if the first 191 characters of your app/form names are highly repetitive, but this is rarely an issue for Orbeon use cases.

4. Verify Orbeon's Upgrade Documentation #

While the above fixes should resolve the issue, it's worth double-checking the official Orbeon 2019.1 CE upgrade notes for any MariaDB-specific workarounds. Sometimes the team provides tailored adjustments for common database compatibility issues.

One critical reminder: always back up your database before making any schema changes or running upgrade scripts—it's easy to roll back if something goes wrong!

内容的提问来源于stack exchange，提问作者George Papageorghiou