+ Start a Discussion
JonSimmonsJonSimmons 

Invalid Query Locator

 

I have code in production that passes all unit tests and works fine in most cases but in some instances I am getting an Invalid Query Locator error and I can't figure out why.

 

 

My code is a custom rollup trigger that  triggers when a case is 'Closed'.  Rolling up data from cases to their parent accounts.

 

 

My code loops through trigger.new to collect the accountid associated with each case that has just closed.  It then loops through the list of accounts pulling and account record and any associated cases.  Finally it loops through the accounts, loops through each account's cases, and adds up all of the appropriate values depositing the result into fields within the Account then updates the account.  A simple rollup really.

 

 

This works fine most of the time but I have come across some accounts that fail this process, it doesn't matter which case is closed on the account, I always get the same error so the problem is with the account not with the case.  This account has roughly 340 closed cases, though I don't believe that is anywhere near any kind of limit.

 

 

To get to my point, I'm not sure what 'Invalid Query Locator' means in this particular case.  I have seen very little info about the Query Locator, all of it old, but it all suggests problems that occur when querys time out or have been 'garbage collected'.  In this case the error comes back immediately so timeout or memory cleanup should not be an issue.

 

 

 

Below is the relevant code, it's chopped to remove specific custom field names and such so it's pretty ugly.

 

Any help is greatly appreciated.

 

Many Thanks

Jon

 

 

 

 

//if the given trigger has just been closed then roll up the FQTD follup values

set<ID> FQTDAccountIDs = new set<ID>(); //list of Accounts involved with fqtd rollup calculations
map<ID, Account> AccountsToUpdate = new map<ID, Account>(); //the accounts that will be updated by this trigger


//prepare for FQTD rollup

Integer i = 0;
for(case theCase : trigger.new)//loop through all of the available records in the trigger
{
if((trigger.old[i].status != 'Closed') && (trigger.new[i].status == 'Closed'))
{
if(!FQTDAccountIDs.contains(trigger.new[i].accountid))
{
FQTDAccountIDs.add(trigger.new[i].accountid);
System.Debug('Adding Account to the FQTD List: ' + trigger.new[i].accountid);
}
}
i++;
}


//ok, so we may have a list of records to update, lets get to the action
if(FQTDAccountIDs.size() > 0) //do we have any records to update?
{

 

  //loop through all accounts and add them, with their cases, to a map for later looping

for(Account thisAccount : [Select Id, name, (Select casenumber, status From Cases where status = 'Closed') From Account where id in :FQTDAccountIDs])
{
AccountsToUpdate.put(thisAccount.id, thisAccount);
}


//loop through each specified account
for(Account currentAccount : AccountsToUpdate.values())
{




// ****** This next for loop is the apparent problem; System.Exception: invalid query locator ********

//loop through all cases associated with this account
for(Case theCase : currentAccount.Cases)
{

//calculate the fields and rollup the data
// currentAccount.rolledupfield += theCase.rollupfield1__c + theCase.rollupfield2__c
}



}


//update the Accounts with the new values
update AccountsToUpdate.values();



}

 

 

JonSimmonsJonSimmons

btw, I did think that possibly the Query Locator was invalid because there were no cases associated with the current account but this particular account has 340 cases.  Also I tried to wrap the loop in an IF block (see below) to verify that there were cases first but I receive an error stating 'Aggregate query has too many rows for direct assignment, use FOR loop'.

 

 

if(currentAccount.Cases.size() > 0) //Too many rows error { for(Case theCase : currentAccount.Cases) { } }

 

 

Thanks

Jon

 

 

CaptainObviousCaptainObvious

I encountered the very same problem recently when I created a trigger to roll up Sales values to Contracts.

I tracked the problem down to a relationship query... What was happening was that the code would loop through the first set of records normally (around 200 records), but because the next batch was retrieved through a 'QueryMore' it invalidated the 'query locator'.

In order to get around the problem, I had to break from best practices and split the query so that the associated sales were retreived in their own FOR loop.


My original loop:

for (Contract conTracts : [SELECT Id, Current_Value__c, Forecasted_Value__c, Product_Group__c, Current_Volume__c, (SELECT Id, Product__c, Quantity__c, Revenue_USD__c, Gross_Revenue_USD__c, Valid__c, PN_Account__r.PN_Type__c, Contract_Volume__c, Contract_Revenue__c, Status__c FROM PN_Sales__r) FROM Contract WHERE Id in :ContractIDs]){ ContractsToUpdate.put(conTracts.id,conTracts); }

The modified loop:

 

for (Contract conTracts : [SELECT Id, Product_Group__c, Current_Value__c, Forecasted_Value__c, Current_Volume__c FROM Contract WHERE Id in :ContractIDs]){ ContractsToUpdate.put(conTracts.id,conTracts); } //For every Contract... for(Contract conTract: ContractsToUpdate.values()){ //Loop through each associated sale for (PN_Sales__c pnSale: [SELECT Id, Product__c, Quantity__c, Revenue_USD__c, Gross_Revenue_USD__c, Valid__c, PN_Account__r.PN_Type__c, Contract_Volume__c, Contract_Revenue__c, Status__c FROM PN_Sales__c WHERE Contract__c =: conTract.id ]){ //Do Calculations .... }

I realize that SOQL statements within a FOR loop run the risk of quickly reaching the governor limits but this was the only solution I could come up with that would properly sum over 200 associated records :smileyindifferent:

Message Edited by CaptainObvious on 09-16-2009 04:34 PM
JonSimmonsJonSimmons

 

Thanks for the tip, this has suddenly become a pretty big problem so I will take a look at this right away and let you know how it turns out.

 

 

Thanks

Jon

 

JonSimmonsJonSimmons

 

That appears to have worked, thanks alot!

 

 

Jon

 

JonSimmonsJonSimmons

Ok, I take it all back.  It's not working correctly.  Now I'm getting an error stating that I'm running too many SOQL queries. 

 

Does anyone have any more ideas?

 

 

Thanks

Jon

 

CaptainObviousCaptainObvious
Sorry that didnt work out for you... were you doing a bulk operation (mass update?) at the time you received the 'Too many SOQL queries' error, or were you closing a single case? Could you also post your modified code?
JonSimmonsJonSimmons

To be honest I'm not entirely sure.  I was told that the customer implemented a workflow rule that is updating cases automatically, which is causing the rollup triggers to kick off.  The workflow is apparently updating large numbers of cases at the same time, causing the error.

 

 

TehNrdTehNrd
Anyone go to support with this? I'm getting the same error but I don't think the work around will work. Seems like a bug.
JonSimmonsJonSimmons

It IS a bug.  I worked with Salesforce support and QA and the developers, each gave me a workaround that brought me to another problem or bug.

 

In the end the answer was to run the code in an async (@future) class.

 

 

Jon

 

TehNrdTehNrd
Oh great! I am running it in an async class already!
TehNrdTehNrd

I bugging (no pun intended) support now as well. If you paste this into the system log and the account has 200 or more contacts this will fail. Seems like a bug.

 

 

List<Account> accts = new List<Account>();
for(Account a : [select Id, (Select Id from Contacts) from Account where Id = '00130000007yerz']){
accts.add(a);
}

for(Account a : accts){
//Loop through all contacts on account
for(Contact c : a.Contacts){
system.debug(c);
}
}

Message Edited by TehNrd on 02-05-2010 01:42 PM
SuperfellSuperfell
This should be in the soql-r docs somewhere, when you cursor the parent/outer cursor, all the previous child cursors are invalidated, if you want to access the contacts, you need to do it as you cursor over the accounts.
TehNrdTehNrd

Thanks for responding Simon, but you will have to forgive my ignorance, as I don't completely understand what you mean or what is going on here.

 

Here is the doc I think you are referring to but it does not help much, http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_querymore_querylocator.htm?SearchType=Stem

 

What I think you may be saying is to break the cardinal rule of not having SOQL statements in a for loop? Is this correct?

 

The example above errors out but if I query accounts and related contacts with batch apex and send them to a loop of Accounts and then a lopp of Contacts it works fine, even if contacts is greater than 200.

Message Edited by TehNrd on 02-02-2010 08:31 PM
JonSimmonsJonSimmons

 

Hi TehNrd,

 

I believe you are correct about Simon's response.  It's been a while since I was dealing with this issue but as I recall I had to break the inner query out of the loop, breaking the rule.  Putting the whole thing into an async class was required because async classes are not bound by the same limitations as triggers, making it 'ok' to break the rule.

 

 

Jon

 

TehNrdTehNrd
Thanks for the help, but as I mentioned before this is occuring in an async class. I've got a support case open so we will see what happens. Case#03279998
Message Edited by TehNrd on 02-03-2010 12:18 PM
SuperfellSuperfell

What i'm saying is that you have to access the inner query results within the context of the outer query. e.g.

 

for (Account a : [select ...., (select name from contacts) from account ...]) {

// a.contacts is only really valid within the scope of the query loop

 

// if you stashed away accounts in the loop, and access them here, the nested query cursor no longer exists

 

 

This is more obvious in the web services api, but as apex tries somewhat to hide the QueryResult object, its harder to see that there's query cursors going on when you have enough child rows. 

TehNrdTehNrd

Simon, thanks for the clarification as you're right, this hasn't come up much in regards to Apex.

 

One thing I've noticed is that this works fine with Batch Apex. Here is my setup. With the following batch apex example this can call the updateDomainOnAccount() method even if the account has 1000+ contacts and process all of these contacts with no issues. It would appear query cursors are maintained with batch apex. Is this correct?

 

It is this inconsistant behavior that led me to think this was a bug.

 

public class EmailDomains{

//Main method that has all of the logic, this can be called from Batch, trigger, etc
public static void updateDomainOnAccount(List<Account> accts){
for(Account a : accts){
for(Contact c : a.Contacts){
//Do cool stuff
}
}
}

}

 

BATCH APEX:
global class EmailDomainBatch implements Database.Batchable<sObject>{

public string query = 'select Id, (Select Id from Contacts) from Account';

global Database.QueryLocator start(Database.BatchableContext bc){
return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext bc, List<sObject> objects){
List<Account> accts = new List<Account>();
for(sObject s : objects){
Account a = (Account)s;
accts.add(a);
}

EmailDomains.updateDomainOnAccount(accts);
}

global void finish(Database.BatchableContext bc){
system.debug('All done.');
}
}

 

 

 

 

 

Message Edited by TehNrd on 02-03-2010 03:34 PM
SuperfellSuperfell

In batch apex the outer/primary query is driven by batch apex itself, your code is called for each iteration of the primary cursor, so by definition, all of you batch work is done within the scope of the query loop., e.g. its effectively equivilent to

 

for (Account [] accs : [select ....]) {

   batchClassInstance.execute(ctx, accs);

}

 

 

TehNrdTehNrd

Ahh, it's starting to come together now.

 

I setup the batch code above so I could utilize the 50mil record limit as I can query and process accounts that have thousands of Contacts with no issues. Had I queried only Accounts in the start method of the batch job I would need to query contacts in the execute loop but this is limited to 10,000 rows which averages out to only 50 contacts per account (assuming a default scope of 200 records per execute).

 

This works for batch but it will not work if invoked from triggers, visualforce, anonymous, etc.

 

It looks like I'll need to re-architect my code to something like this:

 

public static void calledFromBatch(List<Account> accts){
for(Account a : accts){
processAccount(a);
}
}

public static void calledFromTriggerVisualforceEtc(Set<Id>){
for(Account a : select Id, (Select Id from Contacts) from Account){
processAccount(a);
}
}

public void processAccount(Account acct){
for(Contact c : a.Contacts){
//Do cool stuff
}
}

 

Appreciate you sticking with me on this.

 

-Jason

Message Edited by TehNrd on 02-03-2010 04:09 PM
MevinMevin
Hello TehNrd
I am having the "Invalid Query Locator" in the execution of my batch apex
I have 3 inner select; hence after processing more than 333 records Im getting the "Invalid Query Locator"
I have tried your solution Im still getting this error.
Are you sure the above code processed more than 1000 child records?
Is there any other solution?
I need a solution urgently; can someone please help me

 

ucardilucardil

Hi,

 

I have same issue with my batch apex. I have a query:

select c.name, c.Id, (select Id from Orders__r) from company__c c

 

 

This worked well just before my salesforce instance was upgraded to Spring'11

Right after it I started to get Invalid Query Locator on every job run

 

Please, help.

 

 

 

JonSimmonsJonSimmons

 

The error means that that you subquery (select id from Orders__r) is returning too many records.  At the moment I don't recall what the limit was but perhaps the Spring 11 'Upgrade' lowered that limit.

 

You may have to break the rules and run two queries.

 

Good Luck

Jon

 

ucardilucardil

What do you mean by "break the rules and run two queries" ?

JonSimmonsJonSimmons

Salesforce would prefer if you write your query the way you have it, using a subquery and pulling one dataset.

 

To get around this issue (I believe that it's a bug) you may have to write your code with two queries looping through the result set of your main query (pulling your companies) then within that loop calling the subquery (pulling the orders from each company).   This does take more processing time and it's not the preferred method, but it hopefully will result in fewer records retured in each call of your orders subquery and eliminating your problem.

 

 

Jon

 

ucardilucardil

I think I found whats wrong with my code.

I use FOR loop to go through inner query. And this limited now to 200 records. :smileysurprised:

This worked just before Spring'11

I want to rollback to Winter'11 ghghghghgh :smileysad:

 

I need a way to calculate number of records in subquery. What is the right way to do it?

Of course I have a lot of records and I should to do it in batch.

TehNrdTehNrd
p1 force developerp1 force developer

I am also running into same issue.

 

I have a delete page which validates all the related records(child record) then deletes the child records first then the header record.

 

In the main query it has 6 sub queries.

 

When I try it with few records for all 6 child objects, It works fins. But when there are several records (e.g. 50+) then it is giving me invalid query locator error.

 

I am running it through a visuaforce page not a batch process.

 

does anyone got any ideas how to fix it.

 

I thought by summer'11 this issue must have fixed.

 

Thanks

 



Prajapati.LakhanPrajapati.Lakhan

Hi TehNrd,

 

I am getting this error with batch apex also if there are more than 200 child records exist - 'First error: Aggregate query has too many rows for direct assignment, use FOR loop'. 

 

I think its bug and it has to be admitted - Subquery/Nested queries can not be used in batch apex query locator, workaround is to use individual query in execute method

 

 

Thanks,

Lakhan

Kevin Antonioli 4Kevin Antonioli 4
Instead of performing a sub query to get the child records, perform 2 queries. First, perform the parent object query. Then, perform the child object query where the parent Id is in the parent list of records. Next, build a map that maps the parent Id to its associated list of child records. Finally, you can loop through each parent and child in similar fashion.

Here's what I did to fix:
// In this example, quote is the parent and quote line is the child.
// quoteIds is a set of Ids passed in the trigger

List<SBQQ__Quote__c> quotes = [SELECT Id FROM SBQQ__Quote__c WHERE Id :quoteIds];

List<SBQQ__QuoteLine__c> qlList = [SELECT Id, SBQQ__Quote__c FROM SBQQ__QuoteLine__c WHERE SBQQ__Quote__c IN :quotes];
// Map quote Ids to their associated list of quote lines:
Map<Id, List<SBQQ__QuoteLine__c>> quoteIdToQuoteLinesMap = new Map<Id, List<SBQQ__QuoteLine__c>>();
for(SBQQ__QuoteLine__c ql : qlList) {
    List<SBQQ__QuoteLine__c> quoteLines = quoteIdToQuoteLinesMap.get(ql.SBQQ__Quote__c) == null ? new List<SBQQ__QuoteLine__c>() : quoteIdToQuoteLinesMap.get(ql.SBQQ__Quote__c);
    quoteLines.add(ql);
    quoteIdToQuoteLinesMap.put(ql.SBQQ__Quote__c, quoteLines);
}    

// Loop through each quote:
for(SBQQ__Quote__c quote : quotes) {
	//for(SBQQ__QuoteLine__c ql : quote.SBQQ__QuoteLines__r) {
	if(quoteIdToQuoteLinesMap.get(quote.Id) != null) {
    	List<SBQQ__QuoteLine__c> innerQlList = quoteIdToQuoteLinesMap.get(quote.Id);
    	// Loop through the list of quote lines for this quote:
    	for(SBQQ__QuoteLine__c ql : innerQlList) {
    		// Do stuff...
    	}
    }
}