sql-ledger-users

[SL] Timeout on AR::Reports::Outstanding

[Martin B. <boe...@gm...>]

AR::Reports::Outstanding::next should show me all outstanding invoices. But 
loading the page times out (it stays busy all time without displaying 
anything on all browsers).

If I stop the postgresql database server while waiting for the result I get 
the following error:

> DBD::Pg::st execute failed: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> Error!
>
> SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
> a.duedate, (SELECT sum(ac.amount)
> FROM acc_trans ac
> JOIN chart c ON (c.id = ac.chart_id)
> WHERE ac.trans_id = a.id
> AND ac.approved = '1'
> AND c.link LIKE '%_tax%') * 1 AS tax,
> a.amount, (SELECT SUM(ac.amount) * -1 * 1
> FROM acc_trans ac
> JOIN chart c ON (c.id = ac.chart_id)
> WHERE ac.trans_id = a.id
> AND ac.approved = '1'
> AND (c.link LIKE '%AR_paid%'
> OR c.link LIKE '%AR_discount%'
> OR c.link = '')) AS paid,
> a.invoice, a.datepaid, a.terms, a.notes,
> a.shipvia, a.waybill, a.shippingpoint,
> e.name AS employee, vc.name, vc.customernumber,
> a.customer_id, a.till, m.name AS manager, a.curr,
> a.exchangerate, d.description AS department,
> a.ponumber, a.warehouse_id, w.description AS warehouse,
> a.description, a.dcn, pm.description AS paymentmethod,
> a.datepaid - a.duedate AS paymentdiff,
> ad.address1, ad.address2, ad.city, ad.zipcode, ad.country
>
> FROM ar a
> JOIN customer vc ON (a.customer_id = vc.id)
> JOIN address ad ON (ad.trans_id = vc.id)
> LEFT JOIN employee e ON (a.employee_id = e.id)
> LEFT JOIN employee m ON (e.managerid = m.id)
> LEFT JOIN department d ON (a.department_id = d.id)
> LEFT JOIN warehouse w ON (a.warehouse_id = w.id)
> LEFT JOIN paymentmethod pm ON (pm.id = a.paymentmethod_id)
>
>
> WHERE a.approved = '1'
> ORDER by 4 ASC,2,17
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.


So it seems that the server was busy fetching an sql-select. However if I take 
this query and paste it manually into psql I __instantly__ get the result 
(256 rows), so I don't think it's a problem of the database. Also my database 
is very small - about 64kb tar-gzip.

I am using FreeBSD 6.2 with Perl 5.8.8 (base system installations) with:
p5-DBD-Pg-1.49, p5-DBI-1.60.1, Sql-ledger 2.8.11 (happend also in earlier 
versions), apache-2.0.61_2. I tried Lighttpd, which produces same problem.

Same happens with AR::Reports::Transaction.

Anyone knows how to fix this? My collegues already want to purchase a 
commercial product because of this...(pastel partner) :-(

Thanks,
martin

Re: [SL] Timeout on AR::Reports::Outstanding

[Armaghan S. <sa...@le...>]

Martin Boese wrote:
> AR::Reports::Outstanding::next should show me all outstanding invoices. But 
> loading the page times out (it stays busy all time without displaying 
> anything on all browsers).
>
> If I stop the postgresql database server while waiting for the result I get 
> the following error:
>
>   
>> DBD::Pg::st execute failed: server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> Error!
>>
>> SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
>> a.duedate, (SELECT sum(ac.amount)
>> FROM acc_trans ac
>> JOIN chart c ON (c.id = ac.chart_id)
>> WHERE ac.trans_id = a.id
>> AND ac.approved = '1'
>> AND c.link LIKE '%_tax%') * 1 AS tax,
>> a.amount, (SELECT SUM(ac.amount) * -1 * 1
>> FROM acc_trans ac
>> JOIN chart c ON (c.id = ac.chart_id)
>> WHERE ac.trans_id = a.id
>> AND ac.approved = '1'
>> AND (c.link LIKE '%AR_paid%'
>> OR c.link LIKE '%AR_discount%'
>> OR c.link = '')) AS paid,
>> a.invoice, a.datepaid, a.terms, a.notes,
>> a.shipvia, a.waybill, a.shippingpoint,
>> e.name AS employee, vc.name, vc.customernumber,
>> a.customer_id, a.till, m.name AS manager, a.curr,
>> a.exchangerate, d.description AS department,
>> a.ponumber, a.warehouse_id, w.description AS warehouse,
>> a.description, a.dcn, pm.description AS paymentmethod,
>> a.datepaid - a.duedate AS paymentdiff,
>> ad.address1, ad.address2, ad.city, ad.zipcode, ad.country
>>
>> FROM ar a
>> JOIN customer vc ON (a.customer_id = vc.id)
>> JOIN address ad ON (ad.trans_id = vc.id)
>> LEFT JOIN employee e ON (a.employee_id = e.id)
>> LEFT JOIN employee m ON (e.managerid = m.id)
>> LEFT JOIN department d ON (a.department_id = d.id)
>> LEFT JOIN warehouse w ON (a.warehouse_id = w.id)
>> LEFT JOIN paymentmethod pm ON (pm.id = a.paymentmethod_id)
>>
>>
>> WHERE a.approved = '1'
>> ORDER by 4 ASC,2,17
>> server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>>     
>
>
> So it seems that the server was busy fetching an sql-select. However if I take 
> this query and paste it manually into psql I __instantly__ get the result 
> (256 rows), so I don't think it's a problem of the database. Also my database 
> is very small - about 64kb tar-gzip.
>
> I am using FreeBSD 6.2 with Perl 5.8.8 (base system installations) with:
> p5-DBD-Pg-1.49, p5-DBI-1.60.1, Sql-ledger 2.8.11 (happend also in earlier 
> versions), apache-2.0.61_2. I tried Lighttpd, which produces same problem.
>
> Same happens with AR::Reports::Transaction.
>
> Anyone knows how to fix this? My collegues already want to purchase a 
> commercial product because of this...(pastel partner) :-(
>
> Thanks,
> martin
Seems really strange error. Is this happening with all reports or just 
with these two?

Sometimes the left joins can take the database down for simple queries 
if there is no department/warehouse defined. But your testing does not 
point to database.

Which version of postgres? Try doing vacuumdb to the database

-- 
Purpose-built SQL-Ledger Hosting
http://www.ledger123.com/

Free trial available.
--

Re: [SL] Timeout on AR::Reports::Outstanding

[Martin B. <boe...@gm...>]

On Monday 04 February 2008 17:16:21 Armaghan Saqib wrote:
(...)
> Seems really strange error. Is this happening with all reports or just
> with these two?
>
> Sometimes the left joins can take the database down for simple queries
> if there is no department/warehouse defined. But your testing does not
> point to database.
>
> Which version of postgres? Try doing vacuumdb to the database

I am using 8.2.5 . I actually upgraded from 8.1 just to see if this problem 
will disappear.
I suspect more a DBD/DBI problem... Is there any way to test SL cgi script 
from the command line?


Martin

Re: [SL] Timeout on AR::Reports::Outstanding

[Dirk E. S. <ds...@ca...>]

>
> AR::Reports::Outstanding::next should show me all outstanding invoices.
> But
> loading the page times out (it stays busy all time without displaying
> anything on all browsers).

I had something similar, caused buy a stupid little mistake: A user had
registered transactions in the far future, around year 20007. It would
take an eternity to get reports as the javascript loading the date-options
was eating all the browser memory. - Maybe thats your problem; Just call a
trial balance starting next year (2009) until infinity....

Hope it helps

Enrique

-- 
Dirk Enrique Seiffert - Lintec S.A.
Ed. Torre del Reloj - Of. 401
Plaza de los Coches, Centro
Cartagena - Colombia
http://www.lintecsa.com

Re: [SL] Timeout on AR::Reports::Outstanding

[Armaghan S. <sa...@le...>]

Martin Boese wrote:
> On Monday 04 February 2008 17:16:21 Armaghan Saqib wrote:
> (...)
>   
>> Seems really strange error. Is this happening with all reports or just
>> with these two?
>>
>> Sometimes the left joins can take the database down for simple queries
>> if there is no department/warehouse defined. But your testing does not
>> point to database.
>>
>> Which version of postgres? Try doing vacuumdb to the database
>>     
>
> I am using 8.2.5 . I actually upgraded from 8.1 just to see if this problem 
> will disappear.
> I suspect more a DBD/DBI problem... Is there any way to test SL cgi script 
> from the command line?
>
>   
Put the following in a shell script and run in the sql-ledger folder. 
Make sure that line starting with ./ar.pl till end is one line.

#!/usr/local/bin/bash

./ar.pl 
"path=bin/mozilla&login=armaghan&password=armaghan&action=continue&nextsub=transactions&summarY=1&open=Y&l_amount=Y&l
_description=Y&l_invnumber=Y&l_name=Y&l_paid=Y&l_transdate=Y&vc=customer&ARAP=AR&outstanding=1"

-- 
Purpose-built SQL-Ledger Hosting
http://www.ledger123.com/

Free trial available.
-- 

Re: [SL] Timeout on AR::Reports::Outstanding

[Martin B. <boe...@gm...>]

OK,

I ran the ./ar.pl call from the command line with 'perl -d' and traced the 
execution. It stopped at SL/AA.pl(955) $sth->execute(); when it sends data to 
the database => Problem must be with Postgresql.

Switched on verbosity and debugging on postgres and found that when I send the 
query I get a:

> LOG:  could not send data to client: Operation not permitted

Google doesn't know anything about it, but I am highly sure its because I have 
an IPv6 address in /etc/hosts for localhost AND and IPv4. Changing it to 
127.0.0.1 and the timeout disappears. Instead I get a:

> DBD::Pg::st execute failed: out of memory for query result
> lost synchronization with server: got message type " ", length 1382381157

Also strange: To doublecheck that this is the cause (for whatever reasons, 
btw) I wrote this tiny perl program that just runs the query:

> use DBI;
> my $dbh = DBI->connect( 'dbi:Pg:dbname=ledger_maxnet;host=localhost',
>                         'pgsql',
>                         '',
>   );
>
> my $sth = $dbh->prepare($query);
> $sth->execute;

$query holds the SQL query that fails on the web-application. This ALWAYS 
WORKS, I called it hundred times.. You can skip the ";host=localhost" 
parameter, it makes no differences, it just works.

So it remains mystic. I have a memory error now I don't know how to fix. I 
already changed max_connections, shared_buffers, work_mem etc..  but it 
doesn't solve it. Also I tried to tune kernel parameters like 
kern.ipc.shmmax . The postgresql community suggests to use cursors for large 
results - but after all 256 rows isn't to much for that 1GB ram+1GB swap 
machine that I run it on by far.


Martin




On Tuesday 05 February 2008 11:37:57 Armaghan Saqib wrote:
> Martin Boese wrote:
> > On Monday 04 February 2008 17:16:21 Armaghan Saqib wrote:
> > (...)
> >
> >> Seems really strange error. Is this happening with all reports or just
> >> with these two?
> >>
> >> Sometimes the left joins can take the database down for simple queries
> >> if there is no department/warehouse defined. But your testing does not
> >> point to database.
> >>
> >> Which version of postgres? Try doing vacuumdb to the database
> >
> > I am using 8.2.5 . I actually upgraded from 8.1 just to see if this
> > problem will disappear.
> > I suspect more a DBD/DBI problem... Is there any way to test SL cgi
> > script from the command line?
>
> Put the following in a shell script and run in the sql-ledger folder.
> Make sure that line starting with ./ar.pl till end is one line.
>
> #!/usr/local/bin/bash
>
> ./ar.pl
> "path=bin/mozilla&login=armaghan&password=armaghan&action=continue&nextsub=
>transactions&summarY=1&open=Y&l_amount=Y&l
> _description=Y&l_invnumber=Y&l_name=Y&l_paid=Y&l_transdate=Y&vc=customer&AR
>AP=AR&outstanding=1"
>
> --
> Purpose-built SQL-Ledger Hosting
> http://www.ledger123.com/
>
> Free trial available.