The Problem<\/p>\n
The logfile of mysqld, \/var\/log\/upstart\/mysql.log, reported yet another error: <\/p>\n
?120618 14:07:31 [Note] \/usr\/sbin\/mysqld: ready for connections.<\/p>\n
Version: ‘5.5.24-0ubuntu0.12.04.1’ socket: ‘\/var\/run\/mysqld\/mysqld.sock’ port: 3306 (Ubuntu)<\/p>\n
mysqld is alive<\/p>\n
Checking for tables which need an upgrade, are corrupt or were<\/p>\n
not closed cleanly.<\/p>\n
120618 14:07:36 [ERROR] Cannot find or open table nova\/projects from<\/p>\n
the internal data dictionary of InnoDB though the .frm file for the<\/p>\n
table exists. Maybe you have deleted and recreated InnoDB data<\/p>\n
This looked like a DB corruption. Assuming healing it will solve the problem I wasted a few hours on that, in vain. Finally it turned this is a harmless alert that has nothing to do with the Access Denied issue (but is probably a nova bug).<\/p>\n
Some posts on the subject suggested that the socket permissions prevented local access. In my installation:<\/p>\n
# ll \/var\/run\/mysqld\/mysqld.sock
\nsrwxrwxrwx 1 mysql mysql 0 Jun 18 17:34 \/var\/run\/mysqld\/mysqld.sock<\/p>\n
Which is ok. <\/p>\n
Back to mysql’s user accounts.<\/p>\n
User ‘glance’ was properly defined in mysql, as well as ‘keystone’ and ‘nova’. Permissions and grants looked ok – and let’s recall openstack worked (!). Connecting from any remote host (same command as above, with –host of course) worked fine. The glance daemons glance-api and glance-registry weren’t the only services imapcted by error 1045: keystone and nova had the same issue, and their respective log files (under \/var\/log\/upstart) had thousands of lines of the OperationalError quoted above (which is due to the fact openstack’s upstart jobs have a very “slim” logic).<\/p>\n
All that suggested that there was something special about ‘localhost’ access to mysql. <\/p>\n
I use ‘etckeeper’ to keep a log and trace of what’s getting installed and modified. Comparing log timestamps and git changes, I concluded that the mess was caused by a simple modification to \/etc\/hosts: the yellow line below, which is a step in the installation procedure, created the havoc:<\/p>\n
?127.0.0.1\tlocalhost<\/p>\n
127.0.1.1\tostk-controller1<\/p>\n
10.0.0.40 ostk-controller1<\/p>\n
10.0.0.41 ostk-nova1<\/p>\n
A Poor Workaround<\/p>\n
Putting this yellow line in comment provided a poor workaround: it solved mysql ERROR 1045, but the controller must have its hostname resolved in \/etc\/hosts so we’re not satisfied.<\/p>\n
Further reading suggested that the error has something to do with the way mysql interprets ‘%’ in statements such as: <\/p>\n
GRANT USAGE ON *.* TO ‘glance’@’%’ IDENTIFIED BY PASSWORD(‘openstack’);<\/p>\n
Is it possible that ‘%’ stands for “all hosts except localhost”? i read this more than once (see References below) but could hardly belive. Interestingly, the MySQL documentation isn’t clear about this question and that’s the reason, i guess, there’s so much confusion and so many posts related to ERROR 1045.<\/p>\n
It’s worth noting that the hint to the final answer was found in a post with the title “Any way to make anyhost ‘%’ include localhost”…<\/p>\n
Understanding MySQL Access<\/p>\n
I’ve set up a separate VM to explore that, and here are my findings.<\/p>\n
After installing mysql-server (latest for Precise is 5.5.24), the USER table and GRANTS get the default settings listed below (for clarity i’ve cut the right side of the output so it doesn’t look exactly as on screen):<\/p>\n
[14:57:22]root@mysqltests[~]
\n# mysql -u root -p
\n. . .
\nServer version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)
\n. . .<\/p>\n
mysql> SELECT user,host,password FROM mysql.user;
\n+——————+————+————————-+
\n| user | host | password |
\n+——————+————+————————-+
\n| root | localhost | *77B48D6366D102139D3719 |
\n| root | mysqltests | *77B48D6366D102139D3719 |
\n| root | 127.0.0.1 | *77B48D6366D102139D3719 |
\n| root | ::1 | *77B48D6366D102139D3719 |
\n| | localhost | |
\n| | mysqltests | |
\n| debian-sys-maint | localhost | *04D30B480932109EFD77E1 |
\n+——————+————+————————-+
\n7 rows in set (0.00 sec)<\/p>\n
mysql> show grants;
\n+———————————————————+
\n| Grants for root@localhost |
\n+———————————————————+
\n| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ |<\/p>\n
| IDENTIFIED BY PASSWORD ‘*77B48D6366D102139D3719’ |<\/p>\n
| WITH GRANT OPTION |
\n| GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT |<\/p>\n
| OPTION |
\n+———————————————————+
\n2 rows in set (0.00 sec)<\/p>\n
The mysql.user Table<\/p>\n
At first glance, we have 2 users (root and debian-sys-maint). That’s wrong, because mysql’s “user” is a ‘user’@’host’ pair association. So we have 7 in total: ‘root’ is defined (with the same password) for any combination of ‘localhost’ (the first 4 lines), then we have 2 strange lines with empty username, and finally the debian backdoor ‘debian-sys-maint’. <\/p>\n
The grants<\/p>\n
The ‘show grants’ above shows only grants for ‘root’. But if we run the next staement, we see what access is provided to any user connecting from ‘localhost’:<\/p>\n
mysql> show grants for ”@’localhost’;
\n+————————————–+
\n| Grants for @localhost |
\n+————————————–+
\n| GRANT USAGE ON *.* TO ”@’localhost’ |
\n+————————————–+<\/p>\n
Which (indirectly) explains why running this command (as Linux user ‘ori’) doesn’t require a password: <\/p>\n
[16:16:57]ori@mysqltests[~]<\/p>\n
$ mysqladmin ping
\nmysqld is alive<\/p>\n
Where this one fails:<\/p>\n
[16:14:59]ori@mysqltests[~]
\n$ mysqladmin -uroot ping
\nmysqladmin: connect to server at ‘localhost’ failed
\nerror: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’<\/p>\n
Honestly, in the beginning i thought there’s some balck magic here related to the user (‘ori’, in this case) defined during ubuntu installation, or a special Linux group memebership, or some apparmor profile or god-knows what else. <\/p>\n
But there’s no black magic after all, and it’s all inside mysql:<\/p>\n
The first thing to bear in mind is that the empty USER field ” is a wildcard, same as ‘%’ for host. <\/p>\n
The second is that mysql prefers the explicit match over the wildcard. For example, user ‘root’ can match either [1] the explicit ‘root’@localhost’ row or [2] the wildcard ”@’localhost’ row. Since there’s an explicit entry for [1] in the table mysql.user, it’ll be used. This in turn requires a password so when i try to connect as ‘root’ without a password i’m rejected.<\/p>\n
When i connect as ‘ori’ – which isn’t even a mysql user, there’s only one possible match – ”@’localhost’ and this line in the table doesn’t have a password.<\/p>\n
This nicely explains why the above mysqladmin command works for ‘ori’ and fails for ‘root’. <\/p>\n
To sum it up: mysql controls access (or connection request) based on the USER table. Which user, from which host and whether a password is required. <\/p>\n
Once connected, the GRANTS determine what the user is allowed to do. When connected as ‘ori’ i’m limited to “USAGE” (e.g. check if server is up, what version and the like of inoffensive commands). <\/p>\n
So far so good – but why ‘glance’@’localhost’ is denied access on the OpenStack controller?<\/p>\n
When the static IP address of the conroller wasn’t in \/etc\/hosts (or after it was commented-out), there was only one match for ‘glance’ = ‘glance’@’%’<\/p>\n
This, in turn, comes from the connection string (in \/etc\/glance\/glance-registry.conf) which is:<\/p>\n
sql_connection = mysql:\/\/glance:openstack@10.0.0.40\/glance<\/p>\n
It specifies user, password and host.<\/p>\n
The line I’ve added for 10.0.0.40 in \/etc\/hosts, told mysql (indirectly) that host ‘ostk-controller1’ is actually ‘localhsot’. From now on, there are 2 possible matches for ‘glance’, and the one picked by mysql is ”@’localhost’. This row, however, doesn’t require a password – which the sql_connection string provide.<\/p>\n
And that’s why all OpenStack services couldn’t connect to mysql.<\/p>\n
Check against the USER table below, this was taken from ostk-controller (not the test VM):<\/p>\n
mysql> SELECT user,host,password FROM mysql.user;
\n+——————+——————+————————-+
\n| user | host | password |<\/p>\n
+——————+——————+————————-+
\n| root | localhost | *3A4A03AC22526F6B591010 |<\/p>\n
| root | ostk-controller1 | *3A4A03AC22526F6B591010 |<\/p>\n
| root | 127.0.0.1 | *3A4A03AC22526F6B591010 |<\/p>\n
| root | ::1 | *3A4A03AC22526F6B591010 |
\n| | localhost | |<\/p>\n
| | ostk-controller1 | |
\n| debian-sys-maint | localhost | *F714636CE8A7836873F7C8 |
\n| nova | % | *3A4A03AC22526F6B591010 |
\n| glance | % | *3A4A03AC22526F6B591010 |
\n| keystone | % | *3A4A03AC22526F6B591010 |
\n+——————+——————+————————-+
\n10 rows in set (0.00 sec)<\/p>\n
Solution for ERROR 1045<\/p>\n
After understanding why, let’s improve on the poor workaround.<\/p>\n
I’d like to credit an answer by Paul DuBois from 2004 for this solution(it’s worth noting that the subject was “Re: Any way to make anyhost ‘%’ include localhost”).<\/p>\n
Borrowing from there, here’s the remedy:<\/p>\n
in MySQL:<\/p>\n
mysql -uroot -p<\/p>\n
DELETE FROM mysql.user WHERE Host=’localhost’ AND User=”;<\/p>\n
DELETE FROM mysql.user WHERE Host=’ostk-controller1′ AND User=”;<\/p>\n
FLUSH PRIVILEGES;<\/p>\n
in \/etc\/hosts:<\/p>\n
Replace the line<\/p>\n
127.0.1.1 ostk-controller1<\/p>\n
by this one:<\/p>\n
10.0.0.40 ostk-controller1<\/p>\n
Quoting from Debian’s reference manual:<\/p>\n
For a system with a permanent IP address, that permanent IP address should be used here instead of 127.0.1.1<\/p>\n
finally restart networking and mysqld – or simply reboot.<\/p>\n
A Second Solution<\/p>\n
Months after going through the above study, i found out why some OpenStack installations don’t hit this issue; The keystone installation instructions (from Ubuntu, for Essex, can be found here) create each OSTK user in mysql twice, as in: <\/p>\n
mysql> CREATE DATABASE keystone;
\nCREATE USER \u2018keystone\u2019@\u2019localhost\u2019 IDENTIFIED BY \u2018Secret_pass\u2019;
\nGRANT ALL PRIVILEGES ON keystone.* TO ‘keystone\u2019@\u2019localhost\u2019
\nWITH GRANT OPTION;
\nCREATE USER \u2018keystone\u2019@\u2019%\u2019 IDENTIFIED BY \u2018Secret_pass\u2019;
\nGRANT ALL PRIVILEGES ON keystone.* TO \u2018keystone\u2019@\u2019%\u2019
\nIDENTIFIED BY \u2018Secret_pass\u2019;
\nFLUSH PRIVILEGES;<\/p>\n
mysql -u root -p <\/p>\n
CREATE USER ‘bill’@’%’ IDENTIFIED BY ‘passpass’;<\/p>\n
grant all privileges on *.* to ‘bill’@’%’ with grant option;<\/p>\n
mysql -u bill -p<\/p>\n
ERROR 1045 (28000): Access denied for user ‘bill’@’localhost’ (using password: YES)<\/p>\n
CREATE USER bill@localhost IDENTIFIED BY ‘passpass’;
\ngrant all privileges on *.* to bill@localhost with grant option;<\/p>\n
If you want to connect remotely, you must specify either the DNS name, the public IP, or 127.0.0.1 using TCP\/IP:<\/p>\n
mysql -u bill -p -hmydb@mydomain.com
\nmysql -u bill -p -h10.1.2.30
\nmysql -u bill -p -h127.0.0.1 –protocol=TCP<\/p>\n
SELECT USER(),CURRENT_USER();<\/p>\n
mysql> select user,host from mysql.user;
\n+———+———–+
\n| user | host |
\n+———+———–+
\n| lwdba | % |
\n| mywife | % |
\n| lwdba | 127.0.0.1 |
\n| root | 127.0.0.1 |
\n| lwdba | localhost |
\n| root | localhost |
\n| vanilla | localhost |
\n+———+———–+
\n7 rows in set (0.00 sec)<\/p>\n
mysql> grant all on *.* to x@’%’;
\nQuery OK, 0 rows affected (0.02 sec)<\/p>\n
mysql> select user,host from mysql.user;
\n+———+———–+
\n| user | host |
\n+———+———–+
\n| lwdba | % |
\n| mywife | % |
\n| x | % |
\n| lwdba | 127.0.0.1 |
\n| root | 127.0.0.1 |
\n| lwdba | localhost |
\n| root | localhost |
\n| vanilla | localhost |
\n+———+———–+
\n8 rows in set (0.00 sec)<\/p>\n
mysql> update mysql.user set user=” where user=’x’;
\nQuery OK, 1 row affected (0.00 sec)
\nRows matched: 1 Changed: 1 Warnings: 0<\/p>\n
mysql> flush privileges;
\nQuery OK, 0 rows affected (0.01 sec)<\/p>\n
mysql> select user,host from mysql.user;
\n+———+———–+
\n| user | host |
\n+———+———–+
\n| | % |
\n| lwdba | % |
\n| mywife | % |
\n| lwdba | 127.0.0.1 |
\n| root | 127.0.0.1 |
\n| lwdba | localhost |
\n| root | localhost |
\n| vanilla | localhost |
\n+———+———–+
\n8 rows in set (0.00 sec)<\/p>\n
mysql><\/p>\n
~$ mysql -u root -p
\nEnter Password:<\/p>\n
mysql> grant all privileges on *.* to bill@localhost identified by ‘pass’ with grant option;<\/p>\n
root@myhost:\/home\/mysql-5.5.16-linux2.6-x86_64# .\/mysql -ubill -ppass –socket=\/tmp\/mysql-5.5.sock
\nWelcome to the MySQL monitor. Commands end with ; or \\g.
\nYour MySQL connection id is 7
\nServer version: 5.5.16 MySQL Community Server (GPL)<\/p>\n
Type ‘help;’ or ‘\\h’ for help. Type ‘\\c’ to clear the buffer.<\/p>\n
mysql> SELECT user, host FROM mysql.user;
\n+——+———–+
\n| user | host |
\n+——+———–+
\n| bill | % |
\n| root | 127.0.0.1 |
\n| root | ::1 |
\n| root | localhost |
\n+——+———–+
\n4 rows in set (0.00 sec)<\/p>\n
mysql> SELECT USER(), CURRENT_USER();
\n+—————-+—————-+
\n| USER() | CURRENT_USER() |
\n+—————-+—————-+
\n| bill@localhost | bill@% |
\n+—————-+—————-+
\n1 row in set (0.02 sec)<\/p>\n
mysql> SHOW VARIABLES LIKE ‘skip_networking’;
\n+—————–+——-+
\n| Variable_name | Value |
\n+—————–+——-+
\n| skip_networking | ON |
\n+—————–+——-+
\n1 row in set (0.00 sec)<\/p>\n
mysql><\/p>\n","protected":false},"excerpt":{"rendered":"
The Problem<\/p>\n
The logfile of mysqld, \/var\/log\/upstart\/mysql.log, reported yet another error: <\/p>\n
?120618 14:07:31 [Note] \/usr\/sbin\/mysqld: ready for connections.<\/p>\n
Version: ‘5.5.24-0ubuntu0.12.04.1’ socket: ‘\/var\/run\/mysqld\/mysqld.sock’ port: 3306 (Ubuntu)<\/p>\n
mysqld is alive<\/p>\n
Checking for tables which need an upgrade, are corrupt or were<\/p>\n
not closed cleanly.<\/p>\n
120618 14:07:36 [ERROR] Cannot find or open table nova\/projects from<\/p>\n
the internal […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/1696"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1696"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/1696\/revisions"}],"predecessor-version":[{"id":1698,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/1696\/revisions\/1698"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1696"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1696"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1696"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}