1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
---
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;
```
|
