#

UTF Hölle mit mySQL

Internationalisierung wird sicherlich ganz einfach mit Unicode und UTF, dachte ich mir, zumal mySQL ab Version 4.1 Unicode unterstützt. Und versank in einem Haufen von undokumentierten Bugs, bis ich die dicke Keule auspackte.

Das Problem begann mit einem kurzen polnischen Text. Während meine Anwendung für deutsche Sonderzeichen schon mehrfach getestet und als funktionsfähig befunden wurde, begannen die Probleme mit mir bis dato unbekannten Sonderzeichen. Der Text sollte eigentlich „Właśnie …“ heißen, zeigte sich aber in der Anwendung als „W?a?nie“.
Dabei hatte ich die Datenbank (mySQL) an mehreren Stellen auf UTF bzw. Unicode umgestellt. Jedoch waren die Tabellen und Spalten peu à peu gewachsen und ursprünglich nicht in UTF. Komischerweise wurden in einigen Fällen die Daten in der Datenbank als Unicode-Escapes gespeichert, also als „Właś“, in einem anderen Fall aber nicht.

Viel Recherchearbeit war nötig, um in der Webanwendung u. a. Übertragungsprobleme auszuschließen, zu denen es Lösungen gibt, wie

String value = new String(request.getParameter("name").getBytes("ISO-8859-1"), "UTF-8");

Der Fehler lag in meinem Falle definitiv beim Speichern. Also versuchte ich mit unzähligen Versuchen die Datenbank dazu zu überreden, die Daten korrekt zu speichern.
Auch ein mysql --default-character-set=utf8 und ein Umstellen mehrerer Characterset und Collation Attribute auf utf8 brachte nichts.

Dann entdeckte ich im Netz, dass man die Zeichenkodierung in mySQL mit zusätzlichen Parametern im Connectionstring beim Verbindungsaufbau beeinflussen kann:

useUnicode=true&characterEncoding=utf8&characterResultSets=utf8
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost/db?user=blah&password=blah&
  useUnicode=true&characterEncoding=UTF-8" );

Diese kann man in den Hibernate-Properties (hier in Spring) wie folgt einbetten:

<property name="hibernateProperties">
  <props>
    <prop key="hibernate.dialect">
        org.hibernate.dialect.MySQLInnoDBDialect
    </prop>
    <prop key="hibernate.show_sql">false</prop>
    <prop key="hibernate.hbm2ddl.auto">update</prop>
    <prop key="hibernate.cache.use_second_level_cache">true</prop>
    <prop key="hibernate.cache.provider_class">
        org.hibernate.cache.EhCacheProvider
    </prop>
    <prop key="hibernate.cache.use_query_cache">false</prop>
    <prop key="hibernate.current_session_context_class">thread</prop>
    <prop key="connection.provider_class">
        org.hibernate.connection.C3P0ConnectionProvider
    </prop>
    <prop key="hibernate.connection.useUnicode">true</prop>
    <prop key="hibernate.connection.characterEncoding">utf8</prop>
    <prop key="hibernate.connection.characterSetResults">utf8</prop>
    <prop key="hibernate.connection.clobCharacterEncoding">utf8</prop>
..

Aber auch das half bei mir nicht. Und auch Tipps wie von frank und golgote halfen nicht.

Letzten Endes entschied ich mich für einen Weg, der sicherlich nicht der Schönste ist, aber in meinem Fall schnell funktionierte:

Ich schrieb zwei Methoden, mit denen ich einen String mit Unicode-Entities de- und enkodieren kann, also aus „Właśnie“ „W&#322;a&#347;“ mache. Und dies wird dann in der Datenbank gespeichert. Somit sind alle Texte auf den ASCII-Zeichensatz reduziert. Schnell ist sie auch umgesetzt: Statt in Hibernate die Methoden get/setContent auf die entsprechende Spalte zu mappen, wird die Hilfsmethode get/setAsciifiedContent benutzt.

Sicherlich ist dies einer der „dirtiesten“ Hacks, die ich benutzt habe, aber auf die Schnelle das Problem für mehrere heterogene Systeme zu lösen ließ mir hier nicht viel mehr Wahl.

Die Nachteile liegen offen auf der Hand:
In Unicode können Zeichen mit bis zu 4 Bytes dargestellt werden. Das macht in einer Entity 4*2 Zeichen (für die Hexadezimaldarstellung) plus 4 für die Symbole „&#x;“, also bis zu 12 ASCII-Zeichen pro Sonderzeichen.
Bei Suchen auf dieses Feld können die SQL-spezifischen Hilfsmittel (Wildcards, Regex) nicht mehr so einfach benutzt werden, Suchbegriffe müssen ebenfalls vorher Kodiert werden.

Die alternative wäre die Base64-Kodierung gewesen, aber da kann man nicht „mal“ einen Blick in die Datenbank werfen.

Hier die Methoden zum de-/kodieren der Entities (alte Schule des Zustandsautomaten).

/**
 * Will decode unicode escapes to character.
 * &amp;#228; will be converted to &#228;. Also supports hex-unicode like
 * &amp;#xE4; for &amp;#228;.<br/>
 * No support for named entities.
 * 
 * @param encoded
 * @return
 */
public static final 
	String decodeUnicodeEntities(String encoded){
 
	if (encoded==null) return null;
	final StringBuilder target = 
		new StringBuilder(encoded.length());
	char c;
	final StringBuilder entityBuffer = 
		new StringBuilder(10);
	final int state_normal = 0;
	final int state_was_ampersand = 1;
	final int state_in_entity_code = 2;
	int state = state_normal; 
	for (int i=0; i<encoded.length(); 
		i++){
 
		c = encoded.charAt(i);
		switch(state){
			case state_normal:
			default:
				if (c=='&'){
					state = state_was_ampersand;
				}
				else{
					target.append(c);
				}
				break;
			case state_was_ampersand:
				if (c=='#'){
					state = state_in_entity_code;
					entityBuffer.
						delete(0, entityBuffer.length());
				}
				else{
					state  = state_normal;
					target.append('&');
				}
				break;
			case state_in_entity_code:
				if (c==';'){
					// end of entity
					int value;
					if ("x".equalsIgnoreCase(
						entityBuffer.substring(0, 1))){
						// hex
						value = Integer.parseInt(
							entityBuffer.substring(1), 16);
					}
					else{
						value = Integer.parseInt(
							entityBuffer.toString());
					}
					target.append(
						Character.toChars(value));
					state = state_normal;
				}
				else{
					// within entity
					entityBuffer.append(c);
				}
				break;
		}
	}
 
	return target.toString();
}
 
public static final String 
	encodeUnicodeEntities(String uncoded){
 
	if (uncoded==null) return null;
	char c;
	int ci;
	final StringBuilder target = 
		new StringBuilder(uncoded.length()*7);
	for (int i=0; i<uncoded.length(); i++){
		c = uncoded.charAt(i);
		ci = 0xffff & c;
		if (ci >= 160 || c=='&'){
			// Not 7 Bit use the unicode system
			target.append("&#");
			target.append(Integer.toString(ci));
			target.append(';');
		}
		else{
			// nothing special only 7 Bit
			target.append(c);
		}
	}
 
	return target.toString();
}
Tags:, , , , ,

4 Responses to “UTF Hölle mit mySQL” »»

  1. Comment by Toni | 18:47 11.01.08|X

    Ich hatte dieses Problem auch schon des öfteren. Insbesondere mit MySQL gibt es da Probleme zuhauf. Die von dir Gewählte Methode habe ich zwar auch mal in Betracht gezogen, schließlich aber verworfen. Der Grund dafür war, daß man in den Tabellen dann auch schließlich keine Suche mehr durchführen kann (naja man kann vielleicht die eingabe auch escapen, aber das gleicht mehr der Ausbreitung einer Krankheit auf das ganze Programm ;)).

    Neben den üblichen Dingen, die du auch schon probiert hast, hat folgendes bei mir auch schon öfter zum Erfolg geführt:

    * Update der Datenbank (ich benutze NUR NOCH MySQL 5.0x – 5.1 ist mir noch zu suspekt)
    * Update des Datenbanktreibers
    * Update aller relevanten Bibliotheken

    Ein Patentrezept dafür kenne ich allerdings noch nicht.

  2. Comment by Secco | 17:25 15.01.08|X

    Danke für die Tips. Sobald neue Projekte, die keinerlei Abhängigkeiten zu den alten Systemen mit mySLQ 4 haben, aufgesetzt werden, werde ich diese Ratschläge gerne beachten. Weil ich derzeit nicht daran glaube, daß wir genug Zeit haben werden, die alten Datenbanken um zu migrieren.

  3. Comment by Ralf | 12:54 18.01.08|X

    Du kannst ja auch mal einen blick auf PostgreSQL werfen 😉

  4. Comment by Volker | 2:18 22.04.08|X

    Das Problem ist, dass MySQL genau das speichert was du ihm gibst. Wenn dein Eingabestring also ISO-8859-15 Kodiert ist, dann werden die Bytes auch so abgelegt, egal ob du beim Connection String gesagt hast es soll UTF-8 verwendet werden (dann sollte der Eingabestring auch UTF-8 sein und keine ISO-8859-15 Zeichen enthalten) oder in der Tabelle UTF-8 als Default gemacht hast (das regelt nur wie die Bytes bei Zugriffen interpretiert werden).

    Wenn du immer alles ordentlich in UTF-8 reinspeicherst hast du keine Probleme mit MySQL.

Leave a Reply »»

Note: All comments are manually approved to avoid spam. So if your comment doesn't appear immediately, that's ok. Have patience, it can take some days until I have the time to approve my comments.