Tuesday, March 24, 2009

MySQL: frustration with login at the command prompt or the space character mystery

Many years ago when trying to use MySQL I ran into this, but only get to note it down now because someone else run into this problem. I searched and lo and behold not much info out there, so I thought maybe a couple of pointers would be worth while.
When using the command line mysql tool you can specify the username and password of the user during login.
Many manuals describe the syntax as:

mysql -h hostname -u root -p password
or
mysql --user=user_name --password=your_password db_name

You think wow this is easy, right. I can interpret this very simply.
I go to the command prompt and type:

C:\>mysql --user=root --pasword=mypass

but to my astonishment I would get:

mysql: unknown variable 'pasword=mypass'

Here are couple of other beauties that are in the manuals and don't work at all like expected:

C:\>mysql -u root -p mypass
Enter password: ******
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

This could be very frustrating to someone new to MySQL on windows. Can't even login to get the client program started.

The approach I found working for windows based installation is to ignore the manual and avoid space between parameters.
Thus rather than using -u root, you will need to use -uroot. This will magically work. E.g.:
C:\>mysql -uroot -pmypass
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19Server version: 5.0.67-community-nt
MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>


Hope this will avoid some frustration out there.
Cheers,

1 comment:

SMS said...

Thanks man... came in handy.