[Koha-bugs] [Bug 2084] overdues.pl hangs when much data

[bugzilla-daemon at pippin.metavore.com]

http://bugs.koha.org/cgi-bin/bugzilla/show_bug.cgi?id=2084


rch at liblime.com changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |REOPENED
         Resolution|FIXED                       |




------- Comment #4 from rch at liblime.com  2008-05-19 21:43 -------
This is not fixed.
One should not be allowed to run this kind of report on a production server
during up hours, imo.  At least not without a confirm dialog ( 'This is a
processor-intensive report that should not be run on a production server. 
Click here to continue').  Even better would be to actually fix the query or
indexes so we're not copying to tmp table.

here's 
show full processlist after running this report:

|  354 | dbadmin | localhost | koha_db | Query   | 60732 | Sending data        
| SELECT 
                GROUP_CONCAT(accountlines.accounttype ORDER BY
accountlines.date DESC SEPARATOR '<br/>') as l_accounttype,
                GROUP_CONCAT(description ORDER BY accountlines.date DESC
SEPARATOR '<br/>') as l_description,
                GROUP_CONCAT(round(amountoutstanding,2) ORDER BY
accountlines.date DESC SEPARATOR '<br/>') as l_amountoutstanding, 
                GROUP_CONCAT(accountlines.date ORDER BY accountlines.date DESC
SEPARATOR '<br/>') as l_date, 
                GROUP_CONCAT(accountlines.itemnumber ORDER BY accountlines.date
DESC SEPARATOR '<br/>') as l_itemnumber, 
                count(*) as cnt, 
                max(accountlines.date) as maxdate,
                round(sum(amountoutstanding),2) as sum_amount, 
                borrowers.borrowernumber as borrowernumber, 
                borrowers.surname as surname, 
                borrowers.firstname as firstname, 
                borrowers.email as email,
                borrowers.phone as phone,
                accountlines.itemnumber,
                description, 
                accountlines.date as accountdate
                FROM 
                        borrowers, accountlines
                WHERE 
                        accountlines.borrowernumber = borrowers.borrowernumber
                AND accountlines.amountoutstanding <> 0 
                AND accountlines.borrowernumber 
                        IN (SELECT borrowernumber FROM accountlines 
                                where borrowernumber >= 0
                                 AND date <= '2008-05-19' 
                                GROUP BY accountlines.borrowernumber HAVING
sum(amountoutstanding) >= '25' ) 
                AND accountlines.borrowernumber 
                        NOT IN (SELECT borrowernumber FROM accountlines 
                                where borrowernumber >= 0
                                 AND date < '2007-05-19' 
                                GROUP BY accountlines.borrowernumber HAVING
sum(amountoutstanding) >= '25' ) 
 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= '25' 
ORDER BY surname, firstname | 
| 3364 | dbadmin | localhost | koha_db | Sleep   |    17 |                     
| NULL                                                                          

the bad one:  copying to tmp table :: 

| 3365 | dbadmin | localhost | koha_db | Query   |    17 | Copying to tmp table
| SELECT date_due,concat(surname,' ', firstname) as borrower, 
  borrowers.phone, borrowers.email,issues.itemnumber, items.barcode,
biblio.title,
biblio.author,borrowers.borrowernumber,biblio.biblionumber,borrowers.branchcode 
  FROM issues
LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber )
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber )
WHERE 1=1  && date_due<'2008-05-20'  ORDER BY date_due,borrower 




------- You are receiving this mail because: -------
You are the QA contact for the bug, or are watching the QA contact.