Force org status throws "more than 1 record" error

[kyle-blair]

Summary

An error is thrown when running sfdx force:org:status for a sandbox that has an in-progress refresh.

Steps To Reproduce:

1. Submit a refresh for an existing sandbox.
2. Run sfdx force:org:status --setalias test --sandboxname test --wait 60.

Expected result

The status command waits 60 minutes for the refresh to complete. If it completes within 60 minutes then the org details are printed and an sfdx alias is created.

Actual result

The command prints the error ERROR running force:org:status: The query returned more than 1 record.

System Information

{
  "cliVersion": "sfdx-cli/7.177.0",
  "architecture": "darwin-x64",
  "nodeVersion": "node-v18.12.0",
  "pluginVersions": [
    "@oclif/plugin-autocomplete 1.3.5 (core)",
    "@oclif/plugin-commands 2.2.1 (core)",
    "@oclif/plugin-help 5.1.17 (core)",
    "@oclif/plugin-not-found 2.3.7 (core)",
    "@oclif/plugin-plugins 2.1.6 (core)",
    "@oclif/plugin-update 3.0.6 (core)",
    "@oclif/plugin-version 1.1.3 (core)",
    "@oclif/plugin-warn-if-update-available 2.0.12 (core)",
    "@oclif/plugin-which 2.2.0 (core)",
    "alias 2.1.8 (core)",
    "apex 1.3.0 (core)",
    "auth 2.2.21 (core)",
    "community 2.0.15 (core)",
    "config 1.4.22 (core)",
    "custom-metadata 2.0.4 (core)",
    "data 2.1.10 (core)",
    "generator 2.0.10 (core)",
    "info 2.2.7 (core)",
    "limits 2.0.11 (core)",
    "org 2.2.12 (core)",
    "packaging 1.10.6 (core)",
    "schema 2.1.13 (core)",
    "signups 1.2.12 (core)",
    "source 2.3.2 (core)",
    "telemetry 2.0.3 (core)",
    "templates 55.1.0 (core)",
    "trust 2.0.4 (core)",
    "user 2.1.17 (core)",
    "@salesforce/sfdx-plugin-lwc-test 1.0.1 (core)",
    "salesforce-alm 54.8.3 (core)",
    "sfdmu 4.17.6 (user)"
  ],
  "osVersion": "Darwin 21.6.0",
  "shell": "bash",
  "rootPath": "/Users/user/.local/share/sfdx/client/7.177.0-a6c70c9"
}

Additional information

Using the --dev-debug flag I can see the cli is querying the SandboxProcess object filtering by the desired SandboxInfoId and WHERE Status != 'D'. Performing the same query myself I get two records as the error indicates; one with status Completed and one with Processing. Seems like the cli expectations do not match up with backend implementation or vice versa. This all worked fine a week or so ago. I can confirm the same issue is happening in our CI which uses sfdx version 7.165.0.

Some expanded debug output:

SingleRecordQuery_MultipleRecords: The query returned more than 1 record
    at Connection.singleRecordQuery (/Users/user/.local/share/sfdx/client/7.177.0-a6c70c9/node_modules/@salesforce/core/lib/org/connection.js:322:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.poll (/Users/user/.local/share/sfdx/client/7.177.0-a6c70c9/node_modules/@salesforce/core/lib/org/org.js:1065:43)
    at async doPoll (/Users/user/.local/share/sfdx/client/7.177.0-a6c70c9/node_modules/@salesforce/core/lib/status/pollingClient.js:60:26)
******

[github-actions]

Thank you for filing this issue. We appreciate your feedback and will review the issue as soon as possible. Remember, however, that GitHub isn't a mechanism for receiving support under any agreement or SLA. If you require immediate assistance, contact Salesforce Customer Support.

[mshanemc]

In the case of a refresh, which sandbox do you expect to receive the status of ?

[kyle-blair]

That's the interesting part, and that's the assumption that seems to have changed recently. The org status command used to report on the pending refresh. So if I had an existing sandbox, refreshed it, and called org status it would sit and wait until the refresh was complete. This was useful from a CI perspective to easily wait for a refresh to complete and authenticate to the refreshed sandbox at the same time. This could also be seen as a negative if you wanted access to the currently-live instance of the sandbox but that isn't (currently) a problem for us.

To answer your question, I think the most useful and user-friendly option would be the ability to choose. Perhaps a flag for the org status command such as --in-progress or --pending to indicate you want the status of the refresh not the current instance. Or reverse the flag to maintain backwards compatibility with the long-established behavior of giving the refresh status by default. Might need to consider how auto-activation factors in. But I think the least useful option is the behavior change I noticed recently which is to just throw an error.

Is this from an open-sourced plugin by any chance? Found it. Would love to go over the code, discuss options, and potentially contribute.

[kyle-blair]

I opened this as a bug not a feature request because a command that previously worked in a useful fashion now returns an error under somewhat common/expected circumstances. I would say reporting on the current instance status or refresh status is a matter of preference. However, the --wait flag on the command only makes sense to me in the context of waiting for a refresh to complete. It defaults to 2 and you actually can't set it lower than 2. To me, that further indicates the purpose of the command is to poll/wait for a refresh to complete. Always found that minimum wait odd though.

[github-actions]

This issue has not received a response in 7 days. It will auto-close in 7 days unless a response is posted.

[kyle-blair]

I've traced the root cause of this issue from the plugin-org repo. The problem lies in dependency code in this file node_modules/@salesforce/core/lib/org/org.js. I haven't been able to locate the source code/repo for this dependency; is it open-source? If it is (and you can provide a link to it) I can propose a change myself but there's quite a bit going on here that may or may not be for good reason. Here's an explanation of the issue:

The stack trace goes something like

src/commands/force/org/status.ts in plugin-org calls the sandboxStatus method:
this.org.sandboxStatus ->
authWithRetriesByName
authWithRetries (uses queryLatestSandboxProcessBySandboxName to query SandboxProcess object)
pollStatusAndAuth
querySandboxProcessBySandboxInfoId <- problem here

The pollStatusAndAuth method was already provided the SandboxProcess object in the options which was queried via the queryLatestSandboxProcessBySandboxName method. That query has an appropriate filter condition FROM SandboxProcess WHERE SandboxName='${sandboxNameIn}' AND Status != 'D' ORDER BY CreatedDate DESC LIMIT 1. The SandboxProcess object is passed to pollStatusAndAuth where for some reason it attempts to query the SandboxProcess object again. However, this time the querySandboxProcessBySandboxInfoId method is used which applies the problematic filter condition of FROM SandboxProcess WHERE SandboxInfoId='${id}' AND Status != 'D'. That filter yields both the completed and pending SandboxProcess objects even though the intent is to use the latest one (as indicated by the original query closest to the start of the command). One of the underlying method calls uses connection.singleRecordQuery which throws the error.

So this looks like an unintentional side effect of a long series of spaghetti method calls using a sprawling set of query functions. Since the relevant SandboxProcess object was: correctly queried early in the command, passed in the options to subsequent methods, and the subsequent methods don't seem to need any additional columns from the table, I would propose using the provided object instead of re-querying it. Of course there may be a reason for re-querying it which is relevant to other consumers of that method; I assume someone just changed that behavior for some other use case which introduced the bug here. So this may boil down to some refactoring and reduction of excessive helper methods.

Let me know your thoughts.

[github-actions]

This issue has not received a response in 7 days. It will auto-close in 7 days unless a response is posted.

[github-actions]

This issue has not received a response in 7 days. It will auto-close in 7 days unless a response is posted.

[kyle-blair]

This auto-close workflow is quite annoying because the issue is not going stale due to a lack of engagement on my end.

[github-actions]

This issue has not received a response in 7 days. It will auto-close in 7 days unless a response is posted.

[mrichmon12]

@mshanemc we have also been using this command for polling sandbox refreshes for multiple years, and just noticed that it has stopped working after upgrading CLI versions on our CI runners. We tried the new command (org:resume:sandbox) and the same issue is observed. I agree with @kyle-blair that this is a bug.

Why are we auto-closing bugs that are pending a response from Salesforce? Should we be putting in support tickets for this instead?

[hassanalamibmx]

same issue using the new org resume sandbox command with the attribute --use-most-recent

[kyle-blair]

same issue using the new org resume sandbox command with the attribute --use-most-recent

Yep, the new beta replacement command has the same issue both with and without the most recent flag.

[mkhokhlov]

Hey @mshanemc - is there any way you could look into this? The issue was auto-closed for no good reason and it is still happening in 7.194.1.

I did investigate this a little and posted my thoughts in #1749 (comment)

[kyle-blair]

@mshanemc or anyone, can we please reopen this? The issue is still occurring.

[mkhokhlov]

There's a newer ticket now - see #2238 .