---
tags:
  - work
  - nova
---

**instances**
```
+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| deleted_at  | datetime              | YES  |     | NULL    |                |
| uuid        | varchar(36)           | NO   | UNI | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+
```
**instance_system_metadata**
```
+---------------+--------------+------+-----+---------+----------------+
| 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 |
| instance_uuid | varchar(36)  | NO   | MUL | NULL    |                |
| key           | varchar(255) | NO   |     | NULL    |                |
| value         | varchar(255) | YES  |     | NULL    |                |
| deleted       | int(11)      | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
```

**Insert new record**
```
insert into instance_system_metadata (created_at, instance_uuid, `key`, `value`) values (NOW(), "4e675ad6-f01a-476f-8827-8300e540447c", "image_os_admin_user", "administrator"
```

**Updated existing values**
```
update instance_system_metadata set `value` = "administrator", updated_at = NOW() where deleted_at is NULL and `key` = "image_os_admin_user" and instance_uuid in (select instance_uuid from instance_system_metadata where deleted_at is NULL and `key` = "image_os_t
ype" and `value` = "linux");
```

**Which instances currently do not have image_os_admin_user**
```
select i.uuid from instances i left join instance_system_metadata ism on ism.instance_uuid = i.uuid and ism.key = "image_os_admin_user" where i.deleted_at is null and ism.id is null
```

**Which instances of those above, do have qemu guest agent stuff set**
```
select * from instance_system_metadata ism1 where ism1.instance_uuid in (select i.uuid from instances i left join instance_system_metadata ism on ism.instance_uuid = i.uuid and ism.key = "image_os_admin_user" where i.deleted_at is null and ism.id is null) and ism1.key = "image_hw_qemu_guest_agent";
```

**Insert the image_os_admin_user for those records that miss it but do have qemu guest agent metadata**
```
INSERT INTO instance_system_metadata (created_at, instance_uuid, `key`, `value`, deleted)
SELECT
    NOW(),                      -- Current timestamp for created_at
    i.uuid,                     -- The UUID of the instance needing the new key
    'image_os_admin_user',      -- The fixed key you want to insert
    'administrator',            -- The fixed value for the new key
    0
FROM
    instances i
INNER JOIN -- Use INNER JOIN to ensure the instance MUST have the guest agent key
    instance_system_metadata ism_agent ON ism_agent.instance_uuid = i.uuid
                                     AND ism_agent.key = 'image_hw_qemu_guest_agent'
LEFT JOIN -- Use LEFT JOIN to check for the PRESENCE of the admin user key
    instance_system_metadata ism_admin ON ism_admin.instance_uuid = i.uuid
                                     AND ism_admin.key = 'image_os_admin_user'
WHERE
    i.deleted_at IS NULL        -- Ensure the instance is not deleted
    AND ism_admin.instance_uuid IS NULL; -- This is the crucial part: Select only rows where the LEFT JOIN did NOT find an existing 'image_os_admin_user' key
```

```
insert into instance_system_metadata (created_at, instance_uuid, `key`, `value`, deleted) select NOW(), i.uuid, 'image_os_admin_user', 'administrator', 0 from instances i inner join instance_system_metadata ism_agent on ism_agent.instance_uuid = i.uuid and ism_agent.key = 'image_hw_qemu_guest_agent' left join instance_system_metadata ism_admin on ism_admin.instance_uuid = i.uuid and ism_admin.key = 'image_os_admin_user' where i.deleted_at is null and ism_admin.instance_uuid is null;
```