[SLE] mysql root permission denied

[steve]

Hi everyone. After a clean 8.1 install I do:
mysql_install_db
(no errors)
then
/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: '***@localhost' (Using password: NO)'

I've deleted /var/lib/mysql/mysql contents and reinstalled the rpm. The mysql
manual is OK but I'm not sure how I can create the root account properly.

I know it's been up before but finding it is really hard! Thanks, Steve.

[sjb]

Post by steve
mysql_install_db
(no errors)
then
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin: connect to server at 'localhost' failed

mysqladmin -u root -password='new-password'

or

mysqladmin -u root -p'new password'

(NB, no space between p and password)

hth

sjb

[steve]

Post by sjb

Post by steve
mysql_install_db
(no errors)
then
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin: connect to server at 'localhost' failed

mysqladmin -u root -password='new-password'
or
mysqladmin -u root -p'new password'
(NB, no space between p and password)

Hi.
Both ideas give a list of commands so I think the syntax must be wrong. Any
ideas what it should be? Do I literally type 'new-password' or 'new
password'? anything else gives unknown command.
Thanks, Steve.

[sjb]

Post by steve
Both ideas give a list of commands so I think the syntax must be wrong. Any
ideas what it should be? Do I literally type 'new-password' or 'new
password'? anything else gives unknown command.

Are you literally enclosing the password with single quotes? (You don't
need to, BTW)

sjb

[steve]

Post by sjb

Post by steve
Both ideas give a list of commands so I think the syntax must be wrong.
Any ideas what it should be? Do I literally type 'new-password' or 'new
password'? anything else gives unknown command.

Are you literally enclosing the password with single quotes? (You don't
need to, BTW)
sjb

I've tried all combinations, even writing the password on the command line.
All I get is access denied to ***@localhost. This applies even after a
reinstall of the mysql package. What a pain. Any ideas?

[sjb]

Post by steve
reinstall of the mysql package. What a pain. Any ideas?

The default is no password (or, at least it used to be) .. have you tried

mysqladmin -u root

sjb

[steve]

Post by sjb

Post by steve
reinstall of the mysql package. What a pain. Any ideas?

The default is no password (or, at least it used to be) .. have you tried
mysqladmin -u root
sjb

It gives a list of options and commands only.

[sjb]

Post by steve
It gives a list of options and commands only.

Are you actually giving mysqladmin a command?

mysqladmin -u root -pPASS status

If you're trying to get a command line interface to the database

mysql -u root -pPASS

Is mysqld running?

mysqld -u root -pPASS &

sjb

[steve]

Post by sjb

Post by steve
It gives a list of options and commands only.

Are you actually giving mysqladmin a command?
mysqladmin -u root -pPASS status
If you're trying to get a command line interface to the database
mysql -u root -pPASS
Is mysqld running?
mysqld -u root -pPASS &
sjb

Yes, I started mysql with rcmysql start
Is that the same as the mysqld?

Here is an example:
# mysql -u root -pPASS
ERROR 1045: Access denied for user: '***@localhost' (Using password: YES)

and another:

# mysqladmin -u root -pPASS status
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: '***@localhost' (Using password: YES)'

Does this look good?

[sjb]

Post by steve
Does this look good?

No ;-)

Have you set a password for mysql at all? If you haven't and you try to
use one, you'll see that message.

Try it without the password.

mysqladmin -u root status

sjb

[steve]

Post by sjb

Post by steve
Does this look good?

No ;-)
Have you set a password for mysql at all? If you haven't and you try to
use one, you'll see that message.
Try it without the password.
mysqladmin -u root status
sjb

# mysqladmin -u root status
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: '***@localhost' (Using password: NO)'

As a user I can just type mysql and make tables in the test database no
problem:
~> mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 3.23.52-log

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

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.03 sec)

mysql>

It's just root who can't login. I know it's working because of this. I just
want to be able to get it right before I let my students loose on the 'net
with php. Thanks for your patience. Steve.

[sjb]

Post by steve
As a user I can just type mysql and make tables in the test database no
~> mysql

So what happens if you try

mysqladmin status

sjb

[steve]

Post by sjb

Post by steve
As a user I can just type mysql and make tables in the test database no
~> mysql

So what happens if you try
mysqladmin status
sjb

as a user:
~> mysqladmin status
Uptime: 4925 Threads: 1 Questions: 7 Slow queries: 0 Opens: 7 Flush
tables: 1 Open tables: 1 Queries per second avg: 0.001

as root:
# mysqladmin status
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: '***@localhost' (Using password: NO)'

[sjb]

Post by steve
# mysqladmin status
mysqladmin: connect to server at 'localhost' failed

What do you see in ..

more /etc/mysqlaccess.conf

sjb

[steve]

Post by sjb

Post by steve
# mysqladmin status
mysqladmin: connect to server at 'localhost' failed

What do you see in ..
more /etc/mysqlaccess.conf
sjb

# more /etc/mysqlaccess.conf
# ------------------------------------------------------------------------- #
# MySQLaccess version 2.0p2 #
# (c) ***@rug.ac.be, 1997 #
# #
# *** Configuration file *** #
# #
# -Default values read by mysqlaccess during initialisation. #
# This file is looked for in #
# 1) the current directory #
# 2) /etc/ #
# -Options given on the command-line override the values given in here #
# -Given options can't be overruled by empty/blanc options!! #
# ------------------------------------------------------------------------- #


# ----------------#
# Global settings #
# --------------- #
#$Param{'host'} = '';
$Param{'user'} = 'nobody';
$Param{'db'} = 'test';
$Param{'password'} = 'foobar';
$Param{'debug'} = 0;

# --------------------------#
# Settings for Command-line #
# ------------------------- #
if ($CMD) {
$Param{'superuser'} = 'root';
$Param{'rhost'} = 'localhost';
$Param{'spassword'} = '';
$Param{'brief'} = 1;
}

# ---------------------#
# Settings for CGI-BIN #
# -------------------- #
if ($CGI) {
$Param{'superuser'} = 'root';
$Param{'rhost'} = 'localhost';
$Param{'spassword'} = '';
$Param{'table'} = 1;
}

1; #to make require happy

[sjb]

Post by steve
# more /etc/mysqlaccess.conf

Which is exactly the same as mine .. hmm

OK, try

rcmysql stop
mysqld -u root &
mysqladmin status

.. maybe it's something in the init script that's screwing things.

sjb

[steve]

Post by sjb

Post by steve
# more /etc/mysqlaccess.conf

Which is exactly the same as mine .. hmm
OK, try
rcmysql stop
mysqld -u root &
mysqladmin status
.. maybe it's something in the init script that's screwing things.
sjb

# rcmysql stop
Shutting down service mysql

# mysqld -u root &
[1] 4295
# 021107 14:51:27 mysqld: Table 'mysql.host' doesn't exist
# mysqladmin status
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (111)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock'
exists!

Thanks for your patience. Steve.

[sjb]

Post by steve
# 021107 14:51:27 mysqld: Table 'mysql.host' doesn't exist

Ahh .. now we're getting somewhere.

OK, can you start mysql again

rcmysql start

Then login as an ordinary user

mysql

Then

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc host;
+-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+-----------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

And if you don't see a table def like that above (which you probably
won't) it means that the mysql_install_db probably failed somewhere
along the line.

As for fixing it .. err .. uninstall mysql, then re-install it and (as
root) run the mysql_install_db script again.

sjb

[steve]

Post by sjb

Post by steve
# 021107 14:51:27 mysqld: Table 'mysql.host' doesn't exist

Ahh .. now we're getting somewhere.
OK, can you start mysql again
rcmysql start
Then login as an ordinary user
mysql
Then
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc host;
+-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+-----------------+------+-----+---------+-------+
12 rows in set (0.01 sec)
And if you don't see a table def like that above (which you probably
won't) it means that the mysql_install_db probably failed somewhere
along the line.
As for fixing it .. err .. uninstall mysql, then re-install it and (as
root) run the mysql_install_db script again.

Exactly the same! Let's give up no? If anyone thinks of anything, them please
let me know. THanks. Steve.

[sjb]

Post by steve
Exactly the same! Let's give up no? If anyone thinks of anything, them please
let me know. THanks. Steve.

That's weird .. the mysql database is created by mysql_install_db along
with all the tables.

Sorry .. I don't know what's up with it!

sjb

[Kevin Donnelly]

Post by steve
/usr/bin/mysqladmin: connect to server at 'localhost' failed

My understanding of the problem (and please, someone, correct me if I'm wrong)
is that MySQL views the local physical machine as two virtual machines -
localhost, and hostname (as you can see if you look at the user table of the
mysql database). The normal command that is recommended to set the password
only changes the hostname record, but when you then go to log in, the
localhost one is used by default. That doesn't have a password in the
record, so it is rejected. But it won't accept a blank password either,
since one has been set for that user on the hostname twin! So you're stuck.

What you need to do is specify localhost as the login source, as follows:
mysql -u root -h localhost -p
then give your password. Hopefully that should get you in. (If it does not,
your password is incorrect, and we will need to do another Cunning Trick to
sort that out.)

Once there, run:
update user set password=password("mypassword") where user="root";
then:
delete from user where user="";
(that's a double quote followed by another double quote).
Then exit mysql, and run:
mysqladmin -u root reload
and give your password - this will reload the grant tables you just amended.
Then run:
mysql -uroot -p
and give your root password - you should then be logged in as root.

You have just set BOTH hostname and localhost to the SAME password, so you
don't need to specify localhost from now on. You have also got rid of the
blank test user, which is a security risk.

This comes up so often I think it should be put on the SuSE support db
somewhere. Something like:
Immediately after install of MySQL, run:
mysql
and it will allow you to log in without a password. Now set the password for
root by running:
update etc, as above.

This would save an awful lot of hair-pulling.

HTH

Kevin

[p***@cmdline.net]

Post by steve
mysql_install_db
(no errors)

Did you do
mysql_install_db --user mysql
?

Anyway, you shouldn't have done it in the first place since the init
script /etc/init.d/mysql does that for you when you start it for the
first time (rcmysql start).

I don't know but there is a remote possibility that if you did things by
hand you missed a step or did it in a different way than the system
expects it. Starting mysql by the init script is all you need to do to
get it running (and set the admin password as the init script tells you.

BTW, a correct way to _initially_ set the password (i.e. while there
is an empty password) is:

/usr/bin/mysqladmin -u root password 'secret'

After that, you need to use

/usr/bin/mysqladmin -u root -p password 'anotherpassword'

if you want to change it, and enter the current password on the tty.
Everything else will give you the 'Access denied for user:
'***@localhost' message.

Peter

[steve]

Post by p***@cmdline.net

Post by steve
mysql_install_db
(no errors)

Did you do
mysql_install_db --user mysql
?
Anyway, you shouldn't have done it in the first place since the init
script /etc/init.d/mysql does that for you when you start it for the
first time (rcmysql start).
I don't know but there is a remote possibility that if you did things by
hand you missed a step or did it in a different way than the system
expects it. Starting mysql by the init script is all you need to do to
get it running (and set the admin password as the init script tells you.
BTW, a correct way to _initially_ set the password (i.e. while there
/usr/bin/mysqladmin -u root password 'secret'
After that, you need to use
/usr/bin/mysqladmin -u root -p password 'anotherpassword'
if you want to change it, and enter the current password on the tty.
Peter

Hi. Thanks. That did it. In other words following the (awful) mysql
documentation and actually doing what it says messes you up. Had I not done
the mysql_install_db first then it would have worked. I should have read the
readme_suse doc instead. Anyway, I've now got root access and can create
databases once more.