MySQL Bugs: #24620: username containing dash cannot login

Bug #24620	username containing dash cannot login
Submitted:	27 Nov 2006 12:43	Modified:	29 Nov 2006 14:09
Reporter:	Mircea LUTIC	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.27-community-nt	OS:	Windows (Win XP Pro [5.1.2600])
Assigned to:		CPU Architecture:	Any

Description:
From the command line client

mysql>create user "Foo-Bar" identified by 'p';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

mysql -u Foo-Bar -p
ERROR 1045 (28000): Access denied for user 'Foo-Bar'@'localhost' (using password: YES)

Connection is also refused from ASP/ODBC.

How to repeat:
From the command line client

mysql>create user "Foo-Bar" identified by 'p';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

mysql -u Foo-Bar -p
ERROR 1045 (28000): Access denied for user 'Foo-Bar'@'localhost' (using password: YES)

Connection is also refused from ASP/ODBC.

Suggested fix:
allow login

Thank you for a problem report. As you set pasword 'p', you have to provide it in the command line. use:

mysql -u Foo-Bar -pp

It works for me with 5.0.27 on Windows XP. So, it is not a bug.

it does not work for me with either the password entered on the command line or at the "Enter password:" prompt. 
Note that my windows login is not administrator but a restricted account.
I don't think this should have any implication on mysql.
What else should I check?

Mircea.

I was unable to repeat:

C:\dbs\5.0\bin>mysql -uroot -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.27-community-nt

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

mysql> create user "Foo-Bar" identified by 'p';
Query OK, 0 rows affected (0.30 sec)

mysql> exit
Bye

C:\dbs\5.0\bin>mysql -uFoo-Bar -p -P3307
Enter password: *
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.27-community-nt

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

mysql>

My (windows) installation allows anonymous login so 

c:\>mysql -uFoo-Bar

goes right through. However, 

c:\>mysql -uFoo-Bar -p
Enter password: *
ERROR 1045 (28000): Access denied for user 'Foo-Bar'@'localhost' (using password: YES)

and

c:\>mysql -uFoo-Bar -pp
ERROR 1045 (28000): Access denied for user 'Foo-Bar'@'localhost' (using password: YES)

My installation is on d:\bin\mysql.
I removed all comments from d:\bin\mysql\my.ini and here is what it contains:
[client]
port=3306

[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir="D:/bin/mysql/"
datadir="D:/bin/mysql/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=5M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=212K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=10M
innodb_thread_concurrency=8

Please, send the results of:

select user, host from mysql.user;

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
|                  | %         |
| A-B              | %         |
| A_B              | %         |
| DGI-VENICE-VOTA_ | %         |
| Foo-Bar          | %         |
|                  | localhost |
| root             | localhost |
+------------------+-----------+
7 rows in set (0.03 sec)

mysql>

This is expected behavior. See
http://dev.mysql.com/doc/refman/5.0/en/access-denied.html
starting from the words "A very common error is"

mysql> update mysql.user set host='localhost' where user='Foo-Bar';
Query OK, 1 row affected (0.33 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
|                  | %         |
| A-B              | %         |
| A_B              | %         |
| DGI-VENICE-VOTA_ | %         |
|                  | localhost |
| Foo-Bar          | localhost |
| root             | localhost |
+------------------+-----------+
7 rows in set (0.02 sec)

Now the setting Foo-Bar@localhost is most specific and it still doesn't work:

15:50:03,01>mysql -uFoo-Bar -pp
ERROR 1045 (28000): Access denied for user 'Foo-Bar'@'localhost' (using password: YES)

Besides, even if this fix worked, there still is an error.
The "Create user" statement should insert into mysql.user something that can be used for login. I mean the following sequence should ALWAYS work.
Particularly so on a freshly installed system and regardless of whether the system allows anonymous login or not.

Create user somename identified by 'pass';
exit;
mysql -usomename -ppass

I did some more deletions from mysql.user and then:

mysql> create user "A-B" identified by 'p';
ERROR 1396 (HY000): Operation CREATE USER failed for 'A-B'@'%'
mysql> create user "A-C" identified by 'p';
Query OK, 0 rows affected (0.06 sec)

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| A-C     | %         |
| Foo-Bar | localhost |
| root    | localhost |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> exit
Bye

C:\Documents and Settings\mircea.lutic
15:59:17,94>mysql -uA-C -pp
ERROR 1045 (28000): Access denied for user 'A-C'@'localhost' (using password: YES)

C:\Documents and Settings\mircea.lutic
15:59:24,32>mysql -u"A-C" -pp
ERROR 1045 (28000): Access denied for user 'A-C'@'localhost' (using password: YES)

The dash doesn't change anything though - I get the same behaviour for 
create user me identified by password 'p';

sorry - I meant
create user me identified by  'p';
(w/o the password keyword)
it doesn't work