force:data:soql:query limited to 2000 rows?

[jhawk-4]

I recently updated my CLI from 7.147 to 7.151 and now when I run sfdx force:data:soql:query the results are limited to 2000 rows. Previously, I was able to return all data from the query. Is this a recent change that was intentionally made?

CLI Version:
sfdx-cli/7.151.1

OS:
MacOS 11.6.5

Node:
node-v16.15.0

[ghost]

Yeah... this is a problem for me too.
In the past the default limit was 50k, but even that could be exceeded with environment variable SFDX_MAX_QUERY_LIMIT.

As of 7.151 seems capped at 2k and the env variable has no effect.

I'm deeply hoping this regression can be reverted soon. I'm at a loss atm as to how to go about my job.

sfdx-cli/7.151.1 win32-x64 node-v16.15.0

[habl]

I regret updating. It was the only tool allowing to export data from a Linux environment.

[mnunezdm]

You can workaround this downgrading the installed version

Just did it using:

sfdx update --from-local

Select the desired version (in my case was 7.150.0-88c96e0)

sfdx --version

And I was able to query data without limits

[uip-robot-zz]

This issue has been linked to a new work item: W-11204187

[cristiand391]

Hey all 👋🏼 !

Sorry for the inconvenience, a fix is coming in the RC today!

[cristiand391]

Hi folks, this is fixed in the latest rc sfdx v7.153.0, please let us know if you find any issue!

I'm going to keep this open until we promote it to latest next Thursday.

[mnunezdm]

Hi!

Still having this issue in 7.153.0

❯ sfdx --version
sfdx-cli/7.153.0 darwin-x64 node-v16.15.0

I have tried setting the environment variable, and the config

❯ ./query.sh
INFO: Retrieving day 27 of month 5
 ›   Warning: The query result is missing 5141 records due to a 10000 record limit. Increase the number of records returned by setting the config value "maxQueryLimit" or the environment
 ›    variable "SFDX_MAX_QUERY_LIMIT" to 6141 or greater than 10000.
Querying Data... done
FINE: Retrived     1000 rows
❯ SFDX_MAX_QUERY_LIMIT=50000 ./query.sh
INFO: Retrieving day 27 of month 5
 ›   Warning: The query result is missing 5141 records due to a 50000 record limit. Increase the number of records returned by setting the config value "maxQueryLimit" or the environment
 ›    variable "SFDX_MAX_QUERY_LIMIT" to 6141 or greater than 50000.
Querying Data... done
FINE: Retrived     1000 rows
❯ sfdx config:list
=== List Config

 Name          Value Location
 ───────────── ───── ────────
 maxQueryLimit 50000 Local
❯ ./query.sh
INFO: Retrieving day 27 of month 5
 ›   Warning: The query result is missing 5141 records due to a 50000 record limit. Increase the number of
 ›   records returned by setting the config value "maxQueryLimit" or the environment variable
 ›   "SFDX_MAX_QUERY_LIMIT" to 6141 or greater than 50000.
Querying Data... done
FINE: Retrived     1000 rows

[ghost]

You can workaround this downgrading the installed version

Just did it using:

sfdx update --from-local

Select the desired version (in my case was 7.150.0-88c96e0)

sfdx --version

And I was able to query data without limits

Thank you @mnunezdm that is extremely helpful! (here I was downloading data one query at a time using workbench!)

[cristiand391]

Thanks for testing it @mnunezdm !

Yeah, after the fix the default limit was set to 10000 instead of 50000, that was a mistake.

❯ ./query.sh
INFO: Retrieving day 27 of month 5
› Warning: The query result is missing 5141 records due to a 10000 record limit. Increase the number of records returned by setting the config value "maxQueryLimit" or the environment
› variable "SFDX_MAX_QUERY_LIMIT" to 6141 or greater than 10000.
Querying Data... done
FINE: Retrived 1000 rows

In this case the recordSize in your org is 6141 so even without setting a custom limit that should fetched all records.

Where does FINE: Retrived 1000 rows comes from? are you parsing the printed table or the json output?

[mnunezdm]

Exactly @cristiand391, I have a script that parses the output. It's still limited to 1000 records

Again rolling back to 150 fixes the issue

❯ sfdx update --from-local
Found versions: 
     7.148.2-892c42e
     7.149.1-3881a5a
     7.149.1-3881a5a.partial.17197
     7.150.0-88c96e0
     7.150.0-88c96e0.partial.20339
     7.152.0-3054818
     7.153.0-c083a11

Enter a version to update to: 7.150.0-88c96e0

Updating to an already installed version will not update the channel. If autoupdate is enabled, the CLI will eventually be updated back to stable-rc.
sfdx-cli: Updating CLI... done
❯ ./query.sh 
INFO: Retrieving day 27 of month 5
 ›   Warning: sfdx-cli update available from 7.150.0 to 7.151.1.
Querying Data... done
FINE: Retrived     6141 rows

[VinceFINET]

Same issue on my side.
When using the --dev-debug flag i see that the cli is calling the REST API "query more" URLs but only retrieving the first 2000 rows of the first REST API call.

In my example i see the logs:

sf:DataSoqlQueryCommand DEBUG Query complete with 67991 records returned +617ms
sf:DataSoqlQueryCommand INFO Total number of records retrieved: 2000. +0ms

67.991 records retrieved by CLI via the REST API
But only returning 2.000 records in the ouput.

Downgrade to the 7.150 resolve the issue
See https://developer.salesforce.com/docs/atlas.en-us.sfdx_setup.meta/sfdx_setup/sfdx_setup_install_cli.htm#sfdx_setup_install_cli_olderversions

[cristiand391]

Thank you all for testing the it!

After some testing we found the fix worked when running the plugin isolated but not when it's bundled and published with sfdx.
There's also another bug (probably in the jsforce v2 lib) that it's causing sfdx miss some records when specifying a low limit.

Current workaround: downgrade to sfdx v7.150

Sorry again for the inconvenience, we are prioritizing this to get fixed in the next RC.

cc @WillieRuemmele

[kdoruibin]

I don't know if this should be filed as a new bug but it seems related to this bug. We have all our pipelines being down because we are relying on sfdx force:package:version:list --orderby=CreatedDate --packages <Package Alias> to fetch the latest package version (at the end of the list) to install.

What we run in our pipelines to get the latest package version:

version_id=$(sfdx force:package:version:list --orderby=CreatedDate --packages="$pkg" --loglevel ${{ parameters.logLevel }} --targetdevhubusername DevHub --json \
          | jq '.result[] | select(.Description == "develop_ver" or .Description == "master_ver").SubscriberPackageVersionId' | tail -n1 | tr -d '"')

Somehow force:package:version:list must be using `force:data:soql:query: under the hood.

Findings supporting this:
Running

sfdx force:package:version:list --orderby=CreatedDate --packages <Package Alias> > packageVersions.txt

On 7.154.0 gives me a list of 3490 package versions:

=== Package Versions [3490]

On 7.155.1 I get a list of 2000 package versions:

=== Package Versions [2000]

[shetzel]

force:package:version:list does not use the query command. However, both those commands (as well as some others) use a query function provided by the jsforce library and we have been moving plugins to the new major version of jsforce. There are some differences with auto-fetching queries in the new version that is causing problems so we're in the process of fixing it. ETA is the next CLI version but it's still in progress.

[hcerpandam]

Local against SF

$ npm update --global sfdx-cli
$ sfdx config:set maxQueryLimit=2000000

sfdx --version
sfdx-cli/7.155.1 win32-x64 node-v16.9.1

sfdx plugins --core
@oclif/plugin-autocomplete 0.3.0 (core)
@oclif/plugin-commands 1.3.0 (core)
@oclif/plugin-help 3.3.1 (core)
@oclif/plugin-not-found 1.2.6 (core)
@oclif/plugin-plugins 1.10.11 (core)
@oclif/plugin-update 1.5.0 (core)
@oclif/plugin-warn-if-update-available 1.7.3 (core)
@oclif/plugin-which 1.0.4 (core)
@salesforce/sfdx-plugin-lwc-test 0.1.7 (core)
alias 2.0.1 (core)
apex 0.13.0 (core)
auth 2.1.0 (core)
community 2.0.0 (core)
config 1.4.12 (core)
custom-metadata 2.0.0 (core)
data 2.0.3 (core)
generator 2.0.1 (core)
info 2.0.1 (core)
limits 2.0.1 (core)
org 1.13.2 (core)
salesforce-alm 54.5.1 (core)
schema 2.1.1 (core)
sfdx-cli 7.155.1 (core)
sfdx-git-delta 5.3.0
signups 1.1.2 (core)
source 1.10.2 (core)
telemetry 2.0.0 (core)
templates 54.8.0 (core)
trust 2.0.1 (core)
user 2.0.2 (core)

That since yesterday works like a charm

Linux server against Salesforce

$ sfdx update
$ sfdx config:set maxQueryLimit=2000000

sfdx --version
sfdx-cli/7.155.1 linux-x64 node-v16.15.1

sfdx plugins --core
@oclif/plugin-autocomplete 0.3.0 (core)
@oclif/plugin-commands 1.3.0 (core)
@oclif/plugin-help 3.3.1 (core)
@oclif/plugin-not-found 1.2.6 (core)
@oclif/plugin-plugins 1.10.11 (core)
@oclif/plugin-update 1.5.0 (core)
@oclif/plugin-warn-if-update-available 1.7.3 (core)
@oclif/plugin-which 1.0.4 (core)
@salesforce/sfdx-plugin-lwc-test 0.1.7 (core)
@salesforce/sfdx-scanner 2.13.5
alias 2.0.1 (core)
apex 0.13.0 (core)
auth 2.1.0 (core)
community 2.0.0 (core)
config 1.4.12 (core)
custom-metadata 2.0.0 (core)
data 2.0.3 (core)
generator 2.0.1 (core)
info 2.0.1 (core)
limits 2.0.1 (core)
org 1.13.2 (core)
salesforce-alm 54.5.1 (core)
schema 2.1.1 (core)
sfdx-cli 7.155.1 (core)
signups 1.1.2 (core)
source 1.10.2 (core)
telemetry 2.0.0 (core)
templates 54.8.0 (core)
trust 2.0.0 (core)
user 2.0.2 (core)

That is still retrieving 2.000 rows

[cristiand391]

the fix hasn't been released yet, if you wanna try it now you can install latest plugin-data manually:

sfdx plugins:install [email protected]

******************************************
// see if the env var and config setting works in your environment
******************************************

// uninstall the plugin:
sfdx plugins:uninstall data

please make sure to uninstall the plugin after you are done, otherwise sfdx will keep using that version after you update the CLI.

[shetzel]

This should now be fixed in the latest production CLI, v7.156.1

[mnunezdm]

Just tried and works like a charm!

Thx team!

[ghost]

Thanks @shetzel @cristiand391 et al. And thanks again @mnunezdm for the downgrade workaround that helped me during the interim 🙏