diff options
Diffstat (limited to 'Updating the image os admin user to fix password resets.md')
| -rw-r--r-- | Updating the image os admin user to fix password resets.md | 77 |
1 files changed, 77 insertions, 0 deletions
diff --git a/Updating the image os admin user to fix password resets.md b/Updating the image os admin user to fix password resets.md new file mode 100644 index 0000000..48852de --- /dev/null +++ b/Updating the image os admin user to fix password resets.md @@ -0,0 +1,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; +```
\ No newline at end of file |