MySQL Bugs: #43824: Special character search records with case insensitive

Bug #43824	Special character search records with case insensitive
Submitted:	24 Mar 2009 7:58	Modified:	30 Mar 2009 13:23
Reporter:	padma b	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.0.32, 5.0.77	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	special character search results

Description:
Hi,

I am having problem in case of special characters.

If i execute the query 
"SELECT * FROM restarant_details WHERE rest_names like '%casa%'" ,
its showing all the records(case insensitive) which present in the table , it will give the record restaurant name as ' CASA balmes'

Same if i do with special character containing search word 
"SELECT * FROM restarant_details WHERE rest_names like '%Àngels%'",
its showing the records with restaurant name as '
Els Àngels restaurant'.
If i do same search with 'àngels' word 
"SELECT * FROM restarant_details WHERE rest_names like '%àngels%'", the records has be come for same search with 'Àngels'
In this case its not giving any records.

The character set we are using is 'utf-8' and collation is 'utf_general_ci' and we have created FULL TEXT INDEX for the rest_names field.

How to repeat:
I am not understand

Thank you for the report.

But version 5.0.32 is old. Please upgrade to current version 5.0.77, try with it and if problem still exists provide output of SHOW CREATE TABLE restarant_details

The result for 'SHOW CREATE TABLE restarant_details'

 
CREATE TABLE `restarant_details` (\n  `restaurant_id` bigint(20) NOT NULL auto_increment,\n  `rest_names` varchar(100) default NULL,\n  PRIMARY KEY  (`restaurant_id`),\n  FULLTEXT KEY `rest_name` (`rest_names`)\n) ENGINE=MyISAM AUTO_INCREMENT=1484 DEFAULT CHARSET=utf8

Thank you for the feedback.

I can not repeat described behavior. Please upgrade to version 5.0.77, try with it and inform us if problem still exists.

I have upgrade Mysql version to 5.0.77 , now also we are getting same problem with special catalan characters.

Please help in this

Thank you for the feedback.

I could not repeat described behavior with test data using correct client character set. Please provide output of SHOW VARIABLES LIKE '%char%' and SHOW VARIABLES LIKE '%coll%'

The output of two queries is like this

Query1: SHOW VARIABLES LIKE '%char%'
Result:
Variable_name 	             Value
----------------------------------------
character_set_client  	     utf8
character_set_connection     utf8
character_set_database 	     utf8
character_set_filesystem     binary
character_set_results 	     utf8
character_set_server 	     latin1
character_set_system 	     utf8
character_sets_dir 	     /usr/share/mysql/charsets/

Query2: SHOW VARIABLES LIKE '%char%'
Result:
Variable_name 	        Value
----------------------------------------
collation_connection 	utf8_unicode_ci
collation_database 	utf8_general_ci
collation_server 	latin1_swedish_ci

Sorry , i have send the query wrong in earlier message,Could you please consider this message.

The output of two queries is like this

Query1: SHOW VARIABLES LIKE '%char%'
Result:
Variable_name 	             Value
----------------------------------------
character_set_client  	     utf8
character_set_connection     utf8
character_set_database 	     utf8
character_set_filesystem     binary
character_set_results 	     utf8
character_set_server 	     latin1
character_set_system 	     utf8
character_sets_dir 	     /usr/share/mysql/charsets/

Query2: SHOW VARIABLES LIKE '%coll%'
Result:
Variable_name 	        Value
----------------------------------------
collation_connection 	utf8_unicode_ci
collation_database 	utf8_general_ci
collation_server 	latin1_swedish_ci

Thank you for the feedback.

Everything looks correct, but I still can not repeat described behavior:

mysql> drop table restarant_details;
Query OK, 0 rows affected (0.08 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_connection='utf8_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8                                                 | 
| character_set_connection | utf8                                                 | 
| character_set_database   | utf8                                                 | 
| character_set_filesystem | binary                                               | 
| character_set_results    | utf8                                                 | 
| character_set_server     | utf8                                                 | 
| character_set_system     | utf8                                                 | 
| character_sets_dir       | /usr/local/mysql-5.1.31-osx10.4-i686/share/charsets/ | 
+--------------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci | 
| collation_database   | utf8_general_ci | 
| collation_server     | utf8_general_ci | 
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE `restarant_details` (
    -> `restaurant_id` bigint(20) NOT NULL auto_increment,
    -> `rest_names` varchar(100) default NULL,
    -> PRIMARY KEY (`restaurant_id`),
    -> FULLTEXT KEY `rest_name` (`rest_names`)
    -> )
    -> ENGINE=MyISAM AUTO_INCREMENT=1484 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into restarant_details (rest_names) values('Àngels'), ('àngels');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM restarant_details WHERE rest_names like '%Àngels%';
+---------------+------------+
| restaurant_id | rest_names |
+---------------+------------+
|          1484 | Àngels    | 
|          1485 | àngels    | 
+---------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM restarant_details WHERE rest_names like '%àngels%';
+---------------+------------+
| restaurant_id | rest_names |
+---------------+------------+
|          1484 | Àngels    | 
|          1485 | àngels    | 
+---------------+------------+
2 rows in set (0.00 sec)

Please provide repeatable test case like I did.

I have follow the same commands and tried

But the records are not inserting properly means rest_names is not taking the values with special catalan characters( Àngels,àngels ).

The below command is showing the out put like this

mysql> SELECT * FROM restarant_details ;
+---------------+------------+
| restaurant_id | rest_names |
+---------------+------------+
|          1484 |            | 
|          1485 |            | 
+---------------+------------+

I have executed the query "SHOW VARIABLES LIKE '%char%';" 
The " character_set_server     | latin1 ". Its not UTF-8. 
mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
  Is this a problem for inserting Special characters and getting the records.

I am trying to change the character_set_server using this one
> mysqld --character-set-server=utf-8

Its throwing an error
mysqld: Character set 'utf-8' is not a compiled character set and is not spe
ified in the 'C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\Index.xml'
file
090325 18:36:56 [ERROR] Aborting

090325 18:36:56 [Note] mysqld-nt: Shutdown complete

Can u please give an instructions how to change the 'character-set-server'?

Thanks,
Padma

the innodb category must be a mistake, making it charsets. Note that the only
table for which we have CREATE is a MyISAM table, with ft index.

Thank you for the feedback.

As I see from the output you use command line client on Windows. It is not possible to work properly with UTF8 character set using command line client on Windows, because limitation of cmd.exe which does not support encoding UTF-8. You either should use sql file with encoding UTF-8, then load it using source command or SET NAMES in such way what it reflects your local setting. See manual of Windows command line utility chcp.

> Character set 'utf-8' is not a compiled character set
Make sure character_sets_dir  in your my.ini set to the proper directory. See bug #17270 for details.

Thanks for your response

My local setup is on windows. My live server is LINUX only.
Trying all these in my local set up(windows).
I have changed the charcter-set-server to 'utf-8'.

Now i have followed all the steps result is like this 

OUTPUT: 
mysql> drop table restarant_details;
Query OK, 0 rows affected (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_connection='utf8_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+-----------------
| character_set_client     | utf8
     |
| character_set_connection | utf8
     |
| character_set_database   | utf8
     |
| character_set_filesystem | binary
     |
| character_set_results    | utf8
     |
| character_set_server     | utf8
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\chars
ets\ |
+--------------------------+---------------------------
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

And i have created the Table as like the instructions and inserted the data.
But its not accepting the special characters..

Please check the output of queries:
mysql> CREATE TABLE `restarant_details` (
    ->     `restaurant_id` bigint(20) NOT NULL auto_increment,
    ->     `rest_names` varchar(100) default NULL,
    ->     PRIMARY KEY (`restaurant_id`),
    ->     FULLTEXT KEY `rest_name` (`rest_names`)
    ->     )
    ->     ENGINE=MyISAM AUTO_INCREMENT=1484 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into restarant_details (rest_names) values('Àngels'), ('àngels')
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from restarant_details;
+---------------+------------+
| restaurant_id | rest_names |
+---------------+------------+
|          1484 |            |
|          1485 |            |
+---------------+------------+
2 rows in set (0.00 sec)

Please provide the solution to overcome this one. I am trying this on Windows. My server is LINUX Server.

Thanks,
Padma

Thank you for the feedback.

I answered your question already: "You either should use sql file with encoding UTF-8, then load it using source command or SET NAMES in such way what it reflects your local setting. See manual of Windows command line utility chcp." Alternatively you can use other client like MySQL Query Browser.

Thanks for your reply.

I have follow your instructions and got the same output.

If i execute this with PHP functions mysql_query with form submission values, its giving empty result set.

Can you please provide solution for this. Through PHP functions.

Thanks,
Padma