Bug 854981 - openstack-db fails on mysql user create
openstack-db fails on mysql user create
Status: CLOSED UPSTREAM
Product: Fedora
Classification: Fedora
Component: openstack-utils (Show other bugs)
17
Unspecified Linux
unspecified Severity high
: ---
: ---
Assigned To: Pádraig Brady
Fedora Extras Quality Assurance
:
Depends On: 856548
Blocks:
  Show dependency treegraph
 
Reported: 2012-09-06 08:42 EDT by Jon Thomas
Modified: 2013-02-27 08:21 EST (History)
6 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2013-02-27 08:21:53 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Jon Thomas 2012-09-06 08:42:34 EDT
Openstack-db is failing on create user. It appears that the mysql command isn't correct. 

# openstack-db --service nova --init
Please enter the password for the 'root' MySQL user: 
Verified connectivity to MySQL.
Creating 'nova' database.
ERROR 1396 (HY000) at line 2: Operation CREATE USER failed for 'nova'@'localhost'
Asking openstack-nova to sync the database.
Please re-run nova-manage as root.
ERROR 1146 (42S02) at line 1: Table 'nova.migrate_version' doesn't exist
Final sanity check failed.
Please file a bug report on bugzilla.redhat.com against the openstack-nova package.

It fails on a manual attempt as well.

mysql> create user 'nova'@'localhost' identified by 'nova';
ERROR 1396 (HY000): Operation CREATE USER failed for 'nova'@'localhost'
mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| keystone | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| keystone | localhost        |
| root     | localhost        |
+----------+------------------+
8 rows in set (0.00 sec)


The following works:

CREATE DATABASE $APP;
#CREATE USER '$APP'@'localhost' IDENTIFIED BY '${MYSQL_APP_PW}';
#CREATE USER '$APP'@'%' IDENTIFIED BY '${MYSQL_APP_PW}';
GRANT ALL ON $APP.* TO '$APP'@'localhost' IDENTIFIED BY '${MYSQL_APP_PW}';
GRANT ALL ON $APP.* TO '$APP'@'%' IDENTIFIED BY '${MYSQL_APP_PW}';
flush privileges;
EOF


mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| glance   | %                |
| keystone | %                |
| nova     | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| glance   | localhost        |
| keystone | localhost        |
| nova     | localhost        |
| root     | localhost        |
+----------+------------------+
12 rows in set (0.00 sec)

# rpm -qa | grep openstack
openstack-dashboard-2012.1.1-1.fc17.noarch
openstack-nova-scheduler-2012.1.1-15.fc17.noarch
openstack-nova-cert-2012.1.1-15.fc17.noarch
openstack-nova-api-2012.1.1-15.fc17.noarch
openstack-utils-2012.1-2.fc17.noarch
openstack-nova-network-2012.1.1-15.fc17.noarch
openstack-keystone-2012.1.1-1.fc17.noarch
openstack-nova-objectstore-2012.1.1-15.fc17.noarch
openstack-glance-2012.1.1-1.fc17.noarch
openstack-nova-volume-2012.1.1-15.fc17.noarch
openstack-nova-console-2012.1.1-15.fc17.noarch
openstack-nova-common-2012.1.1-15.fc17.noarch
openstack-nova-2012.1.1-15.fc17.noarch
openstack-nova-compute-2012.1.1-15.fc17.noarch

# rpm -qa | grep mysql
mysql-libs-5.5.27-1.fc17.x86_64
mysql-embedded-5.5.25a-1.fc17.x86_64
mysql-connector-java-5.1.17-5.fc17.noarch
qt-mysql-4.8.2-4.fc17.x86_64
mysql-5.5.27-1.fc17.x86_64
php-mysql-5.4.4-4.fc17.x86_64
mysql-server-5.5.27-1.fc17.x86_64
Comment 1 Pádraig Brady 2012-09-06 10:12:37 EDT
Thanks for looking into this!

Hmm others have reported that the previous mysql package on F17 was OK at least.
Just confirmed again with a user that the openstack-db script ran OK.
Though mrunge has reported a similar issue to you (What db version Matthias?)

So are you saying that GRANT now requires the password along
the lines of the patch below. I couldn't find a mention of
that though in the mysql docs or release notes?

I'm guessing the latest F17 update is now mandating this
or perhaps you already had a mysql database installed
and openstack-db is making assumptions about a clean database?


diff --git a/utils/openstack-db b/utils/openstack-db
index d7004bd..80657c5 100755
--- a/utils/openstack-db
+++ b/utils/openstack-db
@@ -219,14 +219,15 @@ echo "Verified connectivity to MySQL."
 
 # Create or Drop the db.
 
+MYSQL_ID="IDENTIFIED BY '${MYSQL_APP_PW}'"
 if [ "$MODE" = 'init' ]; then
   echo "Creating '$APP' database." >&2
 cat << EOF
 CREATE DATABASE $APP;
-CREATE USER '$APP'@'localhost' IDENTIFIED BY '${MYSQL_APP_PW}';
-CREATE USER '$APP'@'%' IDENTIFIED BY '${MYSQL_APP_PW}';
-GRANT ALL ON $APP.* TO '$APP'@'localhost';
-GRANT ALL ON $APP.* TO '$APP'@'%';
+CREATE USER '$APP'@'localhost' $MYSQL_ID;
+CREATE USER '$APP'@'%' $MYSQL_ID;
+GRANT ALL ON $APP.* TO '$APP'@'localhost' $MYSQL_ID;
+GRANT ALL ON $APP.* TO '$APP'@'%' $MYSQL_ID;
 flush privileges;
 EOF
 else
Comment 2 Jon Thomas 2012-09-06 11:08:30 EDT

Thanks for the feedback. Previous problem was on a clean mysql install, but I had a prior openstack config and rpm -e and then rpm -Ivh to get back to clean. Perhaps the db wasn't cleaned. Strangely, if I dropped the nova db and deleted the nova use, it still failed. At this point I don't have any notes on whether I used flush privileges;. So I'm thinking that in the original problem, the nova user existed in the db and that it needed a flush privileges; I've since rebooted and for some reason create seems to work now.

I tested with create/delete and there seems to be something strange going on with users. For some reason it appears that delete works intermittently. Notice in this output how delete behaves differently for 'jeffery' and for 'jthomas'


mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| glance   | %                |
| keystone | %                |
| nova     | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| glance   | localhost        |
| keystone | localhost        |
| nova     | localhost        |
| root     | localhost        |
+----------+------------------+
12 rows in set (0.00 sec)

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

mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| glance   | %                |
| keystone | %                |
| nova     | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| glance   | localhost        |
| jeffrey  | localhost        |
| keystone | localhost        |
| nova     | localhost        |
| root     | localhost        |
+----------+------------------+
13 rows in set (0.00 sec)

mysql> delete from mysql.user WHERE User='jeffery';
Query OK, 0 rows affected (0.00 sec)

mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| glance   | %                |
| keystone | %                |
| nova     | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| glance   | localhost        |
| jeffrey  | localhost        |
| keystone | localhost        |
| nova     | localhost        |
| root     | localhost        |
+----------+------------------+
13 rows in set (0.00 sec)

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

mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| glance   | %                |
| keystone | %                |
| nova     | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| glance   | localhost        |
| jeffrey  | localhost        |
| jthomas  | localhost        |
| keystone | localhost        |
| nova     | localhost        |
| root     | localhost        |
+----------+------------------+
14 rows in set (0.00 sec)

mysql> delete from mysql.user WHERE User='jthomas';
Query OK, 1 row affected (0.00 sec)

mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| glance   | %                |
| keystone | %                |
| nova     | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| glance   | localhost        |
| jeffrey  | localhost        |
| keystone | localhost        |
| nova     | localhost        |
| root     | localhost        |
+----------+------------------+
13 rows in set (0.00 sec)


mysql> drop user jeffrey@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| glance   | %                |
| keystone | %                |
| nova     | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| glance   | localhost        |
| keystone | localhost        |
| nova     | localhost        |
| root     | localhost        |
+----------+------------------+
12 rows in set (0.00 sec)

mysql> CREATE USER 'jthomas'@'localhost' IDENTIFIED BY 'mypass';
ERROR 1396 (HY000): Operation CREATE USER failed for 'jthomas'@'localhost'

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| glance   | %                |
| keystone | %                |
| nova     | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| glance   | localhost        |
| jeffery  | localhost        |
| jthomas  | localhost        |
| keystone | localhost        |
| nova     | localhost        |
| root     | localhost        |
+----------+------------------+
14 rows in set (0.00 sec)

mysql> delete from mysql.user WHERE User='jthomas';
Query OK, 1 row affected (0.00 sec)

mysql> delete from mysql.user WHERE User='jeffery';
Query OK, 1 row affected (0.00 sec)

mysql> select User,Host from mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| glance   | %                |
| keystone | %                |
| nova     | %                |
| root     | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
|          | basin.redhat.com |
| root     | basin.redhat.com |
| glance   | localhost        |
| keystone | localhost        |
| nova     | localhost        |
| root     | localhost        |
+----------+------------------+
12 rows in set (0.00 sec)
Comment 3 Jon Thomas 2012-09-06 11:25:42 EDT
re: So are you saying that GRANT now requires the password along
the lines of the patch below. 

no, I think the problem is mysql.user; consistency. One thought is to put a flush privileges; prior to creating the db like: 

flush privileges;
CREATE DATABASE $APP;
CREATE USER '$APP'@'localhost' IDENTIFIED BY '${MYSQL_APP_PW}';
CREATE USER '$APP'@'%' IDENTIFIED BY '${MYSQL_APP_PW}';
GRANT ALL ON $APP.* TO '$APP'@'localhost';
GRANT ALL ON $APP.* TO '$APP'@'%';
flush privileges;
EOF

I think that would have worked for me, but my case is probably unusual.
Comment 4 Matthias Runge 2012-09-12 05:01:38 EDT
Padraig: 
[root@localhost ~]# rpm -q mysql-server
mysql-server-5.5.27-1.fc17.x86_64

I installed a fresh f17 vm, and also followed the guide [1]

I got tons of messages like these:
 Installing : openstack-glance-2012.1.1-1.fc17.noarch                   50/104 
warning: group glance does not exist - using root
warning: group glance does not exist - using root
warning: group glance does not exist - using root
warning: group glance does not exist - using root
warning: group glance does not exist - using root
warning: group glance does not exist - using root
warning: group glance does not exist - using root
warning: group glance does not exist - using root
warning: group glance does not exist - using root
warning: group glance does not exist - using root
warning: user glance does not exist - using root
warning: user glance does not exist - using root
warning: user glance does not exist - using root


and corresponding in /var/log/audit/audit.log:
type=ADD_GROUP msg=audit(1347432524.038:82): pid=1780 uid=0 auid=1000 ses=2 subj=unconfined_u:system_r:groupadd_t:s0-s0:c0.c1023 msg='op=adding group to /etc/gshadow acct="glance" exe="/usr/sbin/groupadd" hostname=? addr=? terminal=? res=failed'
type=ADD_GROUP msg=audit(1347432524.039:83): pid=1780 uid=0 auid=1000 ses=2 subj=unconfined_u:system_r:groupadd_t:s0-s0:c0.c1023 msg='op=adding group to /etc/group acct="glance" exe="/usr/sbin/groupadd" hostname=? addr=? terminal=? res=failed'
type=ADD_GROUP msg=audit(1347432524.039:84): pid=1780 uid=0 auid=1000 ses=2 subj=unconfined_u:system_r:groupadd_t:s0-s0:c0.c1023 msg='op= acct="glance" exe="/usr/sbin/groupadd" hostname=? addr=? terminal=? res=failed'


libselinux-utils-2.1.10-3.fc17.x86_64
selinux-policy-devel-3.10.0-146.fc17.noarch
libselinux-2.1.10-3.fc17.x86_64
selinux-policy-targeted-3.10.0-146.fc17.noarch
libselinux-python-2.1.10-3.fc17.x86_64
selinux-policy-3.10.0-146.fc17.noarch




Jon, can you reproduce this on your system, such as

[root@localhost audit]# id glance
id: glance: no such user
[root@localhost audit]# id nova
id: nova: no such user

(after openstack-package installation)?




[1] http://fedoraproject.org/wiki/Getting_started_with_OpenStack_on_Fedora_17#Install_packages
Comment 5 Jon Thomas 2012-09-12 11:44:40 EDT
I just tested a fresh f17 install. I didn't use updates-testing, just updates and I didn't have a problem installing. It doesn't look like there is a newer glance package in updates-testing.

yum install  openstack-utils openstack-nova openstack-glance openstack-keystone openstack-dashboard qpid-cpp-server-daemon

...
  Installing : python-glance-2012.1.1-1.fc17.noarch                      85/161 
  Installing : openstack-glance-2012.1.1-1.fc17.noarch                   86/161 
  Installing : hivex-1.3.5-7.fc17.x86_64                                 87/161
...

# openstack-db --service nova --init
mysqld is not running.  Would you like to start it now? (y/n): y
Please enter the password for the 'root' MySQL user: 
Verified connectivity to MySQL.
Creating 'nova' database.
Asking openstack-nova to sync the database.
2012-09-12 11:32:01 WARNING nova.utils [-] /usr/lib64/python2.7/site-packages/sqlalchemy/pool.py:683: SADeprecationWarning: The 'listeners' argument to Pool (and create_engine()) is deprecated.  Use event.listen().
  Pool.__init__(self, creator, **kw)

2012-09-12 11:32:01 WARNING nova.utils [-] /usr/lib64/python2.7/site-packages/sqlalchemy/pool.py:159: SADeprecationWarning: Pool.add_listener is deprecated.  Use event.listen()
  self.add_listener(l)

2012-09-12 11:32:01 AUDIT nova.db.sqlalchemy.fix_dns_domains [-] Applying database fix for Essex dns_domains table.
Complete!
[root@localhost jrthomas]# ls -l /var/log/nova
total 4
-rw-r--r--. 1 nova nova 570 Sep 12 11:32 nova-manage.log
[root@localhost jrthomas]# openstack-db --service glance --init
Please enter the password for the 'root' MySQL user: 
Verified connectivity to MySQL.
Creating 'glance' database.
Asking openstack-glance to sync the database.
/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py:683: SADeprecationWarning: The 'listeners' argument to Pool (and create_engine()) is deprecated.  Use event.listen().
  Pool.__init__(self, creator, **kw)
/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py:159: SADeprecationWarning: Pool.add_listener is deprecated.  Use event.listen()
  self.add_listener(l)
Complete!
[root@localhost jrthomas]# id glance
uid=161(glance) gid=161(glance) groups=161(glance)
[root@localhost jrthomas]# id nova
uid=162(nova) gid=162(nova) groups=162(nova),99(nobody),107(qemu)

# rpm -qa | grep openstack
openstack-nova-objectstore-2012.1.1-15.fc17.noarch
openstack-nova-compute-2012.1.1-15.fc17.noarch
openstack-nova-cert-2012.1.1-15.fc17.noarch
openstack-nova-2012.1.1-15.fc17.noarch
openstack-nova-api-2012.1.1-15.fc17.noarch
openstack-nova-console-2012.1.1-15.fc17.noarch
openstack-dashboard-2012.1.1-1.fc17.noarch
openstack-utils-2012.1-2.fc17.noarch
openstack-glance-2012.1.1-1.fc17.noarch
openstack-nova-volume-2012.1.1-15.fc17.noarch
openstack-nova-scheduler-2012.1.1-15.fc17.noarch
openstack-keystone-2012.1.1-1.fc17.noarch
openstack-nova-common-2012.1.1-15.fc17.noarch
openstack-nova-network-2012.1.1-15.fc17.noarch
# rpm -q mysql-server
mysql-server-5.5.27-1.fc17.x86_64
Comment 6 Pádraig Brady 2013-02-27 08:21:53 EST
I think the crux of this issue is that it's awkward to manually
cleanup users from the DB, so I've modified openstack-db --init
to notice this situation and propose a remedy:

https://github.com/fedora-openstack/openstack-utils/commit/5e118a65

Note You need to log in before you can comment on or make changes to this bug.