You need to sign in to do that
Don't have an account?
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();
}
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
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:
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
That appears to have worked, thanks alot!
Jon
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
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.
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
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);
}
}
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.
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
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.
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.');
}
}
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);
}
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
Hi,
I have same issue with my batch apex. I have a query:
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.
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
What do you mean by "break the rules and run two queries" ?
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
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.
ucardil,
The exact issue you described is being discused here, http://boards.developerforce.com/t5/Apex-Code-Development/Spring-11-Batch-Apex-System-QueryException-Inline-query-has-too/td-p/246317 .
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
Here's what I did to fix: