java.sql.SQLException: Illegal mix of collations for operation 'UNION'

NOTE: Be sure to take a backup of your database prior to making any changes. Always test the changes in a lower environment before making changes in production.

Making manual changes to the database is not recommended, and should be carefully assessed based on your own business needs and risk factors. Any such operation is performed at the sole discretion of your own team.

Issue

• Users are unable to create Fragments since the add button is not visible and the following error is displayed in the Liferay logs:
  

  ERROR [http-nio-8080-exec-16][IncludeTag:128] Current URL /group/india-site/~/
  control_panel/manage/-/fragments /fragment_collections?p_p_auth=b8knNJrv generates 
  exception:com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.
  kernel.dao.orm.ORMException:org.hibernate.exception.GenericJDBCException: could not 
  execute query java.sql.SQLException: Illegal mix of collations for operation 'UNION'

Environment

• Liferay DXP [7.1-7.4]
• MySQL

Resolution

• By default, the character set and collation used for the table are included in the mysqldump.
• As a result, the table will end up being created using the character set that it used in the original database when the user imports a database backup prepared with mysqldump, ignoring the default character set and collation settings in the current database.
• If users run a Liferay upgrade that creates tables, the new database tables will use the default settings for the current database. This will result in inconsistencies over time.

Additional Information

• Run the following sequence of queries, but lportal set it to whatever the actual schema name is to check whether there is an issue with collations or not.

  SET @liferay_schema_name = 'lportal';
  
  SELECT schema_name, default_collation_name
  FROM information_schema.schemata
  WHERE default_collation_name != 'utf8mb4_unicode_ci' and schema_name = @liferay_schema_name;
  
  SELECT table_schema, table_name, table_collation
  FROM information_schema.tables
  WHERE table_collation != 'utf8mb4_unicode_ci' and table_schema = @liferay_schema_name;
  
  SELECT table_schema, table_name, column_name, collation_name
  FROM information_schema.columns
  WHERE collation_name != 'utf8mb4_unicode_ci' and table_schema = @liferay_schema_name;

• MySQL :: MySQL 8.0 Reference Manual :: 6.5.4 mysqldump — A Database Backup Program
• Please note that Liferay support is limited in assisting with any architectural assistance, such as database configuration since it mainly comes under the scope of global services that can assist users further with this.