Applications Systems

Illegal mix of collations error on the DPA trend view

When you click on the trend view of DPA, the following error is returned and the charts are not rendered. ERROR (2018-01-22 18:09:31,834.PST) [http-nio-8123-exec-8] SqlPropsDialogController - Unable to get the SQL properties: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select 'NAME', NAME, DESCR, FLAG from CON_SQL_NAME where HASH=? union all select 'ID', SQLID, null, null from CON_ORASQLID_1 where HASH=?]; SQL state [HY000

First published date

11/12/2018 7:12 PM

Last published date

11/12/2018 7:12 PM

Overview

When you click on the trend view of DPA, the following error is returned and the charts are not rendered.

Unable to get SQL information: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select 'NAME', NAME, DESCR, FLAG from CON_SQL_NAME where HASH=? union all select 'ID', SQLID, null, null from CON_ORASQLID_7 where HASH=?]; SQL state [HY000]; error code [1271]; Illegal mix of collations for operation 'UNION'; nested exception is java.sql.SQLException: Illegal mix of collations for operation 'UNION' Caused By: java.sql.SQLException: Illegal mix of collations for operation 'UNION'
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select 'NAME', NAME, DESCR, FLAG from CON_SQL_NAME where HASH=? union all select 'ID', SQLID, null, null from CON_ORASQLID_7 where HASH=?]; SQL state [HY000]; error code [1271]; Illegal mix of collations for operation 'UNION'; nested exception is java.sql.SQLException: Illegal mix of collations for operation 'UNION'
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)


Environment 

  • MySQL repository 
  • DPA versions before 10.2, or later versions if a schema change didn't get applied
  • Monitoring only Oracle instances

Product section

Database Performance Analyzer

Cause

Initial repository tables are being defined with COLLATE but no CHARACTER_SET for DPA on Windows in early versions of the MySQL repository creation. 

 

Repository table creation scripts were being run on Windows as non-multi-byte but monitored database scripts were being run on Windows as multi-byte. This means that repository creation scripts were getting the NVARCHAR types changed to VARCHAR before running while the monitored database creation scripts were not. Unfortunately, due to a MySQL Bug (#77265) MySQL creates the tables using different character set and collation depending on whether it is a VARCHAR vs NVARCHAR.

 

MySQL Bug #77265 Collation not being set correctly
https://bugs.mysql.com/bug.php?id=77265

(© 2018 Oracle, available at https://bugs.mysql.com, obtained on February 8, 2018)

Resolution

This in most cases, this issue is resolved in the upgrade scripts in DPA version 10.2. 

If you encounter this error in DPA 10.2 or later, then run the following script to resolve the issue. 

DPA has several tables that might be at issue the table CON_ORA_SQLID_X tables where X is the ID of the server in the COND table for any Oracle instance. One line like the following examples needs to be run for all tables that have the schema incorrect.

Example: 

ALTER TABLE CON_ORASQLID_1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE CON_ORASQLID_2 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE CON_ORASQLID_3 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE CON_ORASQLID_4 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE CON_ORASQLID_5 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;