| Bug #55686 | Query is not returning complete OUTPUT | ||
|---|---|---|---|
| Submitted: | 2 Aug 2010 13:44 | Modified: | 3 Sep 2010 13:54 |
| Reporter: | Anil Alpati | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.45 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 Aug 2010 13:49]
Valeriy Kravchuk
Please, send the results of EXPLAIN for your problematic query.
[2 Aug 2010 14:05]
Anil Alpati
Below is explain status for Query +----+-------------+-------+--------+---------------------------------------------------------------------------------------+-------------------------+---------+----------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------------------------------------------------------------------------+-------------------------+---------+----------------------------+--------+-------------+ | 1 | SIMPLE | c | ref | PRIMARY,STATUS_ID,BRANCH_ID,CUST_LO_IDX,CUSTOMER_BRANCH_SEARCH_IDX,PARENT_CUSTOMER_ID | STATUS_ID | 3 | const | 339934 | Using where | | 1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 2 | mifos.c.BRANCH_ID | 1 | | | 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 2 | mifos.c.LOAN_OFFICER_ID | 1 | Using index | | 1 | SIMPLE | ccf | ref | FIELD_ID,CUSTOMER_ID | CUSTOMER_ID | 4 | mifos.c.CUSTOMER_ID | 1 | | | 1 | SIMPLE | cm | ref | MEETING_ID,CUSTOMER_MEETING_IDX,CUST_INHERITED_MEETING_IDX | CUSTOMER_MEETING_IDX | 4 | mifos.c.CUSTOMER_ID | 1 | | | 1 | SIMPLE | g | eq_ref | PRIMARY,PARENT_CUSTOMER_ID | PRIMARY | 4 | mifos.c.PARENT_CUSTOMER_ID | 1 | | | 1 | SIMPLE | k | eq_ref | PRIMARY | PRIMARY | 4 | mifos.g.PARENT_CUSTOMER_ID | 1 | | | 1 | SIMPLE | rd | ref | PRIMARY,MEETING_ID | MEETING_ID | 4 | mifos.cm.MEETING_ID | 1 | Using index | | 1 | SIMPLE | rod | ref | DETAILS_ID | DETAILS_ID | 4 | mifos.rd.DETAILS_ID | 1 | | | 1 | SIMPLE | a | ref | PRIMARY,ACCOUNT_STATE_ID,CUSTOMER_ID_ACCOUNT_IDX | CUSTOMER_ID_ACCOUNT_IDX | 5 | mifos.cm.CUSTOMER_ID | 3 | Using where | | 1 | SIMPLE | la | eq_ref | PRIMARY,PRD_OFFERING_ID | PRIMARY | 4 | mifos.a.ACCOUNT_ID | 1 | | | 1 | SIMPLE | ls | eq_ref | PRIMARY | PRIMARY | 4 | mifos.a.ACCOUNT_ID | 1 | | | 1 | SIMPLE | lag | ref | ACCOUNT_ID | ACCOUNT_ID | 4 | mifos.ls.ACCOUNT_ID | 1 | | | 1 | SIMPLE | po | eq_ref | PRIMARY | PRIMARY | 2 | mifos.la.PRD_OFFERING_ID | 1 | Using index | | 1 | SIMPLE | pom | ref | PRD_OFFERING_ID,PRD_MEETING_ID | PRD_OFFERING_ID | 2 | mifos.po.PRD_OFFERING_ID | 1 | Using where | | 1 | SIMPLE | pm | eq_ref | PRIMARY | PRIMARY | 4 | mifos.pom.PRD_MEETING_ID | 1 | Using index | | 1 | SIMPLE | p_rd | ref | MEETING_ID | MEETING_ID | 4 | mifos.pom.PRD_MEETING_ID | 1 | | | 1 | SIMPLE | asch | ref | ACCOUNT_ID,NEW_STATUS | ACCOUNT_ID | 4 | mifos.la.ACCOUNT_ID | 1 | Using where | | 1 | SIMPLE | lsh | ref | ACCOUNT_ID | ACCOUNT_ID | 4 | mifos.la.ACCOUNT_ID | 16 | Using where | +----+-------------+-------+--------+---------------------------------------------------------------------------------------+-------------------------+---------+----------------------------+--------+-------------+
[2 Aug 2010 15:00]
Valeriy Kravchuk
Please, send the results of: select count(*) from customer c where c.status_id=3;
[3 Aug 2010 12:28]
Anil Alpati
+--------+ | Total | +--------+ | 450192 | +--------+
[3 Aug 2010 13:54]
Valeriy Kravchuk
Please, send the result of SHOW PROCESSLIST when this query hangs. Your my.cnf (or my.ini, you had not specified OS, so I am not sure) file content may be also useful. Looks like this query has to read too many rows on large data set (more than 7 million).
[3 Sep 2010 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Query is not returning complete OUTPUT. I was expected totally 80 lakhs but query results upto 30-40 lakhs after its not responding any thing in query browser. If I I check MySQL Administrator - Server Connections, It shows query is still running. But in command line or Query browser there is no response. Please let me know what was problem How to repeat: Below is the query is just for your reference. select a.`GLOBAL_ACCOUNT_NUM` as BANK_ID , 'ACTCRD' as SEGMENT_IDENTIFIER , a.`GLOBAL_ACCOUNT_NUM` as HM_UNIQ_RFR_NBR , a.`GLOBAL_ACCOUNT_NUM` as ACCOUNT_NUMBER , a.office_id as BRANCH_IDENTIFIER , k.global_cust_num as KENDRA_CENTRE_IDENTIFIER , p.personnel_id as LOAN_OFFICER_ORIG_LOAN , DATE_FORMAT(curdate(),'%d%m%Y') as DATE_OF_ACCOUNT_INFORMATION , if(o.search_id like '1.1.1%',"GL","IL") as LOAN_CATEGORY , g.global_cust_num as GROUP_ID , la.business_activities_id as LOAN_PURPOSE , a.account_state_id as ACCOUNT_STATUS , DATE_FORMAT(a.created_date,'%d%m%Y') as APPLICATION_DATE , DATE_FORMAT(asch.changed_date,'%d%m%Y') as SANCTIONED_DATE , DATE_FORMAT(la.disbursement_date,'%d%m%Y') as DATE_OPENED , DATE_FORMAT(a.closed_date,'%d%m%Y') as DATE_CLOSED , ROUND(la.loan_amount,0) as APPLIED_FOR_AMOUNT , ROUND(la.loan_amount,0) as LOAN_AMOUNT_SANCTIONED , ROUND(la.loan_amount,0) as TOTAL_AMOUNT_DISBURSED , la.no_of_installments as NUMBER_OF_INSTALLMENTS , concat(p_rd.recurrence_id,"-",p_rd.recur_after) as REPAYMENT_FREQUENCY , ROUND((lsh.principal+lsh.interest),0) as MINIMUM_AMT_DUE , ROUND((ls.orig_principal-ls.principal_paid),0) as CURRENT_BALANCE , ROUND(ifnull(lag.overdue_principal,0),0) as AMOUNT_OVERDUE , lag.days_in_arrears as DPD , null as WRITE_OFF , null as DATE_WRITE_OFF , null as WRITE_OFF_REASON , null as NO_OF_MEETING_HELD , null as NO_OF_ABSENTEES_IN_MEETING , 'Y' as INSURANCE_INDICATOR , 'L02' as TYPE_OF_INSURANCE , null as SUM_ASSURED , rod.days as AGREED_MEETING_WEEK_DAY , ccf.field_value as AGREED_MEETING_DAY_TIME , null as RESERVED_FOR_FUTURE_USE , null as RESERVED_FOR_FUTURE_USE1 , c.global_cust_num as PARENT_ID , ------ the member/client id must be here 1 as EXTRACTION_FILE_ID , null as SEVERITY , null as DATE_OF_LAST_PAYMENT , --- last date when a payment/ collection was made null as ASSET_CLASIFICATION , -- classification of members 'MFIGRAMEEN' as MEMBER_CODE , 'MFIGRAMEEN' as MEMBER_SHRT_NM , null as ACCOUNT_TYPE , null as OWNERSHIP_IND , null as OLD_MEMBER_CODE , null as OLD_MEMBER_SHRT_NM , null as OLD_ACCOUNT_NBR, null as CIBIL_ACT_Status from account a inner join customer c on (c.customer_id=a.customer_id) inner join office o on (c.branch_id=o.office_id) inner join personnel p on (c.loan_officer_id=p.personnel_id) inner join loan_account la on (la.account_id=a.account_id) inner join prd_offering po on (la.prd_offering_id=po.prd_offering_id) inner join prd_offering_meeting pom on (po.prd_offering_id=pom.prd_offering_id) inner join meeting pm on (pom.prd_meeting_id=pm.meeting_id) inner join recurrence_detail p_rd on (p_rd.meeting_id=pm.meeting_id) inner join loan_summary ls on (la.account_id=ls.account_id) inner join loan_schedule lsh on (a.account_id=lsh.account_id and lsh.installment_id=1) inner join account_status_change_history asch on (asch.account_id=a.account_id and new_status=3 ) left join loan_arrears_aging lag on (a.account_id = lag.account_id) inner join mifos.customer g on (c.parent_customer_id=g.customer_id) inner join mifos.customer k on (g.parent_customer_id=k.customer_id) inner join customer_meeting cm on (c.customer_id=cm.customer_id) left join customer_custom_field ccf on (c.customer_id=ccf.customer_id and ccf.field_id=5) inner join recurrence_detail rd on (cm.meeting_id = rd.meeting_id) inner join recur_on_day rod on (rd.details_id=rod.details_id) where a.account_state_id in (5,9) and c.status_id=3;