MySQL Bugs: #31628: can't create table with chinese tablename of odd characters

Bug #31628	can't create table with chinese tablename of odd characters
Submitted:	16 Oct 2007 5:32	Modified:	19 Nov 2007 13:46
Reporter:	todiff wu	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S2 (Serious)
Version:	5.0.44sp1	OS:	Windows
Assigned to:	Alexander Barkov	CPU Architecture:	Any
Tags:	charset, Chinese, CREATE TABLE

Description:
can't create table with chinese tablename of odd characters in mysql 5.0.44sp1 for windows. I have repeated the issue production,the quuestion happens actually on only mysql 5.0 for win, not in mysql 5.0 for linux and 5.1.

How to repeat:
the repeat procedure is described as follow:
 
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44sp1-enterprise-gpl-nt MySQL Enterprise Serv
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Database changed
 
mysql> 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
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)
 
mysql> create table 中国大使馆 (id int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '国大
使馆 (id int)' at line 1
 
mysql> create table 中国大使 (id int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '国大
使 (id int)' at line 1
 
mysql> set names gbk;
Query OK, 0 rows affected (0.02 sec)
 
mysql> create table 中国大使馆 (id int);
ERROR 1005 (HY000): Can't create table '涓浗澶т娇棣? (errno: 22)
 
mysql> create table 中国大使 (id int);
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into 中国大使 values (1),(2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from 中国大使;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.03 sec)
 
mysql> create table 中国大 (id int);
ERROR 1005 (HY000): Can't create table '涓浗澶? (errno: 22)
 
mysql> create table 中国 (id int);
Query OK, 0 rows affected (0.02 sec)
 
mysql> create table 中 (id int);
ERROR 1005 (HY000): Can't create table '涓? (errno: 22)
 
mysql>

Suggested fix:
recompile and change variable character_set_system value helped

Miguel,

I tried to modify the status to verified,but it was so odd that I couldn't change it, only open or closed was available for me to select,nay, i can't unassign myself. 
I had used Query Browser to replace mysql.exe to do that, the same error no 22 occored. I will test it again using Query Browser and attach the error image later on,
and Sean had reminded me to use linux mysql client to do that too, I will test that once linux OS is installed on my VM then upload the testing result.

Thanks.

testing using Query Browser,the same errno 22.

Attachment: executing_on_QBrowser_of_win_01.gif (image/gif, text), 140.42 KiB.

I have tested connecting from mysql client of version 5.0.44sp1 on  Linux to mysql server 5.0.44SP1 on Windows platform, but the same result occurs again as follow:

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

mysql> create table 中国大使馆 (id int);
ERROR 1005 (HY000): Can't create table '涓澶т娇棣' (errno: 22)

This problem was fixed in 5.1 by introducing so called
"table name to file name encoding".

It won't be fixed in 5.0.

You have either to switch to 5.1, or avoid
using Chinese characters in table names.