I18N: DDL that uses ascii for entity_ref stops events with utf-8 chars from being processed.

Description

There are several DDL statements in mysql.properties that create dashboard columns entity_ref as varchar (265) character set ascii.

This breaks dashboard events when trying to join on tables containing real international utf-8 characters (like "ü" and "ä" and so on):

2013-02-15 09:44:14,051 WARN Dashboard Event Processing Thread org.sakaiproject.dash.dao.impl.DashboardDaoImpl - getNewsItem: Error executing query: class org.springframework.jdbc.UncategorizedSQLException:PreparedStatementCallback; uncategorized SQLException for SQL [select ni.id as ni_id, ni.news_time as ni_news_time, ni.title as ni_title, ni.news_time_label_key as ni_news_time_label_key, ni.entity_ref as ni_entity_ref, ni.subtype as ni_subtype, st.id as type_id, st.identifier as type_identifier, site.id as site_id, site.context_id as site_context_id, site.context_url as site_context_url, site.context_title as site_context_title from dash_news_item ni join dash_context site on site.id=ni.context_id join dash_sourcetype st on st.id=ni.entity_type where ni.entity_ref=?]; SQL state [HY000]; error code [1267]; Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Changing the statements entity_ref columns to varchar(255) and character set utf-8 fixes the problem.

Environment

MySQL

Attachments

1

Activity

Show:

Wolfgang Wolfgang June 11, 2014 at 10:17 AM

We have the patched version in production since February.

Jean-François Lévêque June 11, 2014 at 3:25 AM

Is the fix correct, Wolfgang?

Jean-François Lévêque June 17, 2013 at 2:29 AM

Guessing the Fix Version

Zhen Qian June 14, 2013 at 2:14 PM

r83976.

The patch is checked in for MySQL. entity-reference field is changed from 265 to 255 for hsql and oracle scripts, too.

Jean-François Lévêque February 19, 2013 at 1:38 AM

This is more important than I thought first.

Fixed

Details

Assignee

Reporter

Conversion Script Required

Yes

Components

Fix versions

Affects versions

Priority

Created February 16, 2013 at 2:20 AM
Updated June 11, 2014 at 10:17 AM
Resolved June 14, 2013 at 2:14 PM