Bug 32511 - Circulation statistics result Error 500 where select Collection, Shelving location, Home library or Holding library
Summary: Circulation statistics result Error 500 where select Collection, Shelving loc...
Status: CLOSED INVALID
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low major
Assignee: Hammat wele
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-12-21 01:43 UTC by Mohd Hafiz Yusoff
Modified: 2023-12-28 20:43 UTC (History)
7 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments
Select selving location (120.93 KB, image/png)
2022-12-21 01:43 UTC, Mohd Hafiz Yusoff
Details
Result show Error 500 (120.93 KB, image/png)
2022-12-21 01:44 UTC, Mohd Hafiz Yusoff
Details
Result show Error 500 (87.99 KB, image/png)
2022-12-21 01:46 UTC, Mohd Hafiz Yusoff
Details
Select Collection, Shelving location, Home library or Holding library (121.54 KB, image/png)
2022-12-21 02:06 UTC, Mohd Hafiz Yusoff
Details
Bug 32511: Circulation statistics result Error 500 where select Collection, Shelving location, Home library or Holding library (2.50 KB, patch)
2023-01-24 21:49 UTC, Hammat wele
Details | Diff | Splinter Review
Circulation statistics result after test (12.94 KB, image/png)
2023-01-25 15:43 UTC, Mohd Hafiz Yusoff
Details
Bug 32511: Circulation statistics result Error 500 where select Collection, Shelving location, Home library or Holding library (2.55 KB, patch)
2023-01-25 16:06 UTC, Biblibre Sandboxes
Details | Diff | Splinter Review
Bug 17649: Only one message per borrower and overdue level (6.29 KB, patch)
2023-01-26 14:51 UTC, Hammat wele
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Mohd Hafiz Yusoff 2022-12-21 01:43:22 UTC
Created attachment 144755 [details]
Select selving location

After upgrade from koha 22.05 to 22.11, Circulation statistics show error 500 result when select Shelving location
Comment 1 Mohd Hafiz Yusoff 2022-12-21 01:44:49 UTC
Created attachment 144756 [details]
Result show Error 500
Comment 2 Mohd Hafiz Yusoff 2022-12-21 01:46:43 UTC
Created attachment 144757 [details]
Result show Error 500

Update attachment
Comment 3 Mohd Hafiz Yusoff 2022-12-21 02:06:04 UTC
Created attachment 144758 [details]
Select Collection, Shelving location, Home library or Holding library

Update where select Collection, Shelving location, Home library or Holding library
Comment 4 Mohd Hafiz Yusoff 2022-12-27 16:55:25 UTC
This bug or not?
Comment 5 Owen Leonard 2023-01-10 20:24:25 UTC
I believe I'm seeing the same thing:

CGI::Compile::ROOT::kohadevbox_koha_reports_issues_stats_2epl::calculate(): DBI Exception: DBD::mysql::st execute failed: The used SELECT statements have a different number of columns at /kohadevbox/koha/reports/issues_stats.pl line 92
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77
Comment 6 Blou 2023-01-12 18:42:17 UTC
We have it in 22.05.06
Comment 7 Hammat wele 2023-01-24 21:49:05 UTC
Created attachment 145637 [details] [review]
Bug 32511: Circulation statistics result Error 500 where select Collection, Shelving location, Home library or Holding library

Circulation statistics show error 500 when the tables 'items' and 'deleteditems' do not have the same number of columns. The error 'The used SELECT statements have a different number of columns' is shown in apache log.

This patch resolvs the problem by selecting the common columns before execute the 'UNION' operator.

To Test

     1. In Staff interface, click on Reports module
     2. Click on Circulation
     3. Fill the form
        Period: Click on 'Row' and Select a date interval
     4. Click on 'Submit'
        --> Koha gives a 500 error message
     5. Apply the patch
     6. Repeat step 1,2,3,4
         --> There's no more error
Comment 8 Amaury GAU 2023-01-25 09:41:23 UTC
Hi, 

I did : 

 1. In Staff interface, click on Reports module
     2. Click on Circulation
     3. Fill the form
        Period: Click on 'Row' and Select a date interval
     4. Click on 'Submit'
        --> Koha gives a 500 error message
     5. Apply the patch
     6. Repeat step 1,2,3,4
         --> Same error occurred. 

Maybe there is something else needed ?
Comment 9 Mohd Hafiz Yusoff 2023-01-25 15:43:23 UTC
Created attachment 145661 [details]
Circulation statistics result after test

(In reply to Hammat wele from comment #7)
> Created attachment 145637 [details] [review] [review]
> Bug 32511: Circulation statistics result Error 500 where select Collection,
> Shelving location, Home library or Holding library
> 
> Circulation statistics show error 500 when the tables 'items' and
> 'deleteditems' do not have the same number of columns. The error 'The used
> SELECT statements have a different number of columns' is shown in apache log.
> 
> This patch resolvs the problem by selecting the common columns before
> execute the 'UNION' operator.
> 
> To Test
> 
>      1. In Staff interface, click on Reports module
>      2. Click on Circulation
>      3. Fill the form
>         Period: Click on 'Row' and Select a date interval
>      4. Click on 'Submit'
>         --> Koha gives a 500 error message
>      5. Apply the patch
>      6. Repeat step 1,2,3,4
>          --> There's no more error

Patch tested by me with my database on Koha 22.11.01. All work!

Signed-off-by: Mohd Hafiz Yusoff <mhby87@gmail.com>
Comment 10 Mohd Hafiz Yusoff 2023-01-25 15:45:20 UTC
Patch tested by me with my database on Koha 22.11.01. All work!

Signed-off-by: Mohd Hafiz Yusoff <mhby87@gmail.com>
Comment 11 Biblibre Sandboxes 2023-01-25 16:06:51 UTC
Created attachment 145662 [details] [review]
Bug 32511: Circulation statistics result Error 500 where select Collection, Shelving location, Home library or Holding library

Circulation statistics show error 500 when the tables 'items' and 'deleteditems' do not have the same number of columns. The error 'The used SELECT statements have a different number of columns' is shown in apache log.

This patch resolvs the problem by selecting the common columns before execute the 'UNION' operator.

To Test

     1. In Staff interface, click on Reports module
     2. Click on Circulation
     3. Fill the form
        Period: Click on 'Row' and Select a date interval
     4. Click on 'Submit'
        --> Koha gives a 500 error message
     5. Apply the patch
     6. Repeat step 1,2,3,4
         --> There's no more error

Signed-off-by: Mohd Hafiz Yusoff <mhby87@gmail.com>
Comment 12 Hammat wele 2023-01-26 14:51:49 UTC Comment hidden (obsolete)
Comment 13 Fridolin Somers 2023-02-06 21:18:29 UTC
> Circulation statistics show error 500 when the tables 'items' and 'deleteditems' do not have the same number of columns.

How can this be ?

On item deletion we move datas from items to deleteditems via Koha::Items move_to_deleted() :
https://git.koha-community.org/Koha-community/Koha/src/commit/1b6e9c7bd1c6e50ab610c6d15fe2e46af2f666ec/Koha/Item.pm#L332
Comment 14 Fridolin Somers 2023-02-06 21:26:52 UTC
Maybe linked to Bug 30392 ?

But I dont se how it can go wrong.
Comment 15 Fridolin Somers 2023-02-06 21:34:28 UTC
See also Bug 21982
Comment 16 Fridolin Somers 2023-02-06 21:35:32 UTC
On current master, I have a different error message :

CGI::Compile::ROOT::kohadevbox_koha_reports_issues_stats_2epl::calculate(): DBI Exception: DBD::mysql::st execute failed: 'koha_kohadev.statistics.datetime' isn't in GROUP BY at /kohadevbox/koha/reports/issues_stats.pl line 92
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77
Comment 17 Jonathan Druart 2023-02-08 07:38:55 UTC
This patch does not seem to be the correct fix. The columns in items and deleteditems are supposed to be the same, if you have a mismatch then you must fix the DB structure.

Did you compare both table structures?
Comment 18 Mohd Hafiz Yusoff 2023-02-08 07:48:23 UTC
(In reply to Jonathan Druart from comment #17)
> This patch does not seem to be the correct fix. The columns in items and
> deleteditems are supposed to be the same, if you have a mismatch then you
> must fix the DB structure.
> 
> Did you compare both table structures?

Patch working at me, but this discussion Failed QA.

How to fix DB structures or compare table structures. Tq
Comment 19 Hammat wele 2023-02-08 21:49:47 UTC
(In reply to Fridolin Somers from comment #14)
> Maybe linked to Bug 30392 ?
> 
> But I dont se how it can go wrong.

I don't know how, but in some case the colomn number of items and deleteditems are not the same. I have compared both tables and notice that the 'deleted_on' column is missing in the deleteditems table (in our case in 22.05.06).

You right! there's already a bugzilla for this.
Comment 20 Katrin Fischer 2023-02-08 21:53:03 UTC
(In reply to Hammat wele from comment #19)
> (In reply to Fridolin Somers from comment #14)
> > Maybe linked to Bug 30392 ?
> > 
> > But I dont se how it can go wrong.
> 
> I don't know how, but in some case the colomn number of items and
> deleteditems are not the same. I have compared both tables and notice that
> the 'deleted_on' column is missing in the deleteditems table (in our case in
> 22.05.06).
> 
> You right! there's already a bugzilla for this.

Do you know the number?

That one should especially be available in deleteditems and we need to figure out what happened there.

Either the update patch didn't work... or something got backported that had it without the change in the db?
Comment 21 Hammat wele 2023-02-08 23:20:41 UTC
> Do you know the number?
> 
> That one should especially be available in deleteditems and we need to
> figure out what happened there.
> 
> Either the update patch didn't work... or something got backported that had
> it without the change in the db?

The items table has 46 columns and the deleteditems has 45 columns. I think  there should be something backported without updating the Database as you said.
After applying the patch 30392 and executing the ./installer/data/mysql/updatedatabase.pl. both tables have the same number of columns (46) and there is no more error
Comment 22 Jonathan Druart 2023-02-09 08:30:12 UTC
(In reply to Hammat wele from comment #21)
> > Do you know the number?
> > 
> > That one should especially be available in deleteditems and we need to
> > figure out what happened there.
> > 
> > Either the update patch didn't work... or something got backported that had
> > it without the change in the db?
> 
> The items table has 46 columns and the deleteditems has 45 columns.

Which column was missing? Which version of Koha?
Comment 23 Hammat wele 2023-02-09 12:33:38 UTC Comment hidden (obsolete)
Comment 24 Hammat wele 2023-02-09 14:48:33 UTC
(In reply to Jonathan Druart from comment #22)
> (In reply to Hammat wele from comment #21)
> > > Do you know the number?
> > > 
> > > That one should especially be available in deleteditems and we need to
> > > figure out what happened there.
> > > 
> > > Either the update patch didn't work... or something got backported that had
> > > it without the change in the db?
> > 
> > The items table has 46 columns and the deleteditems has 45 columns.
> 
> Which column was missing? Which version of Koha?

In my local test database, I intentionally deleted the `deleted_on` column to be able to reproduce the problem. I got the database structure on which @blou detected the problem (in Comment 6 ) and the missing column in the deleteditems table is a column named `paidfor` which is not present in the items table.

I think we added the `paidfor` column in items table and didn't to add it in deleteditems table. that's where the problem came from.
Comment 25 Katrin Fischer 2023-02-09 14:54:30 UTC
> I think we added the `paidfor` column in items table and didn't to add it in
> deleteditems table. that's where the problem came from.

Hi Hammat, that's interesting. paidfor used to be a field in items, but was removed quite some time ago. See here: 
Bug 26268 - Remove items.paid for once and for all

So probably not a local customization, but something gone wrong in an update quite a while ago?

Should we then close this bug maybe?
Comment 26 Blou 2023-02-09 15:38:19 UTC
Hello Katrin,

Yes, please mark as INVALID.  I came to the same conclusion, probably an update that failed when we moved to 21.05 (I see the change in 20.06 pile...)
Comment 27 Katrin Fischer 2023-02-13 20:16:16 UTC
(In reply to Blou from comment #26)
> Hello Katrin,
> 
> Yes, please mark as INVALID.  I came to the same conclusion, probably an
> update that failed when we moved to 21.05 (I see the change in 20.06 pile...)

Thx, Blou. Done.
Comment 28 Mohd Hafiz Yusoff 2023-02-15 14:36:15 UTC
(In reply to Katrin Fischer from comment #27)
> (In reply to Blou from comment #26)
> > Hello Katrin,
> > 
> > Yes, please mark as INVALID.  I came to the same conclusion, probably an
> > update that failed when we moved to 21.05 (I see the change in 20.06 pile...)
> 
> Thx, Blou. Done.

Remark as INVALID, any update can solve my problem?
Comment 29 Katrin Fischer 2023-02-15 14:40:32 UTC
(In reply to Mohd Hafiz Yusoff from comment #28)
> (In reply to Katrin Fischer from comment #27)
> > (In reply to Blou from comment #26)
> > > Hello Katrin,
> > > 
> > > Yes, please mark as INVALID.  I came to the same conclusion, probably an
> > > update that failed when we moved to 21.05 (I see the change in 20.06 pile...)
> > 
> > Thx, Blou. Done.
> 
> Remark as INVALID, any update can solve my problem?

You'll need to compare the tables items and deleteditems in your database as a first step. If you also have the paidfor column still and that is the only difference, check if anything is stored in that column. If not, delete it.
Comment 30 Katrin Fischer 2023-02-15 14:40:58 UTC
(In reply to Katrin Fischer from comment #29)
> (In reply to Mohd Hafiz Yusoff from comment #28)
> > (In reply to Katrin Fischer from comment #27)
> > > (In reply to Blou from comment #26)
> > > > Hello Katrin,
> > > > 
> > > > Yes, please mark as INVALID.  I came to the same conclusion, probably an
> > > > update that failed when we moved to 21.05 (I see the change in 20.06 pile...)
> > > 
> > > Thx, Blou. Done.
> > 
> > Remark as INVALID, any update can solve my problem?
> 
> You'll need to compare the tables items and deleteditems in your database as
> a first step. If you also have the paidfor column still and that is the only
> difference, check if anything is stored in that column. If not, delete it.

... and always do a backup before any such 'operations'.
Comment 31 Mohd Hafiz Yusoff 2023-02-15 16:38:32 UTC
(In reply to Katrin Fischer from comment #29)
> (In reply to Mohd Hafiz Yusoff from comment #28)
> > (In reply to Katrin Fischer from comment #27)
> > > (In reply to Blou from comment #26)
> > > > Hello Katrin,
> > > > 
> > > > Yes, please mark as INVALID.  I came to the same conclusion, probably an
> > > > update that failed when we moved to 21.05 (I see the change in 20.06 pile...)
> > > 
> > > Thx, Blou. Done.
> > 
> > Remark as INVALID, any update can solve my problem?
> 
> You'll need to compare the tables items and deleteditems in your database as
> a first step. If you also have the paidfor column still and that is the only
> difference, check if anything is stored in that column. If not, delete it.

Yes, in table items has a column paidfor. I delete column paidfor, and its work.

ALTER TABLE items DROP COLUMN paidfor;

Thank you all.