Submit a ticketCall us

Training ClassThe Orion® Platform Instructor-led Classes

Provided by SolarWinds® Academy, these trainings will introduce users to the Orion Platform and its features, management, and navigation. These courses are suitable for users looking to discover new tips, tricks, and ways to adapt their Orion products to better suit their monitoring needs:
Deploying the Orion Platform
Configuring Orion views, maps, and accounts
Configuring Orion alerts and reports

Reserve your seat.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > Illegal mix of collations error on the DPA trend view

Illegal mix of collations error on the DPA trend view

Updated February 8, 2018

Overview

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

 

2018-01-23_11-33-38.png

 

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

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;

 

 

 

 

Last modified

Tags

This page has no custom tags.

Classifications

Public