MySQL password restrictions?

Question

I installed MySQL 5.5 on a fresh VM running Ubuntu 14 LTS through apt-get. During installation, I set a root user password generated by a password manager. After installation, I could not access any functions as the MySQL root user:

<user>@<hostname>:~$ mysql -u root -p
Enter password: <root user pw set at installation>
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Thinking something went wrong setting the root password, I used

<user>@<hostname>:~$ sudo dpkg-reconfigure mysql-server-5.5

to reset the root password, though I "reset" it to the exact same password. I continued to receive the same Access denied error.

I then reset the root password again, using the same method, to something shorter and easily typable (i.e., weak), using only alphanumeric characters. This worked! I can now access MySQL functions as the root user.

It seems like the only difference is the complexity of the password. The original strong password was 18 characters and included punctuation symbols like ^ \ : !, etc. Only ASCII, though, and no whitespaces.

Did this password violate some sort of rule that MySQL passwords must follow, either in terms of length or character sets? I've searched, and I haven't found any references to such restrictions, which I'd think would be well-publicized. If not this, then what else could the problem have been?

Thanks!

Answer 1

There are no restrictions on MySQL passwords imposed by the MySQL software. As far as the database is concerned, they can be as long as you want and as complex as you want. There are some caveats, however.

Sometimes the shell or a script or something will interpret the password before it's passed to the database, and if there are any special characters they may be mangled. For example, if your password is "*" and you passed it via the command line, the shell would interpret that as a wildcard character and fill in a bunch of filenames. You might have to escape special characters like that with a backslash ("\").

Another caveat: if your password were to include non-ASCII characters (Unicode, for example), then whatever is interfacing with the database has to tell the database that that's what's coming. MySQL assumes ASCII and usually has to be told otherwise.

Not sure if either of those applied to your situation. But I figured I'd give you an answer to the question as asked.