MySQL Bugs: #37724: Currency symbols like € are not recogonized

Bug #37724	Currency symbols like € are not recogonized
Submitted:	29 Jun 2008 7:03	Modified:	30 Jul 2008 11:48
Reporter:	albert arul prakash rajendran	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S1 (Critical)
Version:	5.1	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	€, euro currency, euro symbol

Description:
Hi i found this bug in MySQL which is not accepting the euro currency symbol € into the database. It happens in ISAM storage engine also. This problem is because DEFAULT CHARACTER SET = utf8, latin1 is not supporting the symbol €

Hope this helps you guys to find out the problem.

How to repeat:
Steps to reproduce 

Create a table with below script
DROP TABLE IF EXISTS `albert`.`Currency` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `albert`.`Currency` (
  `idCurrency` INT NOT NULL AUTO_INCREMENT ,
  `CurrencySymbol` VARCHAR(10) NOT NULL DEFAULT '$' ,
  `currencyname` VARCHAR(100) NULL ,
  `shortcurrency` VARCHAR(50) NULL ,
  `insertby` VARCHAR(50) NOT NULL DEFAULT 'IndiansInc' ,
  `exist` CHAR(1) NOT NULL DEFAULT 'y' ,
  PRIMARY KEY (`idCurrency`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

SHOW WARNINGS;

now insert the test data with the currency symbols and codes using the below list

-- -----------------------------------------------------
-- Data for table `albert`.`Currency`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `Currency` (`idCurrency`, `CurrencySymbol`, `currencyname`, `shortcurrency`, `insertby`, `exist`) VALUES (1, '$', 'UnitedStates Dollar', 'USD', 'IndiansInc', 'y');
INSERT INTO `Currency` (`idCurrency`, `CurrencySymbol`, `currencyname`, `shortcurrency`, `insertby`, `exist`) VALUES (2, '£', 'British Pound', 'GBP', 'IndiansInc', 'y');
INSERT INTO `Currency` (`idCurrency`, `CurrencySymbol`, `currencyname`, `shortcurrency`, `insertby`, `exist`) VALUES (3, '€', 'Euro', '', 'IndiansInc', 'y');
INSERT INTO `Currency` (`idCurrency`, `CurrencySymbol`, `currencyname`, `shortcurrency`, `insertby`, `exist`) VALUES (4, 'AUS$', 'Australian Dollar', 'AUD', 'IndiansInc', 'y');

COMMIT;

Now If we run the below query in mysql server

select * from Currency;

We will find the euro currency symbol (€) is converted to a question mark (?).

I am not sure which Character set supports Euro symbols and other currency symbols. If Mysql comes with a currency table (DDL) and insert data (DML) then it will be a great help for all developers like me.

Thanks 
Albert Arul Prakash
http://www.bepenfriends.com

many thanks for writing a bug report.

With my first test I could reproduce it but making the test a second time, I couldn't reproduce.

I have to get deeper into this.

Ok I just started to make lots of tests on 5.1-bzr-tree.

Here is the first of my tests:

Connection 1:
TERMINAL: UTF8

Open MySQL CLI

create database bug37724latin1 character set latin1;

\u bug37724latin1

create table tlatin1myisam(t varchar(10))engine=myisam;

create table tlatin1innodb(t varchar(10)) engine=innodb;

set names utf8;

show variables like 'char%';

+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| character_set_client     | utf8                                           | 
| character_set_connection | utf8                                           | 
| character_set_database   | latin1                                         | 
| character_set_filesystem | binary                                         | 
| character_set_results    | utf8                                           | 
| character_set_server     | latin1                                         | 
| character_set_system     | utf8                                           |

insert into tlatin1myisam values('€');

insert into tlatin1innodb values('€');

select length(t) from tlatin1myisam;
+-----------+
| length(t) |
+-----------+
|         1 | 
+-----------+

select length(t) from tlatin1innodb;
+-----------+
| length(t) |
+-----------+
|         1 | 
+-----------+

select * from tlatin1innodb;
+------+
| t    |
+------+
| €  | 
+------+

select * from tlatin1myisam;
+------+
| t    |
+------+
| €  | 
+------+

Connection 2:
Terminal: ISO-8859-15
Open CLI:

\u bug37724latin1

set names latin1;

show variables like 'char%';
+--------------------------+------------------------------------------------+
| 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

select * from tlatin1innodb;
+------+
| t    |
+------+
|     | 
+------+

select * from tlatin1myisam;
+------+
| t    |
+------+
|     | 
+------+

In my CLI it looks like "nothing" or better like ''. That there is a sign, I just figured out by copy-paste here.

Test 2: vice versa to test 1:

TERMINAL ISO-8859-15 plus set names latin1:
same database: bug37724latin1 with latin1 as default character set.

create table t2latin1innodb(t varchar(10))engine=innodb;
create table t2latin1myisam(t varchar(10))engine=myisam;

insert into t2latin1myisam values('€');
insert into t2latin1innodb values('€');

select length(t) from t2latin1myisam;
+-----------+
| length(t) |
+-----------+
|         1 | 
+-----------+

select length(t) from t2latin1innodb;
+-----------+
| length(t) |
+-----------+
|         1 | 
+-----------+

select * from t2latin1myisam;
+------+
| t    |
+------+
| €    | 
+------+
1 row in set (0.00 sec)

select * from t2latin1innodb;
+------+
| t    |
+------+
| €    | 
+------+

Other connection:
Terminal UTF8 plus SET NAMES UTF8:

select * from t2latin1myisam;
+------+
| t    |
+------+
| ¤   | 
+------+

select * from t2latin1innodb;
+------+
| t    |
+------+
| ¤   | 
+------+

Test 3:

Terminal UTF8 and SET NAMES UTF8:

create database bug37724utf8 character set utf8;
\u bug37724utf8

create table tutf8myisam(t varchar(10))engine=myisam;
create table tutf8innodb(t varchar(10))engine=innodb;

insert into tutf8myisam values ('€');
insert into tutf8innodb values('€');

select length(t) from tutf8myisam;
+-----------+
| length(t) |
+-----------+
|         3 | 
+-----------+

select length(t) from tutf8innodb;
+-----------+
| length(t) |
+-----------+
|         3 | 
+-----------+

select * from tutf8myisam;
+------+
| t    |
+------+
| €  | 
+------+

select * from tutf8innodb;
+------+
| t    |
+------+
| €  | 
+------+

Other connection:
Terminal ISO-8859-15 plus SET NAMES LATIN1:

\u bug37724utf8

select * from tutf8myisam;
+------+
| t    |
+------+
|     | 
+------+

select * from tutf8innodb;
+------+
| t    |
+------+
|     | 
+------+

Same as on test 1 here. In my terminal I see no sign ... it looks like ''.

I think I can stop testing here.

Problem: Euro sign needs 3 bytes in UTF8.

My suggestion is for converting from UTF8 to LATIN1 there is only a check for 2 byte signs not for 3 byte signs.

This is apparently not a bug.

The reason why it fails on Linux is different to the reason why it fails here for you.

You are using Windows.

Please figure out first, which client encoding your Windows is using.

Just type in a command Window:
CHCP

if your windows is using code page 850 then you have to make "SET NAMES CP850" first. Only if it use code page 1252 then you can use "SET NAMES LATIN1".

The problem here is, that the Windows encoding must have the possibility to display the Euro sign and that's only possible when you tell the system, what encoding your windows is using.

The Linux problem is at bug #37738

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".