KB-1770 "java.sql.SQLException: Illegal mix of collations for operation '[operator_name]'" causes error while loading records and tasks

Symptoms

Users are unable to open records and tasks and the following error is seen in the application server log: 

Expression evaluation error in rule '<view_name>' (called by rule '<rule_name>') at function a!queryEntity [line 5]: An error occurred while retrieving the data. (APNX-1-4198-000)
...
Caused by: java.sql.SQLException: Illegal mix of collations for operation '<operator_name>'

For JBoss, server.log is located in the <JBOSS_HOME>/standalone/log directory. For Tomcat, tomcat-stdOut.log file is located in the <APPIAN_HOME>/log directory.  

Cause

This error is witnessed when the MySQL connector version is changed to 5.1.47. This driver uses a different collation and characterEncoding setting than MySQL connector versions 5.1.46 and lower. 

This issue has been addressed via AN-117844 in the following hotfixes/versions:

• Appian 18.2 Hotfix Package G
• Appian 18.1 Hotfix Package I

Action

Appian 18.2 and Earlier

Revert the MySQL connector version to 5.1.46 or apply the latest hotfix to the environment.

Appian 18.3

1. Modify the suite.xml file located in the <APPIAN_HOME>/tomcat/apache-tomcat/conf/Catalina/localhost/ directory to support this collation by making the following changes to the connectionProperties field:

   connectionProperties='useUnicode=true;characterEncoding=UTF-8;connectionCollation=utf8mb4_unicode_ci'

2. Restart the application server.

Affected Versions

This article applies to Appian 18.1, 18.2 and 18.3.

Last Reviewed: November 2018