[Koha] report behaves differently if run from intranet or the outside [SOLVED]

[Katrin Fischer]

Hi Alvaro,

the number of rows that can be requested via the JSON web service by
default is configurable with the SvcMaxReportRows system preference. But
it looks like you also found a workaround!

Hope this helps,

Katrin

On 11.07.20 18:38, Alvaro Cornejo wrote:
> Hi
>
> I found the solution:
>
> Place a "LIMIT XXX" at the end of the report. It seems that there is a hard
> limit for public reports that gets overridden if a LIMIT statement is
> placed on the query.
>
> Regards,
>
> Alvaro
>
>
> |----------------------------------------------------------------------------------------|
>   Stay safe / Cuídate/  Reste sécurisé
> *7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
> mesure.
>   *q *Recycle always / Recicla siempre / Recyclez toujours
>   P Print only if absolutely necessary / Imprime solo si es necesario /
> Imprimez seulement si nécessaire
>
>
> Le sam. 11 juil. 2020 à 11:27, Alvaro Cornejo <cornejo.alvaro at gmail.com> a
> écrit :
>
>> Hi
>>
>> It seems to be a problem with the public setup of the report. Hard limit
>> somewhere in the code?
>>
>> I changed almost all my reports to public to test and in all cases, I get
>> no more than the first 10 records.
>>
>> I initially thought something crashed on the process but json files are
>> completed, not corrupted or damaged.
>>
>> Regards
>>
>> Alvaro
>>
>>
>>
>> |----------------------------------------------------------------------------------------|
>>   Stay safe / Cuídate/  Reste sécurisé
>> *7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
>> mesure.
>>   *q *Recycle always / Recicla siempre / Recyclez toujours
>>   P Print only if absolutely necessary / Imprime solo si es necesario /
>> Imprimez seulement si nécessaire
>>
>>
>> Le sam. 11 juil. 2020 à 00:41, Alvaro Cornejo <cornejo.alvaro at gmail.com>
>> a écrit :
>>
>>> Hi
>>>
>>> I´ve been working with a report to get records stats and found a strange
>>> behaviour.
>>>
>>> The exactly same report provides different data if run it from the report
>>> interface in koha than calling it from the outside (public link) either
>>> from a perl script or from a browser (tested on ie / chrome)
>>>
>>> The query is quite simple:
>>> ------------------
>>> SELECT
>>> ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]') AS
>>> Tipo,
>>> FORMAT((COUNT(DISTINCT biblionumber)),0) AS Registros,
>>> FORMAT((COUNT(itemnumber)),0) AS Volúmenes
>>>
>>> FROM biblio_metadata
>>> LEFT JOIN items USING (biblionumber)
>>>
>>> GROUP BY Tipo
>>> WITH ROLLUP
>>> -------------------------
>>>
>>> if I run it from intranet I get (being set public):
>>>
>>> Tipo     Registros     Volúmenes
>>> Analitico de Revista     7      7
>>> ANLIB      6,939        10
>>> ANREV     8,351       671
>>> ART        96,109       95,054
>>> ART-E      50        1
>>> LIB        30,433        30,783
>>> LIB-A      4,919       91
>>> LIB-E      27       27
>>> SEP      1,304      1,300
>>> SER      165       485
>>> SER-E     1        2
>>> TESIS      9      9
>>> VID       4       4
>>>          148,318      128,444  (this is from ROLLUP
>>>
>>> If run it from the outside I get only the first 10 lines:
>>> (tested with script and manually parse the JSON and calling the public
>>> link with chrome and ie)
>>>
>>> Tipo     Registros     Volúmenes
>>> Analitico de Revista     7      7
>>> ANLIB      6,939        10
>>> ANREV     8,351       671
>>> ART        96,109       95,054
>>> ART-E      50        1
>>> LIB        30,433        30,783
>>> LIB-A      4,919       91
>>> LIB-E      27       27
>>> SEP      1,304      1,300
>>> SER      165       485
>>>
>>> I initially thought there were some bad records and fixed some that do
>>> not have a 942c field defined.
>>>
>>> I tested it with a condition to get only new records in the last 30 days
>>> and this behavior does not happen. Although the report have only 8 rows
>>> including the rollup
>>>
>>> Updated query
>>> ------------------
>>> SELECT
>>>
>>> ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]') AS
>>> Tipo,
>>> FORMAT((COUNT(DISTINCT biblionumber)),0) AS Registros,
>>> FORMAT((COUNT(itemnumber)),0) AS Volúmenes
>>>
>>> FROM biblio_metadata
>>> LEFT JOIN items USING (biblionumber)
>>>
>>> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= items.dateaccessioned
>>>
>>> GROUP BY Tipo
>>> WITH ROLLUP
>>> -------------------------
>>>
>>> Same result through intranet and public links with a script / browser
>>> Tipo Registros Volúmenes
>>> ANLIB    1    1
>>> ART    895     895
>>> LIB    60    62
>>> LIB-E    22    22
>>> SEP     21    21
>>> SER    1   1
>>> VID    4    4
>>>         1,004    1,006
>>>
>>> I´ve increased the interval of days to this second report until I got
>>> more than 10 lines and the behaviour repeats. From the public link I can
>>> get no more than 10 lines but from within koha I get all the lines.
>>>
>>> I´m running koha 19.11.07.000 with perl  5.026001 in ubuntu 18.04
>>>
>>> Any ideas? I guess there is no record limit since I´ve coverflow running
>>> and I get more than 10 records.
>>>
>>> Might be a timeout? It's a heavy query but runs for about 30 seconds on
>>> intranet and from a browser.
>>>
>>> Regards
>>>
>>> Alvaro
>>>
>>>
>>>
>>>
>>> |----------------------------------------------------------------------------------------|
>>>   Stay safe / Cuídate/  Reste sécurisé
>>> *7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et
>>> à mesure.
>>>   *q *Recycle always / Recicla siempre / Recyclez toujours
>>>   P Print only if absolutely necessary / Imprime solo si es necesario /
>>> Imprimez seulement si nécessaire
>>>
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha