java.sql.SQLException: Incorrect string value and no Display Templates listed for the Breadcrumb widget

Issue

• On startup, the server will print errors similar to these:
  

  ERROR [main][JDBCExceptionReporter:234] Incorrect string value: '\xE5\x9F\xBA\xE6\x9C\xAC...' for column 'name' at row 1
  WARN [main][PortalInstanceLifecycleListenerManagerImpl:140] Unable to register portal instance {mvccVersion=1, companyId=20099, accountId=20101, webId=liferay.com, key=redacted, mx=liferay.com, homeURL=, logoId=0, system=false, maxUsers=0, active=true}
  org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
  [...]
  Caused by: java.sql.SQLException: Incorrect string value: '\xE7\xA4\xBE\xE5\x8C\xBA...' for column 'name' at row 1
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
   at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)
   ... 288 more

• When wanting to select a Display Template for the Breadcrumb widget, no options will be listed:
  

Environment

• Liferay DXP 7.1

Resolution

• Root cause: Either the database schema, tables or both haven't got character set utf8 and collate utf8_general_ci

• Contact your Database Admin Team (DBA) to perform the changes. The requirement from Liferay is that the schema and all tables need to have character set utf8 and collate utf8_general_ci. Below some simplified steps, provided only as a proof-of-concept and whose usage is not recommended:
  
  1) Stop the server and perform a full backup of your database and perform a test always on non-production first
  2) Make sure the schema is properly set:

  ALTER DATABASE lportal CHARACTER SET utf8 COLLATE utf8_general_ci;

  3) Gather all table names and prepare the DB modification commands:
  

  SELECT CONCAT('ALTER TABLE ', tbl.TABLE_SCHEMA, '.', tbl.TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = 'lportal';

  4) The query will generate a list of commands you can execute in the DB
  5) Once done, perform the usual cleanup routine:

  - Delete the content of the following folders: liferay/work, Liferay/osgi/state, Tomcat/work and Tomcat/temp
  - Restart the server (the java.sql.SQLException should be gone)
  - Clear the browser's cache or test in Incognito mode, Breadcrumbs Display Templates should be listed now.

Additional Information

• https://help.liferay.com/hc/en-us/articles/360018183071-Liferay-s-database
• https://help.liferay.com/hc/en-us/articles/360018421111-Setting-Up-and-Configuring-MySQL-Database-With-Liferay-Portal