SQLException: Index column size too large. The maximum column size is 767 bytes.

Issue

• I used a copy of Production database (MySQL) to test upgrading from Liferay 7.4 U62 to U90. The upgrade was failed with the following error:
  

  INFO  [main][UpgradeProcess:118] Upgrading com.liferay.portal.configuration.persistence.internal.upgrade.registry.ConfigurationPersistenceUpgradeStepRegistrator - Modifying table Configuration_ to alter the type of the column configurationId to VARCHAR(512) not null
  INFO  [main][UpgradeProcess:136] Failed upgrade process com.liferay.portal.configuration.persistence.internal.upgrade.registry.ConfigurationPersistenceUpgradeStepRegistrator - Modifying table Configuration_ to alter the type of the column configurationId to VARCHAR(512) not null in 348 ms          
  ERROR [main][UpgradeStepRegistratorTracker:178] Failed upgrade process for module com.liferay.portal.configuration.persistence.impl
  com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLException: Index column size too large. The maximum column size is 767 bytes.

 

Environment

• Liferay DXP 7.4

 

Resolution

• This error happens when creating an index on the column. Usually it is caused by different charset/collation set in the database. For example, the Production database is set utf8mb4, and local database is set utf8. In MySQL, utf8 takes up to 3 bytes and utf8mb4 up to 4 bytes. Thus creating an index with utf8mb4 in local database will fail.
• Please make sure the charset/collation is set the same in your local environment when using a copy of the database from another environment.