Bug 33800 - Error 500 in issues_stats.pl
Summary: Error 500 in issues_stats.pl
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: 22.11
Hardware: All All
: P5 - low enhancement
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-05-23 03:01 UTC by Eugene Espinoza
Modified: 2024-07-04 20:37 UTC (History)
1 user (show)

See Also:
GIT URL:
Initiative type: ---
Sponsorship status: ---
Comma delimited list of Sponsors:
Crowdfunding goal: 0
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Eugene Espinoza 2023-05-23 03:01:21 UTC
We're in Koha 22.11.04 and in "/cgi-bin/koha/reports/borrowers_stats.pl" and just the default "itemtype" and "issuing library" buttons ticked, cell value = count total items and output = "to screen into browser". After submitting, we're getting "Error 500". In the log, we were getting this:
[2023/05/23 10:13:49] [WARN] Argument "2023-05-01" isn't numeric in numeric lt (<) at /usr/share/koha/intranet/cgi-bin/reports/issues_stats.pl line 214.
[2023/05/23 10:13:49] [WARN] Argument "2023-05-31" isn't numeric in numeric lt (<) at /usr/share/koha/intranet/cgi-bin/reports/issues_stats.pl line 214.

This worked in previous Koha ILS version. It seems there is a bug. Thanks!
Comment 1 Eugene Espinoza 2023-06-23 13:48:12 UTC
Additionally, after updating to 22.11.06, we are getting the error message below in plack-error.log:
CGI::Compile::ROOT::usr_share_koha_intranet_cgi_2dbin_reports_issues_stats_2epl::calculate(): DBI Exception: DBD::mysql::st execute failed: The used SELECT statements have a different number of columns at /usr/share/koha/intranet/cgi-bin/reports/issues_stats.pl line 92
Comment 2 Eugene Espinoza 2023-06-28 07:49:49 UTC
Hi! I've reread my first comment, it should be "/cgi-bin/koha/reports/issues_stats.pl" and "/cgi-bin/koha/reports/borrowers_stats.pl"
Comment 3 Marcel de Rooy 2023-06-28 11:31:48 UTC
(In reply to Eugene Espinoza from comment #1)
> Additionally, after updating to 22.11.06, we are getting the error message
> below in plack-error.log:
> CGI::Compile::ROOT::
> usr_share_koha_intranet_cgi_2dbin_reports_issues_stats_2epl::calculate():
> DBI Exception: DBD::mysql::st execute failed: The used SELECT statements
> have a different number of columns at
> /usr/share/koha/intranet/cgi-bin/reports/issues_stats.pl line 92

Eugene,
This SQL error seems to be caused by a UNION statement.
The only one in this script is in the calculate routine (called on L92).
If you go there, you will see:
    $strcalc .= "LEFT JOIN (SELECT * FROM items UNION SELECT * FROM deleteditems) items ON statistics.itemnumber=items.itemnumber "

You could try SELECT * FROM items UNION SELECT * FROM deleteditems in MySQL client to see if it works. It does on current master.
If it doesnt, please compare the columns of items and deleteditems in your database, and tell us the differences. Did you make modifications to the tables?
Comment 4 Eugene Espinoza 2023-06-28 14:58:54 UTC
Marcel,
Thanks for commenting! I am missing the paidfor column in my deleteditems table. As far as I can remember, we did not delete any columns in tables for this Koha instance. This Koha instance was upgraded from 20.11 then 22.11 then 23.05. I wander why the paidfor field was not included in the upgrade? Anyway closing this bug, as it is an isolated case. Thank you very much!
Comment 5 Marcel de Rooy 2023-06-28 15:03:15 UTC
Here is a pointer:

$DBversion = '20.06.00.041';
if ( CheckVersion($DBversion) ) {

    if ( column_exists( 'items', 'paidfor' ) ) {
        my ($count) = $dbh->selectrow_array(
            q|
                SELECT COUNT(*)
                FROM items
                WHERE paidfor IS NOT NULL AND paidfor <> ""
            |
        );
        if ($count) {
            warn "Warning - Cannot remove column items.paidfor. At least one value exists";
        }
        else {
            $dbh->do(q|ALTER TABLE items DROP COLUMN paidfor|);
            $dbh->do(q|UPDATE marc_subfield_structure SET kohafield = '' WHERE kohafield = 'items.paidfor'|);
        }
    }

    if ( column_exists( 'deleteditems', 'paidfor' ) ) {
        my ($count) = $dbh->selectrow_array(
            q|
                SELECT COUNT(*)
                FROM deleteditems
                WHERE paidfor IS NOT NULL AND paidfor <> ""
            |
        );
        if ($count) {
            warn "Warning - Cannot remove column deleteditems.paidfor. At least one value exists";
        }
        else {
            $dbh->do(q|ALTER TABLE deleteditems DROP COLUMN paidfor|);
        }
    }

    NewVersion( $DBversion, 26268, "Remove items.paidfor field" );
}


The field paidfor should not be present anymore.
Comment 6 Katrin Fischer 2023-06-30 18:39:02 UTC
You might see bug 34063 which left the paidfor in one of the tables in some cases. If you delete it, things should work ok.