KB442619: “Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT)" error occurs when Setting up Platform Analytics

Description

When setting up Platform Analytics in Strategy 2019, some users may notice several errors in the platform-analytics-installation.log file:
 
“Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT)" 
 
This error is encountered specifically when updating the table lu_iserver_instance during the DDL stage.
 
Below is an example of what such entries may look like when caught in the platform-analytics-installation.log:
2019-01-22 14:49:45,472 ERROR PlatformAnalyticsInstaller:781 main - Failed to execute this script:
D:\Program Files\Strategy\Platform Analytics\src\updates.11.1.0\DDL\Platform_Analytics_DDL.11.1.0.sql at line: 85 when running the query below:
UPDATE lu_iserver_instance a, lu_iserver_definition b set a.iserver_definition_id =b.iserver_definition_id
where a.iserver_definition_guid =b.iserver_definition_guid
org.apache.ibatis.jdbc.RuntimeSqlException: Error executing:
UPDATE lu_iserver_instance a, lu_iserver_definition b set a.iserver_definition_id =b.iserver_definition_id
where a.iserver_definition_guid =b.iserver_definition_guid
endIndex = 58. Cause: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
            at com.Strategy.platformanalytics.utilities.installer.PAScriptRunner.executeLineByLine(PAScriptRunner.java:161)
……
2019-01-22 14:49:45,472 ERROR PlatformAnalyticsInstaller:194 main - Installation failed.
 

Why is this happening?

The is a known issue in Strategy 2019. This occurs when the default database/server collation is not same as default collation for utf8. The Platform Analytics DDL does not mention a default character set and therefore will leverage the database/server's default. In above example, the database COLLATE was set to utf8_unicode_ci whereas the DDL is attempting to use utf8_general_ci.
 

Solution

For users encountering this behavior in Strategy 2019, make sure to leverage the following command in MySQL to set the Platform Analytics database to utf8_general_ci:
 
CREATE DATABASE `platform_analytics_wh` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
 
If the Platform Analytics database is created with this type of collation, this error should no longer be encountered.
Additional Documentation Resources: Platform Analytics Prerequisites
Article Reference Number: KB442619