List:Replication« Previous MessageNext Message »
From:Christoph Lechner Date:November 15 2007 12:13am
Subject:Replication breaks with error 1406 when confronted with Umlauts
View as plain text  
Hi all,

after running 2 weeks without error, today my MySQL replication broke.
SHOW SLAVE STATUS tells me

Last_Errno: 1406
Last_Error: Error 'Data too long for column 'i18nvalue' at row1' on
query. Default database: 'xxxx'. Query: 'insert into portal_i18n
(i18nvalue, i18nkey) values ('<?xml version=\"1.0\"?>\n\n<root
available-locales=\"en_US\"> \n <name
language-id=\"de_DE\">Gültig
von</name> \n <name language-id=\"en_US\">Valid
from</name>\n</root>',
'employee.details.chipcard.title2table2')'

Of course the data isn't too long for the table (the entry for i18nvalue
is only 161 bytes long), the structure of the table is

mysql> DESCRIBE portal_i18n;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| i18nid    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| i18nvalue | text         | NO   |     |         |                |
| i18nkey   | varchar(255) | NO   |     |         |                |
+-----------+--------------+------+-----+---------+----------------+

The query worked on the replication master.
BTW: Why does he say "Gültig"? It should be Gültig, so the
Umlaut ü
got scrambled.

All the rows already in the table don't contain any Umlaut (the Umlauts
are encoded in &uuml; etc. HTML style), so the first Umlaut that wasn't
encoded broke the setup. (Needless to stress that the setup _should_
survive umlauts ...)

Both the replication slave and replication master have the same charset
settings which are:
mysql> SHOW VARIABLES LIKE "character_set%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Both master and slave run MySQL version 5.0.32-Debian_7etch1-log, as in
Debian 4.0.

What's my mistake?

TIA
- C. Lechner

[This was posted about a month ago in the MySQL forums, but I got no
reply. The problem is still persisting, please don't feel offended by
this repost.]
Thread
Replication breaks with error 1406 when confronted with UmlautsChristoph Lechner15 Nov
  • Re: Replication breaks with error 1406 when confronted with UmlautsMarcus Bointon15 Nov
    • Re: Replication breaks with error 1406 when confronted with UmlautsChristoph Lechner15 Nov