Multiselect Picklist in Liferay Object with MariaDB in Liferay 7.4 GA132

General
1

Hi everyone,

we are using Liferay 7.4 GA132 with MariaDB 11.4.5 and utf8mb4.

We are trying to publish a custom Object called ProjectInformation that contains multiple Multiselect Picklist fields. Publishing fails with:

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.

After checking the generated database statement, we saw that Liferay creates Multiselect Picklist fields as varchar(5000). With utf8mb4, each of those fields can take up to 20,000 bytes. So after only a few Multiselect Picklists, the MariaDB/InnoDB row-size limit is reached.

What is confusing is that older Object tables still have Multiselect Picklists as varchar(280), but newly created Objects now get varchar(5000). According to the documentation, this limit was increased from 280 to 5000 in newer Liferay versions.

Our questions are:

  1. Is there a supported way to configure the size of Multiselect Picklist fields, e.g. back to 280?

  2. Is varchar(5000) expected behavior in Liferay 7.4 GA132?

  3. What is the recommended solution when many Multiselect Picklists are needed in one Object?

  4. Is there a supported workaround besides splitting the data into multiple Objects or building a custom UI?

Splitting into multiple Objects would be difficult for us, because the standard Object/Form UI displays fields from one Object directly, while relationships are shown in separate tabs. We need the information to appear together in one form.

Has anyone else run into this issue?

1 Answer

1
2

Hi @ahmadbaseet ,

Welcome to Liferay Discuss!

To quickly answer your questions no, there is currently no portal property, system setting, or configuration option to control the Multiselect Picklist column size. The size is hardcoded in the Objects framework.

-> For your reference, the change from varchar(280) to varchar(5000) was introduced in DXP 2024.Q4.2 via LPD-44387 in response to customer complaints about the 280-character limit being too restrictive.

With that said, our team has acknowledged the error:

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.

as a bug in LPD-86826, which changes the Multiselect Picklist column type from VARCHAR(5000) to TEXT (CLOB). TEXT/CLOB columns are not counted toward the InnoDB row-size limit because they are stored off-page, which completely eliminates the row-size constraint. This fix is available starting from 7.4.13 DXP U149/ 2026.Q2.0.

After upgrading to a version with the fix you might need a groovy script to convert any existing object definitions. I attached a sample groovy script you could modify to fit your use case. I recommend reindexing after running the groovy script.

import com.liferay.object.constants.ObjectFieldConstants
import com.liferay.portal.kernel.dao.db.DB
import com.liferay.portal.kernel.dao.db.DBManagerUtil
import com.liferay.portal.kernel.dao.jdbc.DataAccess
import com.liferay.portal.kernel.security.auth.CompanyThreadLocal
import com.liferay.portal.kernel.workflow.WorkflowConstants

import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet

long companyId = 42637

String schema = "lpartition_${companyId}"

long originalCompanyId = CompanyThreadLocal.getCompanyId()

try {
	CompanyThreadLocal.setCompanyId(companyId)

	println "Starting Multiselect Picklist migration from VARCHAR to TEXT for companyId ${companyId} (schema ${schema})..."

	DB db = DBManagerUtil.getDB()

	println "Database dialect: " + db.getDBType()

	println "Updating ObjectField.dbType from '" +
		ObjectFieldConstants.DB_TYPE_STRING + "' to '" +
		ObjectFieldConstants.DB_TYPE_CLOB +
		"' for all Multiselect Picklist fields on companyId ${companyId}..."

	db.runSQL(
		"update ObjectField set dbType = '" +
			ObjectFieldConstants.DB_TYPE_CLOB + "' where businessType = '" +
			ObjectFieldConstants.BUSINESS_TYPE_MULTISELECT_PICKLIST +
			"' and dbType = '" + ObjectFieldConstants.DB_TYPE_STRING + "'")

	println "ObjectField.dbType update completed."

	int inspectedFieldsCount = 0
	int migratedFieldsCount = 0
	int failedFieldsCount = 0

	Connection connection = DataAccess.getConnection()

	try {
		PreparedStatement preparedStatement = connection.prepareStatement(
			"select ObjectField.dbColumnName, ObjectField.dbTableName, " +
				"ObjectField.name as objectFieldName, " +
				"ObjectField.objectFieldId, " +
				"ObjectDefinition.name as objectDefinitionName " +
				"from ObjectField " +
				"inner join ObjectDefinition on " +
				"ObjectDefinition.objectDefinitionId = " +
				"ObjectField.objectDefinitionId " +
				"where ObjectDefinition.status = ? and " +
				"ObjectField.businessType = ?")

		try {
			preparedStatement.setInt(1, WorkflowConstants.STATUS_APPROVED)
			preparedStatement.setString(
				2, ObjectFieldConstants.BUSINESS_TYPE_MULTISELECT_PICKLIST)

			ResultSet resultSet = preparedStatement.executeQuery()

			try {
				while (resultSet.next()) {
					inspectedFieldsCount++

					String dbColumnName = resultSet.getString("dbColumnName")
					String dbTableName = resultSet.getString("dbTableName")
					String objectFieldName = resultSet.getString(
						"objectFieldName")
					long objectFieldId = resultSet.getLong("objectFieldId")
					String objectDefinitionName = resultSet.getString(
						"objectDefinitionName")

					println "Processing Multiselect Picklist field '" +
						objectFieldName + "' (ID=" + objectFieldId +
						") on Object definition '" + objectDefinitionName +
						"'..."

					println "  Altering column '" + dbColumnName +
						"' on table '" + dbTableName + "' to TEXT..."

					try {
						db.runSQLTemplate(
							"alter_column_type " + dbTableName + " " +
								dbColumnName + " TEXT null",
							true)

						migratedFieldsCount++
					}
					catch (Exception exception) {
						failedFieldsCount++

						println "[ERROR] Failed to alter column '" +
							dbColumnName + "' on table '" + dbTableName +
							"': " + exception.getMessage()

						exception.printStackTrace()
					}
				}
			}
			finally {
				resultSet.close()
			}
		}
		finally {
			preparedStatement.close()
		}
	}
	finally {
		DataAccess.cleanUp(connection)
	}

	println "Migration finished for companyId ${companyId}."
	println "Summary:"
	println "  Multiselect Picklist fields inspected : " + inspectedFieldsCount
	println "  Columns migrated                      : " + migratedFieldsCount
	println "  Failed columns                        : " + failedFieldsCount

	if (failedFieldsCount > 0) {
		println "[ERROR] Migration completed WITH ERRORS. " +
			"Review the log above for the failing columns."
	}
	else {
		println "Migration completed successfully with no errors."
	}
}
finally {
	CompanyThreadLocal.setCompanyId(originalCompanyId)
}

Hope this helps!

Hi, thanks a lot for the solution.

 –