MySQL Bugs: #39064: Problem with searching for UTF-8 Characters

Bug #39064	Problem with searching for UTF-8 Characters
Submitted:	27 Aug 2008 8:53	Modified:	28 Aug 2008 19:36
Reporter:	Sven Lehnert	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S1 (Critical)
Version:	5.0.67 on Ubuntu	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I've some tables with UTF-8 as character set and also an Connection with UTF-8. 
I called a Select routine in a Stored Procedure where i searched for the letter Ŧ (it is an unice character not Ascii). With the Version 5.0.51a it will work fine but in 5.0.67 it gives me the result for a "?" instead of "Ŧ".
I didn't change anything and after downgrading it works well.

Bye Sven!

How to repeat:
No idea

Thank you for the report.

Please provide repeatable test case: query, SHOW CREATE TABLE for all underlying tables, data from tables bug is repeatable with.

Hello!

Sorry my English is so bad so a cannot really explain the Problem. Know i get out that i have the same problem with 5.0.51a on Ubuntu but with 5.0.31 on Fedora 6 there is no Problem.

I've a Table with utf8 fields. 

If i do the following:

SELECT Kuerzel from Gilden where Kuerzel like '%Ŧ%';

i will get all Fields with the Character Ŧ inside,

but if i try to make this

call gildentest('%Ŧ%');

i will get all Fiedls with an ? insinde.

The Stored Procedure (for this test) only looks like that:

BEGIN
SELECT Kuerzel from Gilden where Kuerzel like suche;
END

suche is dafinied as varchar(20);

I tried it with some options like charset, set names etc. but it doesn't work.
I don't know why it is a different between calling from a stored procedure.

Bye Sven!

Thank you for the feedback.

Please provide output of SHOW CREATE TABLE Gilden

Hello!

Gilden | CREATE TABLE `Gilden` (
  `PRIMARY` bigint(20) NOT NULL auto_increment,
  `Kuerzel` varchar(10) collate utf8_unicode_ci NOT NULL default '',
  `MapID` bigint(20) unsigned default NULL,
  `Name` varchar(100) collate utf8_unicode_ci default NULL,
  `Mitglieder` int(11) default NULL,
  `Gruendung` date default NULL,
  `Gildentext` text collate utf8_unicode_ci,
  `LastUpdate` datetime default NULL,
  `SeafightNr` varchar(20) collate utf8_unicode_ci default NULL,
  `Mehrfachvorhanden` tinyint(1) default '0',
  `AllianzID` bigint(20) NOT NULL default '0',
  `lastupdatealli` datetime default NULL,
  `geaendertvon` varchar(30) collate utf8_unicode_ci NOT NULL default 'System',
  PRIMARY KEY  (`PRIMARY`),
  KEY `primaer` (`PRIMARY`),
  KEY `SeafightNr` (`SeafightNr`,`MapID`),
  KEY `kuerzel` (`Kuerzel`,`MapID`),
  KEY `suche3` (`lastupdatealli`),
  KEY `suche` (`Kuerzel`),
  KEY `suche4` (`AllianzID`,`MapID`)
) ENGINE=MyISAM AUTO_INCREMENT=99563 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Alle Gilden' |

CREATE DEFINER=`php`@`localhost` PROCEDURE `gildentest`(suche varchar(20))
BEGIN
SELECT Kuerzel from Gilden where Kuerzel like suche;
END

mysql> call gildentest('%Ŧ%');
+---------+
| Kuerzel |
+---------+
| [  ?]   |
| [`?`]   |
| [`?`]   |
| [_?_]   |
| [_?_]   |
| [_S?]   |
| [-:?]   |
| [-?-]   |

mysql> select kuerzel from Gilden where Kuerzel like '%Ŧ%';
+---------+
| kuerzel |
+---------+
| [»Ŧ«]   |
| [§ŦÅ]   |
| [†Ŧ†]   |
| [†Ŧþ]   |
| [¦ŦĦ]   |
| [~Ŧ~]   |
| [~Ŧ~]   |
| [$ĐŦ]   |
| [₣ŦĦ]   |
| [₣ŦĦ]   |
| [₣ŦŴ]   |

Hope that is what you want.

Bye Sven!

Hello .... nice... i think i have found my mistake.

I copied the Databases 1:1 with Navicat BUT it created a new table with the (wrong) default Charset Latin1. After changing this and changing the Stored procedure (otherwise there is no different) it works. 
Sorry....

Bye Sven!

Thank you for the feedback.

Closed as "Not a Bug", because last comment.