Description:
Tested on:
Ubuntu 7.10 64bit
FreeBSD 7.0 64bit
an older Ubuntu 64bit
I compiled/installed mysql bk tree as user (not as root). My user name here is myuser. I have this problem with 5.0 and 5.1. I didn't test 6.0.
My explanations are for 5.1 but they are similar for 5.0 (besides using 50 instead of 51 in names):
I compiled by using: BUILD/compile_amd64_debug_max --prefix=/home/myuser/mysql51bk -c
make
make install
on freebsd: gmake and gmake install
after this:
myuser$ cd /home/myuser/mysql51bk
myuser$ mkdir etc
myuser$ cd etc
myuser$ vi my.cnf
here my my.cnf:
[mysqld]
port = 6951
socket = /tmp/mysql51bk.sock
[client]
port = 6951
socket = /tmp/mysql51bk.sock
I started daemon:
myuser$ /home/myuser/mysql51bk/libexec/mysqld --defaults-file=/home/myuser/mysql51bk/etc/my.cnf &
After this I called cli:
myuser$ /home/myuser/mysql51bk/bin/mysql -u root
mysql> grant all on *.* to myuser@'localhost' with grant option;
mysql> grant all on *.* to myuser@'%' with grant option;
mysql> create table testtable;
mysql> \q
myuser$ /home/myuser/mysql51bk/bin/mysql
mysql> show grants;
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' WITH GRANT OPTION
mysql> select user();
myuser@localhost
Of course that ok ... but here comes the bug:
root# adduser testuser
give the testuser a own UID and also a password
myuser$ su - testuser
type password
Now you are testuser.
testuser$ /home/myuser/mysql51bk/bin/mysql
mysql> select user();
myuser@localhost
mysql> show grants;
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' WITH GRANT OPTION
why does it connect as myuser? I didn't say somewhere that it should connect as this user.
I expected ''@localhost as result for select user(); and only GRANT USAGE. I expected that it tries to connect as user "testuser".
When I give a mysql password to myuser and then use at testuser:
testuser$ /home/myuser/mysql51bk/bin/mysql -p
it works when I enter the myuser password.
I don't have a my.cnf at /etc/mysql ... the only my.cnf files I have on my systems are at /home/myuser/mysqlVERSIONbk/etc/
I never told somewhere that it always should use myuser instead of system user without giving a "-u".
I didn't change root user.
Also when there is only root on the system and no other user:
mysql> select user();
myuser@localhost
mysql> show grants;
GRANT USAGE ON *.* TO ''@'localhost'
the same when I start as another user.
It always tries to get the user: myuser, instead of the other system user. Grants are irrelevant here.
I have no idea why. Maybe because the data dir belongs to "myuser" or the daemon belongs to "myuser". But that's just a suggestion.
How to repeat:
see above
Suggested fix:
When there is no "-u" given the system should use the system user that are calling the CLI. Or change the documentation that the system always tries to choose the user who installed the system or whatever.