500 error when creating an instance with two network interfaces and one ephemeral IP

[lgfa29]

When creating an instance with two network interfaces and one ephemeral IP the IP returns a 500 error message:

{
    "request_id": "178e6abd-8109-407d-82cc-4a86a1e2ca03",
    "error_code": "Internal",
    "message": "Internal Server Error"
}

Sample request body:

{
  "anti_affinity_groups": [],
  "description": "a test instance",
  "external_ips": [
    {
      "pool_selector": {
        "type": "auto",
        "ip_version": "v4"
      },
      "type": "ephemeral"
    }
  ],
  "hostname": "terraform-acc-myhost",
  "memory": 1073741824,
  "name": "tf-test",
  "ncpus": 1,
  "network_interfaces": {
    "params": [
      {
        "description": "a sample nic",
        "ip_config": {
          "type": "dual_stack",
          "value": {
            "v4": {
              "ip": {
                "type": "auto"
              }
            },
            "v6": {
              "ip": {
                "type": "auto"
              }
            }
          }
        },
        "name": "nicname-v4",
        "subnet_name": "default",
        "vpc_name": "default"
      },
      {
        "description": "a sample nic",
        "ip_config": {
          "type": "dual_stack",
          "value": {
            "v4": {
              "ip": {
                "type": "auto"
              }
            },
            "v6": {
              "ip": {
                "type": "auto"
              }
            }
          }
        },
        "name": "nicname-v6",
        "subnet_name": "other2",
        "vpc_name": "default"
      }
    ],
    "type": "create"
  },
  "ssh_public_keys": [],
  "start": false
}

Looking through Omicron logs I found the saga failure event with the message:

unexpected database error: more than one row returned by a subquery used as an expression

{
    "msg": "saga finished",
    "v": 0,
    "name": "omicron-dev",
    "level": 40,
    "time": "2026-02-02T20:08:32.710435152Z",
    "hostname": "oxide",
    "pid": 5001,
    "action_error_source": "ActionFailed { source_error: Object {\"InternalError\": Object {\"internal_message\": String(\"unexpected database error: more than one row returned by a subquery used as an expression\")}} }",
    "action_error_node_name": "\"external-ip-0\"",
    "result": "failure",
    "saga_name": "instance-create",
    "saga_id": "3b05708e-aa73-4f1d-bafe-2ccacef98b3c",
    "sec_id": "e6bff1ff-24fb-49dc-a54e-c6a350cd4d6c",
    "component": "SEC",
    "component": "nexus",
    "component": "ServerContext",
    "name": "e6bff1ff-24fb-49dc-a54e-c6a350cd4d6c",
    "undo_result": "success"
}
{
    "msg": "saga finished",
    "v": 0,
    "name": "omicron-dev",
    "level": 30,
    "time": "2026-02-02T20:08:32.710570499Z",
    "hostname": "oxide",
    "pid": 5001,
    "saga_id": "3b05708e-aa73-4f1d-bafe-2ccacef98b3c",
    "saga_name": "instance-create",
    "component": "SagaExecutor",
    "component": "nexus",
    "component": "ServerContext",
    "name": "e6bff1ff-24fb-49dc-a54e-c6a350cd4d6c",
    "saga_result": "SagaResult { saga_id: 3b05708e-aa73-4f1d-bafe-2ccacef98b3c, saga_log: SagaLog { saga_id: 3b05708e-aa73-4f1d-bafe-2ccacef98b3c, unwinding: true, events: [N297 started, N297 succeeded, N000 started, N000 succeeded, N001 started, N001 succeeded, N002 started, N002 succeeded, N003 started, N003 succeeded, N004 started, N004 succeeded, N005 started, N005 succeeded, N006 started, N006 succeeded, N007 started, N007 succeeded, N008 started, N008 succeeded, N009 started, N009 succeeded, N010 started, N010 succeeded, N011 started, N011 succeeded, N012 started, N012 succeeded, N013 started, N013 succeeded, N014 started, N014 succeeded, N015 started, N015 succeeded, N016 started, N016 succeeded, N017 started, N017 succeeded, N018 started, N018 succeeded, N019 started, N019 succeeded, N020 started, N020 succeeded, N021 started, N021 succeeded, N022 started, N022 succeeded, N023 started, N023 succeeded, N024 started, N024 succeeded, N025 started, N025 succeeded, N026 started, N026 succeeded, N027 started, N027 succeeded, N028 started, N028 succeeded, N029 started, N029 succeeded, N030 started, N030 succeeded, N031 started, N031 succeeded, N032 started, N032 succeeded, N033 started, N033 succeeded, N034 started, N034 succeeded, N035 started, N035 succeeded, N036 started, N036 succeeded, N037 started, N037 succeeded, N038 started, N038 succeeded, N039 started, N039 succeeded, N040 started, N040 succeeded, N041 started, N041 failed, N040 undo_started, N040 undo_finished, N039 undo_started, N039 undo_finished, N038 undo_started, N038 undo_finished, N037 undo_started, N037 undo_finished, N036 undo_started, N036 undo_finished, N035 undo_started, N035 undo_finished, N034 undo_started, N034 undo_finished, N033 undo_started, N033 undo_finished, N032 undo_started, N032 undo_finished, N031 undo_started, N031 undo_finished, N030 undo_started, N030 undo_finished, N029 undo_started, N029 undo_finished, N028 undo_started, N028 undo_finished, N027 undo_started, N027 undo_finished, N026 undo_started, N026 undo_finished, N025 undo_started, N025 undo_finished, N024 undo_started, N024 undo_finished, N023 undo_started, N023 undo_finished, N022 undo_started, N022 undo_finished, N021 undo_started, N021 undo_finished, N020 undo_started, N020 undo_finished, N019 undo_started, N019 undo_finished, N018 undo_started, N018 undo_finished, N017 undo_started, N017 undo_finished, N016 undo_started, N016 undo_finished, N015 undo_started, N015 undo_finished, N014 undo_started, N014 undo_finished, N013 undo_started, N013 undo_finished, N012 undo_started, N012 undo_finished, N011 undo_started, N011 undo_finished, N010 undo_started, N010 undo_finished, N009 undo_started, N009 undo_finished, N008 undo_started, N008 undo_finished, N007 undo_started, N007 undo_finished, N006 undo_started, N006 undo_finished, N005 undo_started, N005 undo_finished, N004 undo_started, N004 undo_finished, N003 undo_started, N003 undo_finished, N002 undo_started, N002 undo_finished, N001 undo_started, N001 undo_finished, N000 undo_started, N000 undo_finished, N297 undo_started, N297 undo_finished], node_status: {0: UndoFinished, 1: UndoFinished, 2: UndoFinished, 3: UndoFinished, 4: UndoFinished, 5: UndoFinished, 6: UndoFinished, 7: UndoFinished, 8: UndoFinished, 9: UndoFinished, 10: UndoFinished, 11: UndoFinished, 12: UndoFinished, 13: UndoFinished, 14: UndoFinished, 15: UndoFinished, 16: UndoFinished, 17: UndoFinished, 18: UndoFinished, 19: UndoFinished, 20: UndoFinished, 21: UndoFinished, 22: UndoFinished, 23: UndoFinished, 24: UndoFinished, 25: UndoFinished, 26: UndoFinished, 27: UndoFinished, 28: UndoFinished, 29: UndoFinished, 30: UndoFinished, 31: UndoFinished, 32: UndoFinished, 33: UndoFinished, 34: UndoFinished, 35: UndoFinished, 36: UndoFinished, 37: UndoFinished, 38: UndoFinished, 39: UndoFinished, 40: UndoFinished, 41: Failed(ActionFailed { source_error: Object {\"InternalError\": Object {\"internal_message\": String(\"unexpected database error:more than one row returned by a subquery used as an expression\")}} }), 297: UndoFinished} }, kind: Err(SagaResultErr { error_node_name: \"external-ip-0\", error_source: ActionFailed { source_error: Object {\"InternalError\": Object {\"internal_message\": String(\"unexpected database error: more than one row returned by a subquery used as an expression\")}} }, undo_failure: None }) }"
}

The request succeeds if there are no ephemeral IPs and a similar instance works in R17 as well.

[bnaecker]

Can't say for certain yet, but I'm immediately suspicious of this:

omicron/nexus/db-queries/src/db/datastore/external_ip.rs

Line 579 in 44e65c3

let query = Instance::attach_resource_with_update_condition(

There's a SQL query in there designed to condense subqueries into a single boolean using EVERY, but I don't know if that's gotten much test coverage. If that's really the cause, it would also be mitigated by fixing #9722.

[bnaecker]

Ok, here's a simple reproducer:

// Regression for https://github.com/oxidecomputer/omicron/issues/9775.
#[nexus_test]
async fn can_create_instance_with_multiple_nics_and_ephemeral_ip(
    cptestctx: &ControlPlaneTestContext,
) {
    let client = &cptestctx.external_client;
    create_project_and_pool(&client).await;
    let second_vpc_subnet_name = "second";
    create_vpc_subnet(
        &client,
        PROJECT_NAME,
        "default",
        second_vpc_subnet_name,
        "10.2.0.0/24".parse().unwrap(),
        None,
        None,
    )
    .await;
    let nics = params::InstanceNetworkInterfaceAttachment::Create(vec![
        params::InstanceNetworkInterfaceCreate {
            identity: IdentityMetadataCreateParams {
                name: "net0".parse().unwrap(),
                description: String::new(),
            },
            vpc_name: "default".parse().unwrap(),
            subnet_name: "default".parse().unwrap(),
            ip_config: params::PrivateIpStackCreate::DualStack {
                v4: params::PrivateIpv4StackCreate {
                    ip: params::Ipv4Assignment::Auto,
                    transit_ips: vec![],
                },
                v6: params::PrivateIpv6StackCreate {
                    ip: params::Ipv6Assignment::Auto,
                    transit_ips: vec![],
                },
            },
        },
        params::InstanceNetworkInterfaceCreate {
            identity: IdentityMetadataCreateParams {
                name: "net1".parse().unwrap(),
                description: String::new(),
            },
            vpc_name: "default".parse().unwrap(),
            subnet_name: second_vpc_subnet_name.parse().unwrap(),
            ip_config: params::PrivateIpStackCreate::DualStack {
                v4: params::PrivateIpv4StackCreate {
                    ip: params::Ipv4Assignment::Auto,
                    transit_ips: vec![],
                },
                v6: params::PrivateIpv6StackCreate {
                    ip: params::Ipv6Assignment::Auto,
                    transit_ips: vec![],
                },
            },
        },
    ]);
    let external_ips = vec![params::ExternalIpCreate::Ephemeral {
        pool_selector: params::PoolSelector::Auto {
            ip_version: Some(IpVersion::V4),
        },
    }];
    let instance = create_instance_with(
        client,
        PROJECT_NAME,
        "multi-niccy",
        &nics,
        vec![],
        external_ips,
        false,
        None,
        None,
        vec![],
    )
    .await;
}

This fails in exactly the same way on main as the issue here. If I run this against #9779, which has the fix for #9722 in it, then things work fine. These are still separate issues though. The fix for #9722 is semantic: we want to validate only against the primary NIC, we don't care about the others. This issue is really a syntactic issue in the underlying database query we emit inside DatastoreAttachTarget::attach_resource_with_update_condition(). I'm fixing that now.