Happy Business Starts Here

“First error: zqu:Too many query rows: 500001” running migration script even with setQueryRowLimit

Zuora Alumni

“First error: zqu:Too many query rows: 500001” running migration script even with setQueryRowLimit

Problem:

 

Getting the following error running migration script, even  with setQueryRowLimit
(Ref: https://knowledgecenter.zuora.com/AA_Whats_New/S_Previous_Z-Force_Releases/W_2015_Zuora_for_Salesfor...)

 

“First error: zqu:Too many query rows: 500001”

 

zqu.ZuoraQuotesUpgradeConversion6_51 zuc = new zqu.ZuoraQuotesUpgradeConversion6_51();​
zuc.setQueryRowLimit(15000);
ID batchprocessid = Database.executeBatch(zuc);






If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Zuora Alumni

Re: “First error: zqu:Too many query rows: 500001” running migration script even with setQueryRowLi

Solution:

 

In running the following query , 47843 records were returned. These quoteCharges cannot be migrated as the productRatePlanCharge associated with quoteCharges are null.

 

 

Select Id , zqu__TimeProductAdded__c , zqu__ProductRatePlanCharge__c , zqu__RatePlanCharge_ZuoraId__c,zqu__isMigrated__c from zqu__QuoteCharge__c where zqu__isMigrated__c = false and zqu__ProductRatePlanCharge__c = null

NOTE: Please refer to following article for more detail

 

http://community.zuora.com/t5/Zuora-for-Salesforce/Tips-to-troubleshoot-issues-migrating-QuoteCharge...

 

Based on reviewing Debug log and reviewing the code for line of Failure:

 

Class.zqu.ZuoraQuotesUpgradeConversion6_51.start: line 75, column 1

 

The error was thrown because of having 47843 Quote charge records that could not be migrated.

 

Here is line 75 of the code:

 

 

List<QuoteCharge__c> quoteChargesMissingProductReference = 
[Select Id , ProductRatePlanCharge__c , RatePlanCharge_ZuoraId__c from QuoteCharge__c where RatePlanCharge_ZuoraId__c in : zuoraIdToPrpcMap.keySet() AND ProductRatePlanCharge__c = null LIMIT :UPDATE_ROW_LIMIT];

 

 

Ther is a part of the script that queries all the null charges and sets them, but it isn't bulkified at this point.
We have an internal Jira Ticket  to get this addressed in future release of ZQuote.
50k SOQL limit is a cumulative limit and because there are 47843 records returned from this query, it was quickly hitting 50k.

 

As a workaround :


1)It is ok to set the ProductRatePlanCharge__c to a non-null value temporarily. This workaround should work, but these will have to be the correct ID for the plan.
or
2) Fix the ProductRatePlanCharge relationships so the script does not have to, causing it to error out at 50k.


The QuoteCharge has a zqu__ProductRatePlanCharge__c field that needs to be populated with the zqu__ProductRatePlanCharge__c.Id where QuoteCharge.zqu__RatePlanCharge_ZuoraID__c == zqu__ProductRatePlanCharge__c. zqu__ZuoraId__c

 

Just as a reference, following is sample Apex code that was used in one occassion, to update the Quote Charge records to populate Product ratePlanCharge id

 

DISCLAIMER: This sample code is provided just as a reference. This is NOT an official code from Zuora.

 

global class HelloWorldApexBatch implements Database.Batchable<sObject>{
    String query = 'Select id, Name, zqu__RatePlanCharge_ZuoraID__c, zqu__ProductRatePlanName__c, zqu__ProductRatePlanCharge__c from zqu__QuoteCharge__c where zqu__ProductRatePlanCharge__c = null';

    global Database.querylocator start(Database.BatchableContext BC){
                return Database.getQueryLocator(query);}

    global void execute(Database.BatchableContext BC, List<zqu__QuoteCharge__c> qc){
        //start

                
                System.debug('test qc='+qc.size() + ' example ='+qc[0]);

                Set<String> prc_zid = new Set<String>();
                Set<String> prc_name = new Set<String>();
                for(zqu__QuoteCharge__c q:qc){
                    prc_zid.add(q.zqu__RatePlanCharge_ZuoraID__c);
                    prc_name.add(q.zqu__ProductRatePlanName__c);
                    prc_name.add(q.Name);
                }

                List<zqu__ProductRatePlanCharge__c> prc = [select id, zqu__ZuoraId__c, Name from zqu__ProductRatePlanCharge__c where (zqu__ZuoraId__c in :prc_zid or Name in :prc_name) ];
                System.debug('test prc='+prc.size());
 
                System.debug('test qc='+qc.size() + ' example ='+qc[0]);
                System.debug('test prc='+prc.size());
            if(prc == null || prc.size()==0){
                System.debug('Could not find ProductRatePlanCharge with these zuora ids='+prc_zid +' -- and prc_name='+prc_name);
            }else{    
                Map<String, ID> prc_map = new Map<String, ID>();
                for(zqu__ProductRatePlanCharge__c p:prc){
                    prc_map.put(p.zqu__ZuoraId__c, p.id);
                    prc_map.put(p.Name, p.id);
                }

                for(zqu__QuoteCharge__c q:qc){
                    if(prc_map.get(q.zqu__RatePlanCharge_ZuoraID__c) != null){
                        q.zqu__ProductRatePlanCharge__c =prc_map.get(q.zqu__RatePlanCharge_ZuoraID__c);  
                    }else if(prc_map.get(q.zqu__ProductRatePlanName__c) != null){
                        q.zqu__ProductRatePlanCharge__c =prc_map.get(q.zqu__ProductRatePlanName__c);                                               
                    }else{
                        q.zqu__ProductRatePlanCharge__c =prc_map.get(q.Name);                        
                    }
                }
                System.debug('updated qc='+qc.size() + ' example ='+qc[0]);

                update(qc);
            } 
        //end   
    }

    global void finish(Database.BatchableContext BC){
        System.debug('done batching');
    }
}

 






If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √

View solution in original post

1 REPLY 1
Highlighted
Zuora Alumni

Re: “First error: zqu:Too many query rows: 500001” running migration script even with setQueryRowLi

Solution:

 

In running the following query , 47843 records were returned. These quoteCharges cannot be migrated as the productRatePlanCharge associated with quoteCharges are null.

 

 

Select Id , zqu__TimeProductAdded__c , zqu__ProductRatePlanCharge__c , zqu__RatePlanCharge_ZuoraId__c,zqu__isMigrated__c from zqu__QuoteCharge__c where zqu__isMigrated__c = false and zqu__ProductRatePlanCharge__c = null

NOTE: Please refer to following article for more detail

 

http://community.zuora.com/t5/Zuora-for-Salesforce/Tips-to-troubleshoot-issues-migrating-QuoteCharge...

 

Based on reviewing Debug log and reviewing the code for line of Failure:

 

Class.zqu.ZuoraQuotesUpgradeConversion6_51.start: line 75, column 1

 

The error was thrown because of having 47843 Quote charge records that could not be migrated.

 

Here is line 75 of the code:

 

 

List<QuoteCharge__c> quoteChargesMissingProductReference = 
[Select Id , ProductRatePlanCharge__c , RatePlanCharge_ZuoraId__c from QuoteCharge__c where RatePlanCharge_ZuoraId__c in : zuoraIdToPrpcMap.keySet() AND ProductRatePlanCharge__c = null LIMIT :UPDATE_ROW_LIMIT];

 

 

Ther is a part of the script that queries all the null charges and sets them, but it isn't bulkified at this point.
We have an internal Jira Ticket  to get this addressed in future release of ZQuote.
50k SOQL limit is a cumulative limit and because there are 47843 records returned from this query, it was quickly hitting 50k.

 

As a workaround :


1)It is ok to set the ProductRatePlanCharge__c to a non-null value temporarily. This workaround should work, but these will have to be the correct ID for the plan.
or
2) Fix the ProductRatePlanCharge relationships so the script does not have to, causing it to error out at 50k.


The QuoteCharge has a zqu__ProductRatePlanCharge__c field that needs to be populated with the zqu__ProductRatePlanCharge__c.Id where QuoteCharge.zqu__RatePlanCharge_ZuoraID__c == zqu__ProductRatePlanCharge__c. zqu__ZuoraId__c

 

Just as a reference, following is sample Apex code that was used in one occassion, to update the Quote Charge records to populate Product ratePlanCharge id

 

DISCLAIMER: This sample code is provided just as a reference. This is NOT an official code from Zuora.

 

global class HelloWorldApexBatch implements Database.Batchable<sObject>{
    String query = 'Select id, Name, zqu__RatePlanCharge_ZuoraID__c, zqu__ProductRatePlanName__c, zqu__ProductRatePlanCharge__c from zqu__QuoteCharge__c where zqu__ProductRatePlanCharge__c = null';

    global Database.querylocator start(Database.BatchableContext BC){
                return Database.getQueryLocator(query);}

    global void execute(Database.BatchableContext BC, List<zqu__QuoteCharge__c> qc){
        //start

                
                System.debug('test qc='+qc.size() + ' example ='+qc[0]);

                Set<String> prc_zid = new Set<String>();
                Set<String> prc_name = new Set<String>();
                for(zqu__QuoteCharge__c q:qc){
                    prc_zid.add(q.zqu__RatePlanCharge_ZuoraID__c);
                    prc_name.add(q.zqu__ProductRatePlanName__c);
                    prc_name.add(q.Name);
                }

                List<zqu__ProductRatePlanCharge__c> prc = [select id, zqu__ZuoraId__c, Name from zqu__ProductRatePlanCharge__c where (zqu__ZuoraId__c in :prc_zid or Name in :prc_name) ];
                System.debug('test prc='+prc.size());
 
                System.debug('test qc='+qc.size() + ' example ='+qc[0]);
                System.debug('test prc='+prc.size());
            if(prc == null || prc.size()==0){
                System.debug('Could not find ProductRatePlanCharge with these zuora ids='+prc_zid +' -- and prc_name='+prc_name);
            }else{    
                Map<String, ID> prc_map = new Map<String, ID>();
                for(zqu__ProductRatePlanCharge__c p:prc){
                    prc_map.put(p.zqu__ZuoraId__c, p.id);
                    prc_map.put(p.Name, p.id);
                }

                for(zqu__QuoteCharge__c q:qc){
                    if(prc_map.get(q.zqu__RatePlanCharge_ZuoraID__c) != null){
                        q.zqu__ProductRatePlanCharge__c =prc_map.get(q.zqu__RatePlanCharge_ZuoraID__c);  
                    }else if(prc_map.get(q.zqu__ProductRatePlanName__c) != null){
                        q.zqu__ProductRatePlanCharge__c =prc_map.get(q.zqu__ProductRatePlanName__c);                                               
                    }else{
                        q.zqu__ProductRatePlanCharge__c =prc_map.get(q.Name);                        
                    }
                }
                System.debug('updated qc='+qc.size() + ' example ='+qc[0]);

                update(qc);
            } 
        //end   
    }

    global void finish(Database.BatchableContext BC){
        System.debug('done batching');
    }
}

 






If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √

View solution in original post