Bug #18321 Can't store Euro Sign with latin1_german1_ci and latin1_general_ci
Submitted: 18 Mar 2006 1:17 Modified: 8 Apr 2006 14:10
Reporter: Jan W. Hentsch Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:ut to 4.1.18 and 5.0.19 OS:Linux (Linux (maybo others as well))
Assigned to: Alexander Barkov CPU Architecture:Any
Tags: corruption

[18 Mar 2006 1:17] Jan W. Hentsch
Description:
independent of my client and connection collation setting (weather utf8 or latin1) I can't store the EuroSign in columns that have a collation of latin1_general_ci or latin1_german1_ci. With other collations, like latin1_german2_ci or latin1_swedish_ci or utf8_general_ci it works fine.

the Eurosign gets converted to a "?".

I understood that the difference between german1 and german2 is just the sorting order, I don't see a reason for not accepting the Eurosign.

How to repeat:
CREATE TABLE t3 (
  id int(10) unsigned NOT NULL auto_increment,
  f1 varchar(40) character set ascii default NULL,
  f2 varchar(40) character set latin1 collate latin1_bin default NULL,
  f3 varchar(40) character set latin1 collate latin1_general_ci default NULL,
  f4 varchar(40) character set latin1 collate latin1_german1_ci default NULL,
  f5 varchar(40) character set latin1 collate latin1_german2_ci default NULL,
  f6 varchar(40) character set latin1 default NULL,
  f7 varchar(40) character set utf8 collate utf8_bin default NULL,
  f8 varchar(40) default NULL,
  f9 varchar(40) character set utf8 collate utf8_swedish_ci default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t3 VALUES (
    1,
    'ascii_general_ci',
    'latin1_bin',
    'latin1_general_ci',
    'latin1_german1_ci',
    'latin1_german2_ci',
    'latin1_swedish_ci',
    'utf8_bin',
    'utf8_general_ci',
    'utf8_swedish_ci'
);
INSERT INTO t3 VALUES (
    2,
    'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc',
    'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc',
    'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc',
    'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc',
    'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc',
    'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc',
    'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc',
    'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc',
    'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc'
);

Suggested fix:
maybe its in the charset configuration files (aka /usr/share/mysql/charsets/latin1.xml), I tried to understand these files, but no success so far.
[18 Mar 2006 9:47] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat on a latest versions, 4.1.18 and 5.0.19, and inform about the results.
[18 Mar 2006 19:54] Jan W. Hentsch
Hi,

after compiling the latest versions 4.1.18 and 5.0.19 I tried these versions and the same happens again.

BTW: I thought you folks always have the latest versions ready for testing, so I was a little bit wondering about your request. I dit more expect you to test it against the latest releases yourself, which is a little to do if you already have the right environment, but wich is a whole lot to do if you have to search a reasonable machine and install the latest environment for such a small test.

But however, it seems to be a problem, so hopefully you can find it, good luck and thanks again.

cu jwh
[19 Mar 2006 22:45] Miguel Solorzano
Thank you for the bug report.

mysql> select * from t3\G
*************************** 1. row ***************************
id: 1
f1: ascii_general_ci
f2: latin1_bin
f3: latin1_general_ci
f4: latin1_german1_ci
f5: latin1_german2_ci
f6: latin1_swedish_ci
f7: utf8_bin
f8: utf8_general_ci
f9: utf8_swedish_ci
*************************** 2. row ***************************
id: 2
f1: ABC ???????~ ?????? ????? ????? @ abc
f2: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f3: ABC ?°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f4: ABC ?°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f5: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f6: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f7: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f8: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f9: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
2 rows in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.19-debug-log |
+------------------+
1 row in set (0.01 sec)

*************************** 1. row ***************************
id: 1
f1: ascii_general_ci
f2: latin1_bin
f3: latin1_general_ci
f4: latin1_german1_ci
f5: latin1_german2_ci
f6: latin1_swedish_ci
f7: utf8_bin
f8: utf8_general_ci
f9: utf8_swedish_ci
*************************** 2. row ***************************
id: 2
f1: ABC ???????~ ?????? ????? ????? @ abc
f2: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f3: ABC ?°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f4: ABC ?°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f5: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f6: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f7: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f8: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
f9: ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc
2 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.20-debug |
+--------------+
1 row in set (0.01 sec)

mysql>                             

*************************** 1. row ***************************
id: 1
f1: ascii_general_ci
f2: latin1_bin
f3: latin1_general_ci
f4: latin1_german1_ci
f5: latin1_german2_ci
f6: latin1_swedish_ci
f7: utf8_bin
f8: utf8_general_ci
f9: utf8_swedish_ci
*************************** 2. row ***************************
id: 2
f1: ABC ???????????????~ ???????????? ??????
f2: ABC €°§ß²³µ~ äöüÄÖÜ áéí
f3: ABC €°§ß²³µ~ äöüÄÖÜ áéí
f4: ABC €°§ß²³µ~ äöüÄÖÜ áéí
f5: ABC €°§ß²³µ~ äöüÄÖÜ áéí
f6: ABC €°§ß²³µ~ äöüÄÖÜ áéí
f7: ABC €°§ß²³µ~ äöüÄÖÜ áéí
f8: ABC €°§ß²³µ~ äöüÄÖÜ áéí
f9: ABC €°§ß²³µ~ äöüÄÖÜ áéí
2 rows in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.8-beta-debug |
+------------------+
1 row in set (0.01 sec)

mysql>
[23 Mar 2006 7:06] Jan W. Hentsch
So I realize, it's working correct in the 5.1 version. Does this mean I can use the character set and collation definition files (/usr/share/mysql/charsets) from the 5.1 version? Or is it in the code?
[6 Apr 2006 5:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4535
[6 Apr 2006 6:38] Alexander Barkov
Fixed in 4.1.19 and 5.0.21.
[8 Apr 2006 14:10] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 4.1.19 and 5.0.21 changelogs. Closed.
[6 Aug 2008 13:22] fn ft
EURO SYMBOL (€) IN EVERY DATABASE - SOLVED

If you want to store the euro symbol in any field in your database, just use "€" instead "€".

Enjoy.