Often during testing we have found ourselves having to manually remove a volume entry from the cinder database directly. This article describes the steps to work with cinder database. In order to work with the command line, the required environment variables need to be setup as described inĀ http://docs.openstack.org/user-guide-admin/content/cli_openrc.html

Listing the cinder volumes

First we get a list of volumes which we need to manually delete as seen below

# cinder list
+--------------------------------------+----------------+--------------+------+-------------+----------+-------------+
|                  ID                  |     Status     | Display Name | Size | Volume Type | Bootable | Attached to |
+--------------------------------------+----------------+--------------+------+-------------+----------+-------------+
| 13a29c45-1996-41de-9a7f-de689a50fd07 | error_deleting |     test     |  1   |     None    |  false   |             |
| 2b9b8158-f43b-421b-a8c9-77e559ab1b95 | error_deleting |    test2     |  1   |     None    |  false   |             |
| 5e6e1ca4-5e67-40e5-82bb-b40c752e90b8 |   backing-up   |    test4     |  10  |     None    |  false   |             |
| 8f6cb644-fac2-4f81-94a7-14764ca4bae8 | error_deleting |     test     |  1   |     None    |  false   |             |
| c84f72a9-7b24-4d5d-9744-75e0ed44246f |   available    |    test6     |  20  |     None    |  false   |             |
| cb05ab9d-69ae-40c1-b773-7d623bc06372 | error_deleting |    test5     |  10  |     None    |  false   |             |
| e27cad5c-3109-4314-a7aa-778947f054ad | error_deleting |    test1     |  1   |     None    |  false   |             |
| ea22fbe6-eee8-4e42-b2e2-9a178f44b37e | error_deleting |    test5     |  10  |     None    |  false   |             |
| f87b4acc-1704-4742-8611-6f88b9332aae | error_deleting |    test5     |  10  |     None    |  false   |             |
+--------------------------------------+----------------+--------------+------+-------------+----------+-------------+

In the output above we need to delete volume test4 which is shown as backing-up but backup was not configured for this machine. Also we need to manually delete the volumes with status as error_deleting.

Connecting to the Cinder DB

# mysql -u root cinder
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

We have user id 'root' who will have access to all databases, but you could also use 'cinder' and its password since 'cinder' is the owner of the database 'cinder'

Querying the Cinder database dables

We didn't know the tables created in the database. A simple mysql show tables query will list us the tables

mysql> show tables;
+-------------------------+
| Tables_in_cinder        |
+-------------------------+
| backups                 |
| iscsi_targets           |
| migrate_version         |
| migrations              |
| quota_classes           |
| quota_usages            |
| quotas                  |
| reservations            |
| services                |
| sm_backend_config       |
| sm_flavors              |
| sm_volume               |
| snapshot_metadata       |
| snapshots               |
| volume_glance_metadata  |
| volume_metadata         |
| volume_type_extra_specs |
| volume_types            |
| volumes                 |
+-------------------------+
19 rows in set (0.00 sec)

The table we are interested in is 'volumes' and the key we are interested in is 'id'. But we already know the id from the 'cinder list' output. So to delete volume 'test' with id '13a29c45-1996-41de-9a7f-de689a50fd07' we issue the following sql statement

mysql> delete from volumes where id='13a29c45-1996-41de-9a7f-de689a50fd07';
Query OK, 1 row affected (0.21 sec)

We delete the rest of the volumes in a similar manner. After deleting the cinder volumes a 'cinder list' now shows

# cinder list
cinder snapshot list
+--------------------------------------+-----------+--------------+------+-------------+----------+-------------+
|                  ID                  |   Status  | Display Name | Size | Volume Type | Bootable | Attached to |
+--------------------------------------+-----------+--------------+------+-------------+----------+-------------+
| c84f72a9-7b24-4d5d-9744-75e0ed44246f | available |    test6     |  20  |     None    |  false   |             |
+--------------------------------------+-----------+--------------+------+-------------+----------+-------------+

A cinder volume can only be deleted if there is no snapshot depending on that volume. To get the list of snapshots use the following command

cinder snapshot-list

To delete a snapshot use the following command

cinder snapshot-delete <snapshot id>

Similar to the manually deleting a volume from the database, a snapshot can also be delete. In the Cinder db query and delete from the 'snapshot' table similar to the 'volumes' table

Updating quota usages

Since we manually deleted volumes and snapshots from the database, we need to manually update the quota usages. This information is stored in the quota_usages table as seen below

mysql> select * from quota_usages;
+---------------------+---------------------+------------+---------+----+----------------------------------+-----------+--------+----------+---------------+
| created_at          | updated_at          | deleted_at | deleted | id | project_id                       | resource  | in_use | reserved | until_refresh |
+---------------------+---------------------+------------+---------+----+----------------------------------+-----------+--------+----------+---------------+
| 2013-09-21 18:28:12 | 2013-11-22 07:03:42 | NULL       |       0 |  1 | 079aece528b84af1a6d2599e78493284 | gigabytes |    394 |        0 |          NULL |
| 2013-09-21 18:28:12 | 2013-11-22 07:03:42 | NULL       |       0 |  2 | 079aece528b84af1a6d2599e78493284 | volumes   |     10 |        0 |          NULL |
| 2013-09-23 05:06:51 | 2013-11-21 10:22:49 | NULL       |       0 |  3 | 079aece528b84af1a6d2599e78493284 | snapshots |      1 |        0 |          NULL |
+---------------------+---------------------+------------+---------+----+----------------------------------+-----------+--------+----------+---------------+
3 rows in set (0.00 sec)

The quota_usages should reflect the current number of volumes, snapshots, and gigabytes used correctly. They can be manipulated as shown below

mysql> update quota_usages set in_use='0' where resource='snapshots';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update quota_usages set in_use='2' where resource='volumes';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

After the above steps restart the openstack-cinder-volume service

Recent Site Activity|R