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.

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

Issue

Environment

Resolution

Additional Information