Access denied for user 'root'@'localhost' to database SOMEDB

[Thomas]

When I try to give my user xy rights to database xy I receive the access denied message:

mysql> CREATE USER 'testdb1'@'localhost' IDENTIFIED BY 'testpw';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON * . * TO 'testdb1'@'localhost' IDENTIFIED BY 'testpw' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE `testdb1` ;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `testdb1` . * TO 'testdb1'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'testdb1'

I restored the complete database from an mysql-server-5.0 5.0.32-7etch12 which I created with

mysqldump --comments --routines --opt --all-databases --flush-logs --user=debian-sys-maint --password=XYZ

I searched the web and found only this two reports which the same problem, but without an solution:

http://forums.mysql.com/read.php?10,175497,175497
http://forums.mysql.com/read.php?11,302793,302793

But I think it is an bug in mysql, because I should handle it correctly

ProblemType: Bug
Architecture: amd64
Date: Sat Mar 20 10:36:14 2010
DistroRelease: Ubuntu 10.04
Logs.var.log.daemon.log:

Logs.var.log.kern.log:
 Mar 16 22:28:46 s01 kernel: [298533.765967] type=1505 audit(1268778526.035:11): operation="profile_replace" pid=28473 name="/usr/sbin/mysqld"
 Mar 16 22:28:46 s01 kernel: [298533.953508] type=1505 audit(1268778526.223:16): operation="profile_replace" pid=28511 name="/usr/sbin/mysqld"
 Mar 18 07:31:05 s01 kernel: [ 15.121192] type=1505 audit(1268893865.748:9): operation="profile_load" pid=960 name="/usr/sbin/mysqld"
MySQLVarLibDirListing: ['ibdata1', 'OOO', 'web3aps1', 'usr_web1_1', 'sandrassql1', 'debian-5.1.flag', 'brain', 'claudiabsql1', 'lalasql3', 'bbb', 'XXnix', 'roundcube', 'hsvkirchhsql2', 'soundpack', 'web3aps2', 'ililsql1', 'roundcubemail', 'hochzeit_cj', 'mysql', 'lalasql2', 'teamspeak', 'dune@002drockmusic', 'syscp@002eold', 'ib_logfile1', 'phpmyadmin', 'ts3db', 'schwarzhsql1', 's01.pid', 'projekte', 'rdsport', 'hsvkirchhsql1', 'snort_db', 'syscp', 'wowo', 'tws@002dclan', 'lalasql1', 'testdb1', 'lalasql4', 'mysql_upgrade_info', 'karincsql2', 'ib_logfile0', 'gogo', 'rockihsql1', 'apachelogs', 'karincsql1']
Package: mysql-server 5.1.41-3ubuntu7
PackageArchitecture: all
ProcEnviron:
 LANG=de_DE.UTF-8
 SHELL=/bin/bash
ProcVersionSignature: Ubuntu 2.6.32-16.25-generic
SourcePackage: mysql-dfsg-5.1
Uname: Linux 2.6.32-16-generic x86_64

EDIT: Changed private DB names from MySQLVarLibDirListing

[Chuck Short (zulcss)]

Can you attach your /var/log/daemon.log, please?

Thanks
chuck

[Thomas (t.c)]

I restarted mysql damon und repeat the steps i have written ... but thats all what is in daemon.log

Mar 24 01:17:20 s01 /etc/mysql/debian-start[31437]: Upgrading MySQL tables if necessary.
Mar 24 01:17:20 s01 /etc/mysql/debian-start[31440]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Mar 24 01:17:20 s01 /etc/mysql/debian-start[31440]: Looking for 'mysql' as: /usr/bin/mysql
Mar 24 01:17:20 s01 /etc/mysql/debian-start[31440]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Mar 24 01:17:20 s01 /etc/mysql/debian-start[31440]: This installation of MySQL is already upgraded to 5.1.41, use --force if you still need to run mysql_upgrade
Mar 24 01:17:20 s01 /etc/mysql/debian-start[31447]: Checking for insecure root accounts.
Mar 24 01:17:20 s01 /etc/mysql/debian-start[31451]: Triggering myisam-recover for all MyISAM tables

[Thomas (t.c)]

mysql.log and mysql.err is empty

[Mathias Gug (mathiaz)]

I'm not able to reproduce your problem on a fresh lucid install:

$ mysql -u root -pmypwd
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.1.41-3ubuntu9 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'testdb1'@'localhost' IDENTIFIED BY 'testpw';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON * . * TO 'testdb1'@'localhost' IDENTIFIED BY 'testpw' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE `testdb1` ;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `testdb1` . * TO 'testdb1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Could you check the privileges that the root user has?

[Chuck Short (zulcss)]

We'd like to figure out what's causing this bug for you, but we haven't heard back from you in a while. Could you please provide the requested information? Thanks!

[Thomas (t.c)]

root has all privileges. I can also give the pack the complete /var/lib/mysql/ folder and give them to somebodys mail adress. But dont like it, because of sensive data.

I currently install a up to date package and try again

[Chuck Short (zulcss)]

The /var/lib/mysql directory is suppose to have mysql:mysql permissions so thats probably what your problem is.

[Thomas (t.c)]

no, thats not the problem

ls -lah /var/lib/mysql/
insgesamt 157M
drwx------ 41 mysql mysql 4,0K 2010-04-19 17:38 .
drwxr-xr-x 63 root root 4,0K 2010-04-19 17:10 ..
drwx------ 2 mysql mysql 4,0K 2010-03-20 09:19 alientxcsql1
drwx------ 2 mysql mysql 4,0K 2010-03-13 10:59 alientxcsql2
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:01 alientxcsql3
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:01 alientxcsql4
drwx------ 2 mysql mysql 4,0K 2010-03-18 12:14 apachelogs
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:01 bbb
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:01 brain
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:01 claudiabsql1
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:02 deathkeeper
-rw-r--r-- 1 mysql mysql 0 2010-04-19 17:10 debian-5.1.flag
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:02 dune@002drockmusic
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:02 gehirn
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:02 hochzeit_cj
drwx------ 2 mysql mysql 12K 2010-03-13 11:02 hsvkirchhsql1
drwx------ 2 mysql mysql 4,0K 2010-04-20 21:02 hsvkirchhsql2
-rw-rw---- 1 mysql mysql 146M 2010-04-21 15:34 ibdata1
-rw-rw---- 1 mysql mysql 5,0M 2010-04-21 15:34 ib_logfile0
-rw-rw---- 1 mysql mysql 5,0M 2010-03-19 23:10 ib_logfile1
drwx------ 2 mysql mysql 12K 2010-03-13 11:02 karincsql1
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:02 karincsql2
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:02 meefisko
drwx------ 2 mysql mysql 4,0K 2010-04-19 17:10 mysql
-rw-rw---- 1 mysql mysql 6 2010-03-13 01:21 mysql_upgrade_info
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:02 phpmyadmin
drwx------ 2 mysql mysql 20K 2010-03-13 11:02 projekte
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:02 raeschnix
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:02 rdsport
drwx------ 2 mysql mysql 20K 2010-03-13 11:03 rockihsql1
drwx------ 2 mysql mysql 12K 2010-03-18 02:14 RoiderZsql1
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:46 roundcube
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:03 roundcubemail
-rw-rw---- 1 mysql mysql 4 2010-04-19 17:38 s01.pid
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:03 sandrassql1
drwx------ 2 mysql mysql 52K 2010-03-24 15:37 schwarzhsql1
drwx------ 2 mysql mysql 4,0K 2010-03-13 15:04 snort_db
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:03 soundpack
drwx------ 2 mysql mysql 12K 2010-03-18 21:06 syscp
drwx------ 2 mysql mysql 12K 2010-03-13 15:22 syscp@002eold
drwx------ 2 mysql mysql 4,0K 2010-03-14 20:08 teamspeak
drwx------ 2 mysql mysql 4,0K 2010-03-24 01:20 testdb2
drwx------ 2 mysql mysql 4,0K 2010-03-24 00:18 ts3db
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:04 tws@002dclan
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:04 usr_web1_1
drwx------ 2 mysql mysql 4,0K 2010-03-13 11:04 web3aps1
drwx------ 2 mysql mysql 4,0K 2010-03-19 22:56 web3aps2

ls -lah /var/lib/mysql/mysql
insgesamt 868K
drwx------ 2 mysql mysql 4,0K 2010-04-19 17:10 .
drwx------ 41 mysql mysql 4,0K 2010-04-19 17:38 ..
-rw-rw---- 1 mysql mysql 8,7K 2010-03-13 11:02 columns_priv.frm
-rw-rw---- 1 mysql mysql 11K 2010-03-18 20:25 columns_priv.MYD
-rw-rw---- 1 mysql mysql 8,0K 2010-03-18 20:29 columns_priv.MYI
-rw-rw---- 1 mysql mysql 9,3K 2010-03-13...

[Jon Watte (5-launchpad-net-mindcontrol-org)]

I have a very similar problem. I'm running Ubuntu 10.04 LTS with mysql 5.1.
I have not created any users other than the defaults.
Server version: 5.1.41-3ubuntu12.1 (Ubuntu)

I can do this:
mysql
mysql> create database foobar;

I can't do this:
mysql> grant create temporary tables on *.* to public;
ERROR 1045 (28000): Access denied for user ''@'localhost' (using password: NO)

Here are all grants:

mysql> show grants;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for @localhost |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO ''@'localhost' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Something seems to be somewhat off in the packet setup, though:

# grep -C2 mysql /var/log/daemon.log:

Jul 7 16:37:18 jwatte-hello-ubuntu init: apport post-stop process (883) terminated with status 1
Jul 7 16:37:18 jwatte-hello-ubuntu acpid: waiting for events: event logging is off
Jul 7 16:37:18 jwatte-hello-ubuntu /etc/mysql/debian-start[917]: Upgrading MySQL tables if necessary.
Jul 7 16:37:18 jwatte-hello-ubuntu /etc/mysql/debian-start[920]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Jul 7 16:37:18 jwatte-hello-ubuntu /etc/mysql/debian-start[920]: Looking for 'mysql' as: /usr/bin/mysql
Jul 7 16:37:18 jwatte-hello-ubuntu /etc/mysql/debian-start[920]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jul 7 16:37:18 jwatte-hello-ubuntu /etc/mysql/debian-start[920]: Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock'
Jul 7 16:37:18 jwatte-hello-ubuntu /etc/mysql/debian-start[920]: /usr/bin/mysqlcheck: Got error: 1045: Access denied for user 'debian-sys-maint'@'localhost' (using password: YES) when trying to connect
Jul 7 16:37:18 jwatte-hello-ubuntu /etc/mysql/debian-start[920]: FATAL ERROR: Upgrade failed
Jul 7 16:37:18 jwatte-hello-ubuntu /etc/mysql/debian-start[929]: Checking for insecure root accounts.
Jul 7 16:37:18 jwatte-hello-ubuntu acpid: client connected from 934[0:0]
Jul 7 16:37:18 jwatte-hello-ubuntu acpid: 1 client rule loaded

[Clint Byrum (clint-fewbar)]

We are undergoing a big change in the mysql 5.1 packages for lucid right now due to the undisclosed security problems released by Oracle. Can somebody affected please try this after upgrading to the 5.1.61 packages that are in lucid-proposed?

Thanks!

[Clint Byrum (clint-fewbar)]

In looking at this closer, I believe this is a bug in the debian-start script.

Because pipes mask errors in the left-most commands (only the last command in pipes have errors detected), we're not detecting the problem with the mysql upgrade process, leading to the error you see in comment #10.

Deeper inspection reveals that the way the debian-sys-maint user is created is really probably the issue.

Marking as Confirmed, importance Medium. This only seems to affect a few users, but probably makes things fairly frustrating for those users.

[Peter Giles (g1l3sp)]

Clint, is there a way to fix this for those of us in this unpleasant situation?

[Peter Giles (g1l3sp)]

I got it fixed, thanks to post #4 on this thread: http://ubuntuforums.org/showthread.php?t=1836919

Start your mysql client as shown below:

    mysql -u debian-sys-maint -p

In another terminal, 'cat' the file /etc/mysql/debian.cnf (you'll have to 'sudo cat' it). That file contains a password; paste that password in the first terminal when prompted for it. Then execute the following commands at the 'mysql>' prompt:

    grant all privileges on *.* to 'root'@'localhost' identified by 't1g3r' with grant option;
    flush privileges;

That did it for me!

[Peter Giles (g1l3sp)]

I put a password in that last comment like a complete idiot. Well, I've changed it, and if you try to follow along you'll want to substitute 't1g3r' in the above grant command to whatever you want your root mysql password to be.