report behaves differently if run from intranet or the outside
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
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@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
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@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@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
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@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@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@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
participants (2)
-
Alvaro Cornejo -
Katrin Fischer