[Koha] Coverflow installation problems

[Quỳnh Vũ Đỗ]

Hello everyone,
I am using a test database of our Library on Koha 19.11.06 (just
auto-upgraded from 19.11.05) on Ubuntu 16.04.6 LTS.
In the process of customizing the OPAC and installing the COverflow plugin
as described at:
https://bywatersolutions.com/education/coverflow-blog-post-update
I have created the sql report by copying and paste the described content on
that page:

Report SQL:

SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ‘ ‘, 1) AS isbn, b.title

FROM items i

LEFT JOIN biblioitems m USING (biblioitemnumber)

LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)

WHERE DATE_SUB(CURDATE(),INTERVAL 180 DAY) <= i.dateaccessioned AND
m.isbn IS NOT NULL AND m.isbn != ”

ORDER BY rand()

LIMIT 7


The only modification I have brought to the original code was to change the
INTERVAL from 30 DAY to 180 DAY as the last operation made on that database
was made on 25th February 2020.
On running the report I got the following error message:
"The database returned the following error:
Incorrect parameters in the call to native function 'substring_index'
Please check the log for further details"

So I checked the log at /var/log/koha/library/intranet-error.log which
indicated:

[Sun Jun 07 08:21:27.385286 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl: \r:
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
[Sun Jun 07 08:21:27.385426 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
LIMIT ?, ?" with ParamValues: 0=0, 1=7] at
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl line 1001.:
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
[Sun Jun 07 08:21:27.385605 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
DBD::mysql::st fetchrow_arrayref failed: fetch() without execute() [for
Statement "SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, \xe2\x80\x98
\xe2\x80\x98, 1) AS isbn, b.title\r:
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
[Sun Jun 07 08:21:27.385660 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
FROM items i\r: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl,
referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
[Sun Jun 07 08:21:27.385745 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
LEFT JOIN biblioitems m USING (biblioitemnumber)\r:
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
[Sun Jun 07 08:21:27.385834 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)\r:
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
[Sun Jun 07 08:21:27.385966 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
WHERE DATE_SUB(CURDATE(),INTERVAL 180 DAY) <= i.dateaccessioned AND m.isbn
IS NOT NULL AND m.isbn != \xe2\x80\x9d\r:
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
[Sun Jun 07 08:21:27.386024 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
ORDER BY rand()\r: /usr/share/koha/intranet/cgi-bin/reports/
guided_reports.pl, referer:
http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
[Sun Jun 07 08:21:27.386072 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl: \r:
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
[Sun Jun 07 08:21:27.386321 2020] [cgi:error] [pid 30474] [client
127.0.0.1:39572] AH01215: [Sun Jun  7 08:21:27 2020] guided_reports.pl:
LIMIT ?, ?" with ParamValues: 0=0, 1=7] at
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl line 839.:
/usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer:
http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl

As I have limited knowledge in SQL coding, I am unsure of what to do (I
remember to have tested that SQL code with Koha 16.05 and it worked then).

Does anyone have met that problem and possibly have a hint for resolution?
Thanks
=================
M. Vũ Đỗ Quỳnh (Ph.D)
Trường Đại học Thăng Long - Université de Thang Long - Thang Long
University (Hanoi, Vietnam)
Phó Hiệu Trưởng, phụ trách HTQT - Vice-recteur chargé de la coopération
internationale - Vice-Rector in charge of International Cooperation
Web: http://thanglong.edu.vn/