MySQL Bugs: #15338: mysqlclient can't handle characters whose hex string is XX60 for table name.

Bug #15338	mysqlclient can't handle characters whose hex string is XX60 for table name.
Submitted:	30 Nov 2005 1:18	Modified:	20 Dec 2005 6:48
Reporter:	Tetsuro Ikeda	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Windows (Windows2000Pro)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
Usually when we define the table, we use CREATE statement like this.

mysql> create table `tbname` (c1 int);

If the table name's hex string includes "60" for the second byte, mysqlclient can't handle it properly.

The hex string part "60" is the hex string for '`', and this causes the problem.

How to repeat:
I used some Japanese characters for repeating this issue. I'm sorry that I don't know how to input hex string directory for table name. 

mysql> create table `港` (c1 int);
    `> \c
    `> `
    -> \c
mysql>
mysql> create table `柿` (c1 int);
    `> `
    -> \c
mysql>
mysql> create table `本` (c1 int);
Query OK, 0 rows affected (0.04 sec)

# '港' and '柿' don't work. '本' works.
# You can see these characters difference by HEX.

mysql> select hex('港');
+-----------+
| hex('港') |
+-----------+
| 8D60      |
+-----------+
1 row in set (0.00 sec)

mysql> select hex('柿');
+-----------+
| hex('柿') |
+-----------+
| 8A60      |
+-----------+
1 row in set (0.00 sec)

mysql> select hex('本');
+-----------+
| hex('本') |
+-----------+
| 967B      |
+-----------+
1 row in set (0.00 sec)

# The characters that don't work have hex string as "**60".
# And the hex string of '`' is "60".

mysql> select hex('`');
+----------+
| hex('`') |
+----------+
| 60       |
+----------+
1 row in set (0.00 sec)

Suggested fix:
If you try the following, you can use second byte "60" characters.

mysql> create table `柿`` (c1 int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table `港`` (c1 int);
Query OK, 0 rows affected (0.05 sec)

So, the handling of '`' should be fixed.

When I tried with Connector/J 3.1.x from Java application, the problem didn't occur. So, this must be mysqlclient issue.

I was able to repeat this using a recent 5.0 pull under WinXP

I also tested this under FC4 with the same results

This is not a bug. Your client seems to be not configured to use SJIS character set.
You're most likely using latin1, which is the default character set.
You need to add this line into your my.cnf file:

default-character-set=sjis

I don't think this is a charset configuration issue.

My current my.ini is like following:

[mysqld]
default-character-set=sjis

And, I tried like following and got same result.

D:\mysql\mysql-noinstall-5.0.16-win32\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'char%';
+--------------------------+-------------------------------------------------------+
| Variable_name            | Value                                                 |
+--------------------------+-------------------------------------------------------+
| character_set_client     | latin1                                                |
| character_set_connection | latin1                                                |
| character_set_database   | sjis                                                  |
| character_set_results    | latin1                                                |
| character_set_server     | sjis                                                  |
| character_set_system     | utf8                                                  |
| character_sets_dir       | D:\mysql\mysql-noinstall-5.0.16-win32\share\charsets\ |
+--------------------------+-------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> create table `港` (c1 int);
    `> `
    -> \c
mysql>
mysql> set names sjis;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `港` (c1 int);
    `> `
    -> \c
mysql>
mysql> show variables like 'char%';
+--------------------------+------------------------------------------------------------+
| Variable_name            | Value                                                      |
+--------------------------+------------------------------------------------------------+
| character_set_client     | sjis                                                       |
| character_set_connection | sjis                                                       |
| character_set_database   | sjis                                                       |
| character_set_results    | sjis                                                       |
| character_set_server     | sjis                                                       |
| character_set_system     | utf8                                                       |
| character_sets_dir       | D:＼mysql＼mysql-noinstall-5.0.16-win32＼share＼charsets＼ |
+--------------------------+------------------------------------------------------------+
7 rows in set (0.00 sec)

Ikeda-san, would you please try it like this?

D:\ > mysql --default-character-set=sjis
mysql> create table ...

As far as I tried, above works well.
Or, it maybe works well, if you set 'default-character-set=sjis' 
at [mysql] or [client] section of my.ini. (NOT ONLY [mysqld] section)

So, I think the problem is why following two cases work differently.

a) Launch mysql.exe (charset is latin1 by default), and
execute 'SET NAMES sjis;', and create table.

b) Launch mysql.exe with '--default-character-set=sjis',
and create table.

What do you think, Alexander?

I make a sense. This is not a bug. I mistook [mysql] for [mysqld].
Thank you for pointing out and I'm so sorry.

D:\mysql\mysql-noinstall-5.0.16-win32\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> create table `港` (c1 int);
Query OK, 0 rows affected (0.08 sec)

mysql>

This is Tajika. Corrective comment to Alexander. My understanding is, 
'SET NAMES sjis' is interpreted by mysqld server,
while '--default-character-set=sjis' is interpreted by mysql client.
So, 'SET NAMES sjis' does nothing for mysql client to 
recognize 0x60 as a trailing byte of multi-byte character.
That is, this behavior is by design for now. Is this right?

Yes. "SET NAMES sjis" doesn't effect this behavior. The confusable thing is here.

Is this still not a bug?

----------------
The following is done without my.ini configuration.

D:\mysql\mysql-noinstall-5.0.16-win32\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> show variables like 'character_set_client'\G
*************************** 1. row ***************************
Variable_name: character_set_client
        Value: latin1
1 row in set (0.01 sec)

mysql> set names sjis;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'character_set_client'\G
*************************** 1. row ***************************
Variable_name: character_set_client
        Value: sjis
1 row in set (0.00 sec)

mysql> create table `港` (c1 int);
    `> `
    -> \c
mysql>

I think it's confusing, too. In both cases I mentioned above,
"SHOW VARIABLES LIKE 'character%'" returns same result, 
and we can't distinguish them.

To resolve this issue, mysql client will need to have features like below. 
- Check if SQL command input is like 'SET NAMES xxx', or
- Accept a signal from the server, which asks the client to change 
  internal charset to xxx. 

But I'm neutral about whether mysql client tool should be so intelligent.
Anyway, shall we wait for Alexander to come back and give us a comment?

p.s. I'm at NEC System Technologies.

Hello guys, thank you very much for your feedback!

That's true, SET NAMES doesn't change anything on the client side.
This is a known problem, and it could be solved in two ways:
- client library could check every outgoing query to catch
  "SET NAMES" or "SET character_set_client" queries and
then execute "SELECT character_set_client" to know the
new value.
- server could send the new character set value after "SET NAMES"
or "SET character_set_client" queries.

The first method will slow down the client library, and it is not a good idea.
The second method is much better. However it requires changes
in the client-server protocol. This is something we can't do in 4.1 and 5.0.
I hope we'll fix this problem in 5.1.

Meanwhile, please use my.cnf file to set the proper character set.