See also expired bug
https://bugs.launchpad.net/nova/+bug/1305892
Description
===========
Archiving deleted server instances results in an sqlalchemy exception. The table "instances" uses an sqlalchemy enum named "instances0locked_by". The shadow table "shadow_instances" also has an enum but uses a different type "shadow_instances0locked_by".
As a result moving rows from instances to shadow_instances fails.
Steps to reproduce
==================
* setup openstack with a postgresql database
* create a virtual machine (openstack server create ...)
* delete virtual machine (openstack server delete ...)
* run "nova-manage db archive_deleted_rows --max_rows 1 --all-cells --verbose"
Expected result
===============
records are archived
Actual result
=============
sqlalchemy exception
DBAPIError exception wrapped.: psycopg2.errors.DatatypeMismatch: column "locked_by" is of type shadow_instances0locked_by but expression is of type instances0locked_by
SQL:
INSERT INTO shadow_instances
(created_at, updated_at, deleted_at, deleted, id, user_id, project_id, image_ref, kernel_id, ramdisk_id, hostname, launch_index, key_name, key_data, power_state, vm_state, task_state, memory_mb, vcpus, root_gb, ephemeral_gb, ephemeral_key_uuid, host, node, compute_id, instance_type_id, user_data, reservation_id, launched_at, terminated_at, availability_zone, display_name, display_description, launched_on, locked, locked_by, os_type, architecture, vm_mode, uuid, root_device_name, default_ephemeral_device, default_swap_device, config_drive, access_ip_v4, access_ip_v6, auto_disk_config, progress, shutdown_terminate, disable_terminate, cell_name, cleaned, hidden)
SELECT instances.created_at, instances.updated_at, instances.deleted_at, instances.deleted, instances.id, instances.user_id, instances.project_id, instances.image_ref, instances.kernel_id, instances.ramdisk_id, instances.hostname, instances.launch_index, instances.key_name, instances.key_data, instances.power_state, instances.vm_state, instances.task_state, instances.memory_mb, instances.vcpus, instances.root_gb, instances.ephemeral_gb, instances.ephemeral_key_uuid, instances.host, instances.node, instances.compute_id, instances.instance_type_id, instances.user_data, instances.reservation_id, instances.launched_at, instances.terminated_at, instances.availability_zone, instances.display_name, instances.display_description, instances.launched_on, instances.locked, instances.locked_by, instances.os_type, instances.architecture, instances.vm_mode, instances.uuid, instances.root_device_name, instances.default_ephemeral_device, instances.default_swap_device, instances.config_drive, instances.access_ip_v4, instances.access_ip_v6, instances.auto_disk_config, instances.progress, instances.shutdown_terminate, instances.disable_terminate, instances.cell_name, instances.cleaned, instances.hidden
FROM instances
WHERE instances.id IN (%(id_1_1)s)]
Environment
===========
openstack version
python3-novaclient-18.4.0-1.el9s.noarch
python3-nova-28.0.1-1.el9s.noarch
openstack-nova-common-28.0.1-1.el9s.noarch
openstack-nova-api-28.0.1-1.el9s.noarch
openstack-nova-novncproxy-28.0.1-1.el9s.noarch
openstack-nova-scheduler-28.0.1-1.el9s.noarch
openstack-nova-conductor-28.0.1-1.el9s.noarch
postgres
postgresql-private-libs-13.14-1.el9_3.x86_64
postgresql-13.14-1.el9_3.x86_64
postgresql-server-13.14-1.el9_3.x86_64
sqlalchemy
python3-sqlalchemy-1.4.45-3.el9.x86_64
libvirt+kvm (should not matter, but for completeness)
libvirt-client-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-common-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-config-network-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-driver-network-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-driver-nodedev-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-driver-nwfilter-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-driver-qemu-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-driver-secret-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-driver-storage-core-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-lock-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-log-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-plugin-lockd-9.5.0-7.2.el9_3.x86_64
libvirt-daemon-proxy-9.5.0-7.2.el9_3.x86_64
libvirt-libs-9.5.0-7.2.el9_3.x86_64
python3-libvirt-9.3.0-1.el9.x86_64
qemu-kvm-8.0.0-16.el9_3.3.x86_64
qemu-kvm-audio-pa-8.0.0-16.el9_3.3.x86_64
qemu-kvm-block-blkio-8.0.0-16.el9_3.3.x86_64
qemu-kvm-block-rbd-8.0.0-16.el9_3.3.x86_64
qemu-kvm-common-8.0.0-16.el9_3.3.x86_64
qemu-kvm-core-8.0.0-16.el9_3.3.x86_64
qemu-kvm-device-display-virtio-gpu-8.0.0-16.el9_3.3.x86_64
qemu-kvm-device-display-virtio-gpu-pci-8.0.0-16.el9_3.3.x86_64
qemu-kvm-device-display-virtio-vga-8.0.0-16.el9_3.3.x86_64
qemu-kvm-device-usb-host-8.0.0-16.el9_3.3.x86_64
qemu-kvm-device-usb-redirect-8.0.0-16.el9_3.3.x86_64
qemu-kvm-docs-8.0.0-16.el9_3.3.x86_64
qemu-kvm-tools-8.0.0-16.el9_3.3.x86_64
qemu-kvm-ui-egl-headless-8.0.0-16.el9_3.3.x86_64
qemu-kvm-ui-opengl-8.0.0-16.el9_3.3.x86_64
Guesswork
=========
I'm not that deep into the openstack code base. But here is my guesswork
The table is defined in nova/db/main/models.py and the locked_by column is an sqlalchemy Enum
https://github.com/openstack/nova/blob/master/nova/db/main/models.py#L382
The alembic migration nova/db/main/migrations/versions/8f2f1571d55b_initial_version.py is creating a new Enum
https://github.com/openstack/nova/blame/master/nova/db/main/migrations/versions/8f2f1571d55b_initial_version.py#L88
This could probably be solved if both tables use the same Enum Type
Otherwise the insert needs an explicit typecast, something like
cast(locked_by::text as shadow_instances0locked_by)
