Error:Access denied for user 'nova'@'%' to database 'nova_api_cell0'

[benliao]

Description
===========

My Openstack controller and compute node are installed on centOS7.

Everything is fine until I run "su -s /bin/sh -c "nova-manage db sync" nova" as indicated in the online Doc:https://docs.openstack.org/ocata/install-guide-rdo/nova-controller-install.html, I got the error below.
++++++++++++++++++++++++++++++++++++++++++
[root@server ~]# su -s /bin/sh -c "nova-manage db sync" nova
ERROR: Could not access cell0.
Has the nova_api database been created?
Has the nova_cell0 database been created?
Has "nova-manage api_db sync" been run?
Has "nova-manage cell_v2 map_cell0" been run?
Is [api_database]/connection set in nova.conf?
Is the cell0 database connection URL correct?
Error: (pymysql.err.OperationalError) (1044, u"Access denied for user 'nova'@'%' to database 'nova_api_cell0'")
++++++++++++++++++++++++++++++++++++++++++

I'm sure nova_api and nova_cell0 has been created, and "nova-manage api_db sync" and "nova-manage cell_v2 map_cell0" ran successfully. Also in nova.conf, [api_database]/connection is correctly set.
But I logged into the mysql,show databases and there is no such database nova_api_cell0.

I then moved on to next steps. All are fine until launching an instance. Error below.
++++++++++++++++++++++++++++++++++++++++++
[root@server ~]# openstack server list
Unexpected API Error. Please report this at http://bugs.launchpad.net/nova/ and attach the Nova API log if possible.
<class 'sqlalchemy.exc.OperationalError'> (HTTP 500) (Request-ID: req-3f7b8ee6-1939-442e-99a7-883cfc178cdd)
++++++++++++++++++++++++++++++++++++++++++

I also found the same error "Access denied for user 'nova'@'%' to database 'nova_api_cell0'" in nova.api.log

Can someone tell me why I run into this error? Many thanks in advance.

Environment
===========
root@server ~]# rpm -qa | grep nova
openstack-nova-common-15.1.0-1.el7.noarch
openstack-nova-conductor-15.1.0-1.el7.noarch
python-nova-15.1.0-1.el7.noarch
openstack-nova-scheduler-15.1.0-1.el7.noarch
openstack-nova-novncproxy-15.1.0-1.el7.noarch
openstack-nova-api-15.1.0-1.el7.noarch
openstack-nova-placement-api-15.1.0-1.el7.noarch
openstack-nova-console-15.1.0-1.el7.noarch
python2-novaclient-7.1.2-1.el7.noarch

[benliao (benliao)]

nova.conf:
==================

[root@server ~]# grep -v '^#' /etc/nova/nova.conf | grep -v '^$'
[DEFAULT]
use_neutron = True
my_ip=192.168.56.10
firewall_driver=nova.virt.firewall.NoopFirewallDriver
enabled_apis=osapi_compute,metadata
transport_url = rabbit://openstack:aaa123@server
[api]
auth_strategy=keystone
[api_database]
connection = mysql+pymysql://nova:aaa123@server/nova_api
[barbican]
[cache]
[cells]
[cinder]
[cloudpipe]
[conductor]
[console]
[consoleauth]
[cors]
[cors.subdomain]
[crypto]
[database]
connection = mysql+pymysql://nova:aaa123@server/nova
[ephemeral_storage_encryption]
[filter_scheduler]
[glance]
api_servers = http://server:9292
[guestfs]
[healthcheck]
[hyperv]
[image_file_url]
[ironic]
[key_manager]
[keystone_authtoken]
auth_uri = http://server:5000
auth_url = http://server:35357
memcached_servers = server:11211
auth_type = password
project_domain_name = default
user_domain_name = default
project_name = service
username = nova
password = aaa123
[libvirt]
[matchmaker_redis]
[metrics]
[mks]
[neutron]
url = http://server:9696
auth_url = http://server:35357
auth_type = password
project_domain_name = default
user_domain_name = default
region_name = RegionOne
project_name = service
username = neutron
password = aaa123
service_metadata_proxy = true
metadata_proxy_shared_secret = aaa123
[notifications]
[osapi_v21]
[oslo_concurrency]
lock_path=/var/lib/nova/tmp
[oslo_messaging_amqp]
[oslo_messaging_kafka]
[oslo_messaging_notifications]
[oslo_messaging_rabbit]
[oslo_messaging_zmq]
[oslo_middleware]
[oslo_policy]
[pci]
[placement]
os_region_name = RegionOne
project_domain_name = Default
project_name = service
auth_type = password
user_domain_name = Default
auth_url = http://server:35357/v3
username = placement
password = aaa123
[quota]
[rdp]
[remote_debug]
[scheduler]
[serial_console]
[service_user]
[spice]
[ssl]
[trusted_computing]
[upgrade_levels]
[vendordata_dynamic_auth]
[vmware]
[vnc]
enabled=true
vncserver_listen=$my_ip
vncserver_proxyclient_address=$my_ip
[workarounds]
[wsgi]
[xenserver]
[xvp]

databases:
==================

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| glance |
| information_schema |
| keystone |
| mysql |
| neutron |
| nova |
| nova_api |
| nova_cell0 |
| performance_schema |
| test |
+--------------------+
10 rows in set (0.00 sec)

[priyaduggirala (priyad)]

You can try logging to the database with nova user and check. This looks like a misconfiguration.Refer to https://docs.openstack.org/nova/pike/user/cells.html

[Matt Riedemann (mriedem)]

Yeah as suggested, these docs have some pointers:

https://docs.openstack.org/nova/pike/user/cells.html#first-time-setup

Specifically:

"If you don't specify --database_connection then nova-manage will use the [database]/connection value from your config file, and mangle the database name to have a _cell0 suffix."

And:

"If your databases are on separate hosts then you should specify --database_connection or make certain that the nova.conf being used has the [database]/connection value pointing to the same user/password/host that will work for the cell0 database. If the cell0 mapping was created incorrectly, it can be deleted using the nova-manage cell_v2 delete_cell command and then run map_cell0 again with the proper database connection value."

So I think what probably happened is that when you ran map_cell0, it created a nova_api.cell_mappings record with the wrong database connection URL, because now db sync, which is relying on the cell_mappings table record to connect to the cell0 database, is failing because it's looking for nova_api_cell0 but you have nova_cell0.

This is the exact code that is creating the default cell0 connection URL if you didn't specify --database-connection when running map_cell0:

https://github.com/openstack/nova/blob/15.1.0/nova/cmd/manage.py#L1160

So I think you probably just want to (1) delete the cell0 mapping (2) run map_cell0 again and ensure it creates a cell0 entry with the correct database connection URL and then (3) run nova-manage db sync again.

[Nam Nguyen (hiendinh114)]

I have the same issue. I delete cell0 mapping > run map_cell0 successfuly. But when execute the command "nova-manage db sync". It got the different error. (I insttall Zed distribution in Ubuntu 22.04)
Note: I test database connection and it is ok.

root@ops-ctrl-01 nova(keystone)# nova-manage cell_v2 list_cells
Modules with known eventlet monkey patching issues were imported prior to eventlet monkey patching: urllib3. This warning can usually be ignored if the caller is only importing and not executing nova code.
+-------+--------------------------------------+---------------+---------------------------------------------------------+----------+
| Name | UUID | Transport URL | Database Connection | Disabled |
+-------+--------------------------------------+---------------+---------------------------------------------------------+----------+
| cell0 | 00000000-0000-0000-0000-000000000000 | none:/ | mysql+pymysql://nova:****@ops-ctrl.lnb.local/nova_cell0 | False |
+-------+--------------------------------------+---------------+---------------------------------------------------------+----------+

root@ops-ctrl-01 nova(keystone)# su -s /bin/bash nova -c "nova-manage db sync"
Modules with known eventlet monkey patching issues were imported prior to eventlet monkey patching: urllib3. This warning can usually be ignored if the caller is only importing and not executing nova code.
An error has occurred:
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 3250, in _wrap_pool_connect
    return fn()
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 665, in __connect
    with util.safe_reraise():
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invok...

[Nam Nguyen (hiendinh114)]

Sorry. I decleared the wrong password in config file.