Troubleshooting Reports

This is the page that relates to troubleshooting reports to find data which will trigger bugs or cause unexpected behavior.

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

Return to the SQL Reports Library

Barcodes with leading spaces

• Developer: Barton Chittenden, ByWater Solutions
• Module: Circulation
• Purpose: Show items where barcodes have leading spaces -- these will give 'Barcode not found' message when scanned.
• Status: Complete
• Note:

SELECT
    biblionumber,
    title,
    CONCAT(
        '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?',
        'op=edititem&biblionumber=', 
        biblionumber, '&itemnumber=', itemnumber, 
        '#edititem', '\">', itemnumber, '</a>' 
    ) AS itemnumber,
    concat( "'", barcode, "'" ) AS barcode
FROM
    items
    INNER JOIN biblio USING (biblionumber)
WHERE
    barcode like ' %'

Call number sorting issues

• Developer: Barton Chittenden, ByWater Solutions
• Module: Cataloging
• Purpose: Show cn_sort, cn_source for the purpose of trouble-shooting shelf sort issues.
• Status: Complete
• Note:

select
    title,
    itemcallnumber,
    cn_sort,
    cn_source
from
    items
    inner join biblio using (biblionumber)
where
    itemcallnumber like <<Callnumber (use % for wildcard, e.g. B1%)>>
order by cn_sort;

Codes differ in case

• Developer: Barton Chittenden, ByWater Solutions
• Module: Circulation
• Purpose: Find codes in borrower_attributes and borrower_attribute_types which differ by case.
• Status: Complete
• Note: MySQL is case-insensitive by default, but in the case of codes like borrower_attributes.code or borrower_attribute_types.code, the data is used in a case sensitive way inside Koha. The statement COLLATE utf8_bin can be appended after a field name to force case sensitivity in MySQL:

select 
    ba.*,
    bat.code
from 
    borrower_attributes ba
    inner join borrower_attribute_types bat using (code)
where 
    ba.code COLLATE utf8_bin != bat.code COLLATE utf8_bin

items.onloan does not match issues.date_due

• Developer: Barton Chittenden, ByWater Solutions
• Module: Circulation
• Purpose: Find items and issues where items.onloan does not match issues.date_due
• Status: Complete
• Note: Koha stores the due date for an item in two locations: items.onloan and issues.date_due. These fields *should* match, but they have been known to get out of sync, which may cause an item to show as both avialable and checked out in search results.

SELECT 
    itemnumber,
    issuedate,
    date_due 
FROM issues join items using (itemnumber)
WHERE onloan is null

Sort borrower card numbers numerically

• Developer: Barton Chittenden, ByWater Solutions
• Module: Circulation
• Purpose: Find borrower card numbers which are out of sequence.
• Status: Complete
• Note: When populating borrower card number, Koha will choose the next card number in sequence, numerically. If a card number has been saved to the borrowers table which creates a gap in the borrower numbers, (e.g. 1000, 1001, 1002, 10031003), Koha will populate the next card number based on the highest numerical card number. This query can be used to trouble-shoot that situation.

SELECT cardnumber, 
       borrowernumber 
FROM borrowers 
ORDER BY CAST( cardnumber as SIGNED INTEGER)

Direct links to systemprefrences

• Developer: Barton Chittenden, ByWater Solutions
• Module: Administration
• Purpose: Trouble-shooting sysprefs
• Status: Complete
• Note: Sometimes it's nice to have a link to a specific system prefrence. This generates all of them:

Select
    CONCAT('<a href=\"/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=?variable=', variable, '\">', variable, '</a>' ) AS 'Syspref'
from systempreferences

Show last queued message in message_queue by letter code

• Developer: Barton Chittenden, ByWater Solutions
• Module: Notices
• Purpose: Lets you see if a particular letter_code stopped queueing -- could tell you if advanced notices or overdue notices is borked.
• Status: Complete

select
    max(date(time_queued)) as 'last sent',
    letter_code
from message_queue
group by letter_code
order by max(time_queued)

Search for saved report by partial report name

• Developer: Barton Chittenden, ByWater Solutions
• Module: Reports
• Purpose: Look up report number and name by partial report name
• Status: Complete

SELECT
    id,
    report_name
FROM
    saved_sql
WHERE
    report_name like CONCAT('%' , <<Part of report name>> , '%')

Search for saved report by part of query

• Developer: Barton Chittenden, ByWater Solutions
• Module: Reports
• Purpose: Look up report number and name by part of the sql
• Status: Complete

SELECT
    id,
    report_name
FROM
    saved_sql
WHERE
    savedsql like CONCAT('%' , <<Part of saved SQL>> , '%')