mysql special characters in password

Question

I was having trouble with a mysql account, and I eventually narrowed it down to the user's password, which contained the substring '\Y'.

If I create a user with that password, I can't log in with it:

mysql> create user 'test'@'localhost' identified by '\Y';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

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

This happens on two different machines and two different mysql versions.

mysql  Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x86_64) using readline 6.3
mysql  Ver 14.14 Distrib 5.6.19, for osx10.9 (x86_64) using  EditLine wrapper

Any clues why this is happening?

Answer 1

The sequence '\Y' when you created the user is simply 'Y'. So that user's password is "Y".

In MySQL string literals, only certain backslash-codes have any special meaning. "\n" for example is a newline. When you put a backslash before most characters, it has no special meaning, it's just the same literal character. So a "\Y" is simply "Y".

If you want a literal backslash as part of the password, you need to escape the backslash. So to create a password that is the two characters "\Y" you need to:

mysql> create user 'test'@'localhost' identified by '\\Y';

Then when you type the password, use one backslash. Backslash has no meaning at all when typing passwords interactively, so there is no need to escape it.