OpenStack, Troubleshooting

OpenStack DataBase Basic Troubleshooting

Most OpenStack services use a database to store information. The database typically runs on the Controller node.

In this blog post you’ll find some basic troubleshooting and introduction to MariaDB database server OpenStack usage. If you wonder what MariaDB server has to with MySQL server you can check out this link.

First we’ll log into the database server that is running on our Controller node

root@controller:~# mysql -u root -p

After we have entered our DB server password we can see what users have been configured to have access to our database server 

MariaDB [(none)]> SELECT User, Host, Password FROM mysql.user;
+------------------+------------+-------------------------------------------+
| User             | Host       | Password                                  |
+------------------+------------+-------------------------------------------+
| root             | localhost  | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| root             | controller | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| root             | 127.0.0.1  | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| root             | ::1        | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| debian-sys-maint | localhost  | *C6AE074982EBDDB8B23E0C327B4564C257716D52 |
| keystone         | localhost  | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| keystone         | %          | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| glance           | localhost  | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| glance           | %          | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| nova             | localhost  | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| nova             | %          | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| neutron          | localhost  | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
| neutron          | %          | *924DEB3C0C6DBA65C8D67DB4F5DA7B4074AF518E |
+------------------+------------+-------------------------------------------+
13 rows in set (0.00 sec) 

And then we can check what databases has been created on our database server

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

Let’s choose one of these databases by using the “use” command

MariaDB [(none)]> use nova;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Now let’s look at the tables the “nova” database consist of (shortened for brevity):

MariaDB [nova]> SHOW TABLES;
+--------------------------------------------+
| Tables_in_nova                             |
+--------------------------------------------+
| agent_builds                               |
| aggregate_hosts                            |
| .................                          |
| instance_actions                           |
| instance_actions_events                    |
| instance_extra                             |
| instance_faults                            |
| instance_group_member                      |
| instance_group_policy                      |
| instance_groups                            |
| instance_id_mappings                       |
| instance_info_caches                       |
| instance_metadata                          |
| instance_system_metadata                   |
| instance_type_extra_specs                  |
| instance_type_projects                     |
| instance_types                             |
| instances                                  |
| iscsi_targets                              |
| key_pairs                                  |
| .....................                      |
| virtual_interfaces                         |
| volume_id_mappings                         |
| volume_usage_cache                         |
| volumes                                    |
+--------------------------------------------+
108 rows in set (0.00 sec)

In order to see which tables in “nova” database starts with “instance” we’ll do the following:

MariaDB [nova]> SHOW TABLES WHERE Tables_in_nova LIKE 'instance%';
+---------------------------+
| Tables_in_nova            |
+---------------------------+
| instance_actions          |
| instance_actions_events   |
| instance_extra            |
| instance_faults           |
| instance_group_member     |
| instance_group_policy     |
| instance_groups           |
| instance_id_mappings      |
| instance_info_caches      |
| instance_metadata         |
| instance_system_metadata  |
| instance_type_extra_specs |
| instance_type_projects    |
| instance_types            |
| instances                 |
+---------------------------+
15 rows in set (0.00 sec) 

Now that we know what tables are available in the “nova” database let’s look into one table. We’ll pick the “instances” table for our example (shortened for brevity):

 MariaDB [nova]> describe instances;
+--------------------------+-----------------------+------+-----+---------+----------------+
| Field                    | Type                  | Null | Key | Default | Extra          |
+--------------------------+-----------------------+------+-----+---------+----------------+
| created_at               | datetime              | YES  |     | NULL    |                |
| updated_at               | datetime              | YES  |     | NULL    |                |
| deleted_at               | datetime              | YES  |     | NULL    |                |
| id                       | int(11)               | NO   | PRI | NULL    | auto_increment |
| internal_id              | int(11)               | YES  |     | NULL    |                |
| user_id                  | varchar(255)          | YES  |     | NULL    |                |
| project_id               | varchar(255)          | YES  | MUL | NULL    |                |
| .......                  | ............          |      |     |         |                |
| node                     | varchar(255)          | YES  |     | NULL    |                |
| deleted                  | int(11)               | YES  |     | NULL    |                |
| locked_by                | enum('owner','admin') | YES  |     | NULL    |                |
| cleaned                  | int(11)               | YES  |     | NULL    |                |
| ephemeral_key_uuid       | varchar(36)           | YES  |     | NULL    |                |
+--------------------------+-----------------------+------+-----+---------+----------------+
53 rows in set (0.01 sec) 

We can see that “instances” table inside nova database has 53 different fields. Let’s look at the names we have granted our instances

MariaDB [nova]> select display_name from instances;
+----------------+
| display_name   |
+----------------+
| test1          |
| demo-instance1 |
| demo-instance2 |
| test5          |
| test1          |
| test2          |
| test3          |
| test2          |
| test4          |
| test5          |
| test7          |
| demo-instance1 |
| demo-instance2 |
| demo-instance3 |
| demo-instance4 |
| demo-instance5 |
+----------------+
16 rows in set (0.01 sec)

We see that we have 16 rows of name that has been created. Trying to delete one of them in order to resolve a situation of sync issue between the “nova” database and the nova service yield an error

 MariaDB [nova]> DELETE FROM `nova`.`instances` WHERE `instances`.`display_name` ='test7';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`nova`.`block_device_mapping`, CONSTRAINT `block_device_mapping_instance_uuid_fkey` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`))

Just for the sake of this demonstration, we can disable the foreign key check by using the following command:

 MariaDB [nova]> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

And now our “test7” instance is removed from the “nova” database.

 MariaDB [nova]> DELETE FROM `nova`.`instances` WHERE `instances`.`display_name` ='test7';
Query OK, 1 row affected (0.00 sec)

 MariaDB [nova]> select display_name from instances;
+----------------+
| display_name   |
+----------------+
| test1          |
| demo-instance1 |
| demo-instance2 |
| test5          |
| test1          |
| test2          |
| test3          |
| test2          |
| test4          |
| test5          |
| demo-instance1 |
| demo-instance2 |
| demo-instance3 |
| demo-instance4 |
| demo-instance5 |
+----------------+
15 rows in set (0.00 sec)

We’ll enable the foreign key check by the following

MariaDB [nova]> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

And exit the MariaDB server

MariaDB [nova]> exit
Bye

Now it’s time to check what nova service say when we want to see the current instances running on our deployment

root@controller:~# source demo-openrc.sh 
root@controller:~# nova list
+--------------------------------------+----------------+--------+------------+-------------+-------------------------------------+
| ID                                   | Name           | Status | Task State | Power State | Networks                            |
+--------------------------------------+----------------+--------+------------+-------------+-------------------------------------+
| f152792f-4450-4b3c-9035-3a9b4c9d4586 | demo-instance3 | ACTIVE | -          | Running     | demo-net=192.168.1.2, 10.114.129.20 |
| d28ab7c4-23f9-408d-af8a-b865c2e9c619 | demo-instance5 | ACTIVE | -          | Running     | demo-net=192.168.1.4, 10.114.129.19 |
+--------------------------------------+----------------+--------+------------+-------------+-------------------------------------+
root@controller:~# 

We see only 2 instances vs. the 15 instances we saw in the nova DB. The reason is that the “nova” DB keep all instances, even the terminated ones.

Conclusion:

We have logged into MariaDB server and looked on the available databases we have created during our OpenStack installation. Then we looked at the users, tables and the fields existing for each table. We finally looked at how to delete an entry in our database in case we have an inconsistency shown in log message.

References:
https://mariadb.com/kb/en/mariadb/show-tables/
https://mariadb.com/kb/en/mariadb/basic-sql-statements/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s