MySQL Bugs: #50187: Logon denied when username or password with specific french language character

Bug #50187	Logon denied when username or password with specific french language character
Submitted:	8 Jan 2010 13:43	Modified:	16 Jan 2012 9:50
Reporter:	François LHERITIER	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.01.09	OS:	Windows (Vista Pro)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	denied, french, login, logon

Description:
14:21:51  DRIVER={MySQL ODBC 3.51 Driver};SERVER=LOCALHOST;DATABASE=Ithy;
          USER=FRANÇOIS;PASSWORD=FRANÇOIS 
	  Erreur Code=0

14:23:39  DRIVER={MySQL ODBC 5.1 Driver};SERVER=LOCALHOST;DATABASE=Ithy;
          USER=FRANÇOIS;PASSWORD=FRANÇOIS
          Erreur Code=-2147467259
          Microsoft OLE DB Provider for ODBC Drivers
         [MySQL][ODBC 5.1 Driver]Access denied for user 'FRANÃ‡OIS'@'localhost'
         (using password: YES)

Driver 5.1.6 don't accept some specific french character language, in the sample the character is "Ç"

How to repeat:
With any programmation language using ODBC Driver .

Hi François and thanks for your report.

Please try the same with mysql command line client and see if it connects.

Hi Tonci and thanks for your help.

Result of "MySql command Line" = "mysql.exe -u FRANÇOIS -pFRANÇOIS -D ithy"
P:\>mysql -u FRANÇOIS -pFRANÇOIS -D ithy --default-character-set=UTF8
ERROR 1045 (28000): Access denied for user 'FRAN?OIS'@'localhost' (using password: YES)

P:\>mysql -u FRANÇOIS -pFRANÇOIS -D ithy --default-character-set=LATIN1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 968
Server version: 5.1.42-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.42, for Win32 (ia32)

Connection id:          968
Current database:       ithy
Current user:           FRANÃOIS@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.42-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 4 days 37 min 16 sec

Threads: 3  Questions: 24189  Slow queries: 0  Opens: 250  Flush tables: 1  Open tables: 1  Queries per second avg: 0.69
--------------

mysql>

François, please try configuring DSN by putting LATIN1 in Details/Misc Options/Character set. Inform me of result.

I have already tried this test with all the charset given by Mysql , result was always negative .
I have also tried to specify the CHARSET=LATIN1 in my connection string but that was unsucessfull .
With Driver 3, LATIN1 is OK , UTF8 is not OK in the two way.

François, true, but 5.1 is multi-byte enable while 3.51 is not. I will have to check HEX value of your French characters and see if they fall outside utf8 bmp (ie. can't be properly moved from utf8 <-> latin1).

There is also a problem with MySQL client protocol which basically is latin1 (thus you can not connect from cl client too using utf8).

Just out of curiosity, why do you need mix of UTF8 and latin1? You can avoid this problem completely by:
  o using just utf8 if you need several charsets in database while choosing different password
  o using just latin1 (and c/ODBC 3.51) if you do not need multiple csets in database
That is exactly why we have 5.1 and 3.51 drivers.

François,

I need the following output from you:

SHOW CREATE TABLE mysql.user\G

SELECT user, HEX(user) as hexu FROM mysql.user\G

To Susanne :
mysql> SHOW CREATE TABLE mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.03 sec)

mysql>
mysql> SELECT user, HEX(user) as hexu FROM mysql.user\G
*************************** 1. row ***************************
user: 9PTCOCTO
hexu: 395054434F43544F
*************************** 2. row ***************************
user: 9PTCOCTOS
hexu: 395054434F43544F53
*************************** 3. row ***************************
user: A08456
hexu: 413038343536
*************************** 4. row ***************************
user: D46513
hexu: 443436353133
*************************** 5. row ***************************
user: FRANÃOIS
hexu: 4652414EC3874F4953
*************************** 6. row ***************************
user: Ithy
hexu: 49746879
*************************** 7. row ***************************
user: J02318
hexu: 4A3032333138
*************************** 8. row ***************************
user: J02318B
hexu: 4A303233313842
*************************** 9. row ***************************
user: NEPTUNE
hexu: 4E455054554E45
*************************** 10. row ***************************
user: NNI000
hexu: 4E4E49303030
*************************** 11. row ***************************
user: SIME
hexu: 53494D45
*************************** 12. row ***************************
user: root
hexu: 726F6F74
*************************** 13. row ***************************
user: root
hexu: 726F6F74
13 rows in set (0.02 sec)

mysql>

To Tonci :
I don't specify charset in usual run . 
I have no need to mix UTF8 and LATIN1 .
I have specified this option just to try to understand why ODBC 3 run and 5 not .
My hope is than ODBC 5 run faster vs 3, not only to treat charset .

François, ODBC runs as it runs... If you ever check the ODBC trace you'll see why it is slow. Now, I see the problem you're hitting but will leave for Susanne to answer.

After some research and discussing with colleagues I can say that there is a problem here, but it looks more like server and connector/c problem. And I gonna file feature request(or is that a bug) about this problem. But there is something to do on c/odbc side to ease users life. Especially since i'm not sure server will change anything in that field soon(if ever). so i'm taking the bug over.
the workaround for this problem would be to set passwords which representation in utf8 is the same as in "native" charset. or in other words - latin symbols, digits, punctuation signs, special symbols. I'm not sure if there is a problem with user names. But I can't exclude that possibility at the moment.

Lawrin, afair you opened cli bug for this very problem. Please add reference.

Server Bug#50675 is the reason of this bug.

Bug still active with new version 5.01.09 odbc/driver .

Two years ago + 1 release of ODBC,
Can anyone say if an hope may still exist to resolve this ?

In other case, to integrate it and to be compatible with existing ODBC , must i do make development to verify and force Password before loggin to mysql ?