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:Microsoft Windows (Vista Pro)
Assigned to: Assigned Account CPU Architecture:Any
Tags: denied, french, login, logon

[8 Jan 2010 13:43] François LHERITIER
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 .
[14 Jan 2010 17:46] Tonci Grgin
Hi François and thanks for your report.

Please try the same with mysql command line client and see if it connects.
[15 Jan 2010 9:26] François LHERITIER
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>
[15 Jan 2010 9:37] Tonci Grgin
François, please try configuring DSN by putting LATIN1 in Details/Misc Options/Character set. Inform me of result.
[15 Jan 2010 10:57] François LHERITIER
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.
[15 Jan 2010 11:28] Tonci Grgin
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.
[15 Jan 2010 11:44] Susanne Ebrecht
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
[18 Jan 2010 8:38] François LHERITIER
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 .
[18 Jan 2010 8:41] Tonci Grgin
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.
[26 Jan 2010 21:14] Lawrenty Novitsky
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.
[19 Feb 2010 8:06] Tonci Grgin
Lawrin, afair you opened cli bug for this very problem. Please add reference.
[3 Mar 2010 12:32] Lawrenty Novitsky
Server Bug#50675 is the reason of this bug.
[11 Jan 2012 10:10] François LHERITIER
Bug still active with new version 5.01.09 odbc/driver .
[16 Jan 2012 9:50] François LHERITIER
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 ?