# Access the Database

All should be run on the controller.

Find the `database_password`:

```
./cc-ansible --site /opt/site-config edit_passwords
```

Get inside mariadb service container:

```
docker exec -it mariadb bash
```

Enter `database_password`:

```
mysql -u root -p
```

Show the databases:

```console
MariaDB [(none)]> SHOW DATABASES; 
+--------------------+
| Database           |
+--------------------+
| PERCONA_SCHEMA     |
| blazar             |
| cinder             |
| doni               |
| glance             |
| heat               |
| information_schema |
| ironic             |
| keystone           |
| mysql              |
| neutron            |
| nova               |
| nova_api           |
| nova_cell0         |
| performance_schema |
| placement          |
| zun                |
+--------------------+
```

Select the database:

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

Database changed
```

Show the desired tables:

```console
MariaDB [(none)]> SHOW TABLES from zun;
+--------------------------+
| Tables_in_zun            |
+--------------------------+
| alembic_version          |
| allocation               |
| compute_node             |
| container                |
| container_actions        |
| container_actions_events |
| exec_instance            |
| image                    |
| inventory                |
| network                  |
| pci_device               |
| quota_classes            |
| quota_usages             |
| quotas                   |
| registry                 |
| resource_class           |
| resource_provider        |
| volume                   |
| volume_mapping           |
| zun_service              |
+--------------------------+
```

Drop a database:

```
MariaDB [(none)]> DROP DATABASE zun;
Query OK, 20 rows affected (0.175 sec)
```

Show entris in a table:

```
select * from networksegment_extra_capabilities;
```

Here is how to delete an entry of a table. For example here we delete a network segment with id `7edefbb6-78a7-4dc8-af94-6bd18376bb08` in blazar database:

```
select * from network_segments;
delete from networksegment_extra_capabilities where network_id='7edefbb6-78a7-4dc8-af94-6bd18376bb08';
delete from network_allocations where network_id='7edefbb6-78a7-4dc8-af94-6bd18376bb08';
delete from network_segments where id='7edefbb6-78a7-4dc8-af94-6bd18376bb08';
```

After cleaning a service's database, Keystone's records must be cleaned as well.

From Keystone the following items should be deleted:

* The service entry itself:

  ```
  'name': 'zun', 'service_type': 'container'
  ```

  To delete the service:

  ```
  openstack service list
  openstack service delete <service-id>
  ```
* Its endpoints:

  ```
  'service': 'zun', 'url': 'http://10.20.111.99:9517/v1/', 'interface': 'admin'
  'service': 'zun', 'url': 'http://10.20.111.99:9517/v1/', 'interface': 'internal'
  'service': 'zun', 'url': 'https://testbed.expeca.proj.kth.se:9517/v1/', 'interface': 'public'
  ```

  To delete all endpoints, use Keystone CLI:

  ```
  openstack endpoint list
  openstack endpoint delete <endpoint-id1>
  openstack endpoint delete <endpoint-id2>
  openstack endpoint delete <endpoint-id3>
  ```
* Its user:

  ```
  'user': 'zun', 'project': 'service'
  ```

  Delete the user by:

  ```
  openstack user list
  openstack user delete <user-id>
  ```

NOTE: `zun` service has entries in `placement` database service as well. Deleting them is crucial and resolved a bug. The following instructions are regarding that.

```
MariaDB [(none)]> use placement;
MariaDB [placement]> select * from resource_providers;
```

We need to remove the `controller-01-k8s` resource provider which is a key item. However, it has references in other tables of the database. So they should be deleted too.

* `allocations` table:

```
MariaDB [placement]> select * from allocations;
MariaDB [placement]> DELETE FROM allocations WHERE id='17';
MariaDB [placement]> DELETE FROM allocations WHERE id='18';
MariaDB [placement]> DELETE FROM allocations WHERE id='21';
```

* `inventories` table:

```
MariaDB [placement]> select * from inventories;
MariaDB [placement]> DELETE FROM inventories WHERE id='1';
MariaDB [placement]> DELETE FROM inventories WHERE id='2';
MariaDB [placement]> DELETE FROM inventories WHERE id='3';
```

Then the foreign key check must be disabled, delete the resource provider, and enable it again.

```
MariaDB [placement]> SET FOREIGN_KEY_CHECKS=0;
MariaDB [placement]> DELETE FROM resource_providers WHERE id='1';
MariaDB [placement]> SET FOREIGN_KEY_CHECKS=1;
```

Then deploy the service to create a new database.