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: | |
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 |
[8 Jan 2010 13:43]
François LHERITIER
[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 ?