You are currently reviewing an older revision of this page.

KB-1793 "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. 

Action

Appian 18.2 and Earlier

Revert the MySQL connector version to 5.1.46.

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