[Koha-devel] [Koha] Error 500 when searching in Koha 23.11.0

David Schmidt mail at davidschmidt.at
Mon Apr 15 15:31:37 CEST 2024 

hmm

im surprised the column/table names are not quoted.

the SQL seems to be the output of https://github.com/Koha-Community/Koha/blob/master/misc/maintenance/audit_database.pl#L24

and the default for using quote_identifiers is true. (https://metacpan.org/pod/SQL::Translator#quote_identifiers)

but i just ran the script myself and identifiers are not quoted. even after explicitely setting `sqlt_args => { quote_identifiers => 1 }`

the hardcoded relative filename https://github.com/Koha-Community/Koha/blob/master/misc/maintenance/audit_database.pl#L10 is not very robust either but you can pass it via commandline.

hks3-koha at koha-hks3:~$ perl /usr/share/koha/bin/maintenance/audit_database.pl --filename=/usr/share/koha/intranet/cgi-bin/installer/data/mysql/kohastructure.sql

cheers
david


On Mon, 15 Apr 2024, at 2:47 PM, Michael Kuhn wrote:
> Hi David
> 
> You wrote:
> 
> > could it be that "schema" is a reserved word and it has to be quoted?
> >
> > looks like it -> https://mariadb.com/kb/en/reserved-words/
> > <https://mariadb.com/kb/en/reserved-words/>
> >
> > 
> https://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table 
> <https://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table>
> 
> Yes, this seems to be the case since there is no more error showing when 
> running the following commands:
> 
> ALTER TABLE biblio_metadata CHANGE COLUMN `schema` `schema` varchar(16) 
> NOT  NULL;
> Query OK, 0 rows affected (48,334 sec)
> Records: 0  Duplicates: 0  Warnings: 0
> 
> ALTER TABLE deletedbiblio_metadata CHANGE COLUMN `schema` `schema` 
> varchar(16) NOT NULL;
> Query OK, 0 rows affected (5,583 sec)
> Records: 0  Duplicates: 0  Warnings: 0
> 
> The current columns "schema" for both tables no look like:
> 
> | Field        | Type        | Null | Key | Default             | Extra
> +--------------+-------------+------+-----+---------------------+------
> ...
> | schema       | varchar(16) | NO   |     | NULL                |
> 
> And the script "audit_database.pl" doesn't show no errors anymore.
> 
> @David David Cook: Probably your script "audit_database.pl" should 
> consider this and quote all shown table names and column names.
> 
> Many thanks & best wishes: Michael
> -- 
> Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis
> Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz
> T 0041 (0)61 261 55 61 · E mik at adminkuhn.ch · W www.adminkuhn.ch
> 
> 
> 
> > On Mon, 15 Apr 2024, at 9:40 AM, Michael Kuhn via Koha-devel wrote:
> >> Hi David
> >>
> >> You wrote:
> >>
> >> > I don't think your database was properly upgraded, and doing these
> >> > manual corrections isn't really going to be enough unless you go line
> >> > by line through the upgrade scripts and figure out all the things that
> >> > were missed.
> >> >
> >> > If you're able to, I would go back and try the upgrade again. If you
> >> > can't... then I would carefully look through every database revision.
> >>
> >> I already did try upgrading the Koha 21.11 database again, several
> >> times. With or without changes in the dump. It lead to nothing so
> >> eventually I came up with the "solution" I described because the library
> >> needs the database to work.
> >>
> >> > You might also want to consider looking at
> >> > ./misc/maintenance/audit_database.pl as that could save you time.
> >> > (Just don't blindly run the suggestions made by the script. They're
> >> > just hints.)
> >>
> >> Thanks for the hint! Unfortunately I didn't find no help page or
> >> documentation for this script... However, I ran the script and there
> >> were some suggestions that I applied. (As far as I see this script is
> >> only available for the database strucjture of Koha 23.11 but not for the
> >> original 21.11)
> >>
> >> But for two tables the suggested SQL commands won't work:
> >>
> >> ALTER TABLE biblio_metadata CHANGE COLUMN schema schema varchar(16) NOT
> >> NULL;
> >>
> >> ALTER TABLE deletedbiblio_metadata CHANGE COLUMN schema schema
> >> varchar(16) NOT NULL;
> >>
> >> When trying to apply these commands both give this output:
> >>
> >> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> >> manual that corresponds to your MariaDB server version for the right
> >> syntax to use near 'schema schema varchar(16) NOT NULL' at line 1
> >>
> >> Do you happen to know what is wrong with these commands? I checked the
> >> syntax ans it seems to be OK according to
> >> https://mariadb.com/kb/en/alter-table/ 
> >> <https://mariadb.com/kb/en/alter-table/> or
> >> https://dba.stackexchange.com/questions/152387/altering-a-column-null-to-not-null <https://dba.stackexchange.com/questions/152387/altering-a-column-null-to-not-null>
> >>
> >> The current columns "schema" for both tables looks as follows:
> >>
> >> | Field        | Type        | Null | Key | Default             | Extra
> >>
> >> +--------------+-------------+------+-----+---------------------+------
> >> ...
> >> | schema       | varchar(16) | YES  |     | NULL                |
> >>
> >>
> >>
> >> Best wishes: Michael
> >> -- 
> >> Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis
> >> Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz
> >> T 0041 (0)61 261 55 61 · E mik at adminkuhn.ch <mailto:mik at adminkuhn.ch> 
> >> · W www.adminkuhn.ch <http://www.adminkuhn.ch>
> >>
> >>
> >>
> >> > -----Original Message-----
> >> > From: Koha-devel <koha-devel-bounces at lists.koha-community.org 
> >> <mailto:koha-devel-bounces at lists.koha-community.org>> On Behalf Of 
> >> Michael Kuhn via Koha-devel
> >> > Sent: Saturday, 13 April 2024 9:28 AM
> >> > To: koha at lists.katipo.co.nz <mailto:koha at lists.katipo.co.nz>; 
> >> Koha-devel <koha-devel at lists.koha-community.org 
> >> <mailto:koha-devel at lists.koha-community.org>>
> >> > Subject: Re: [Koha-devel] [Koha] Error 500 when searching in Koha 
> >> 23.11.0
> >> >
> >> > Hi
> >> >
> >> > Just for the record: I updated from Koha 21.11.10 to 23.11.04.
> >> >
> >> > When searching the catalogue I got a result list, but when clicking 
> >> a single hit I got an error 500. In file "plack-error.log" I found this:
> >> >
> >> > DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: 
> >> DBD::mysql::st execute failed: Unknown column 'issue.renewals_count' 
> >> in 'field list' at /usr/share/koha/lib/Koha/Objects.pm line 317
> >> >
> >> > In Koha 21.11 this column was called "issues.renewals" and in Koha 
> >> 23.11 it should be called "issues.renewals_count" - but for whatever 
> >> reason the upgrade did not rename it properly. I have done that myself:
> >> >
> >> > ALTER TABLE issues RENAME COLUMN renewals TO renewals_count;
> >> >
> >> > Now single hits show up.
> >> >
> >> > PS1: The error message says the column is "issue.renewals_count" but 
> >> actually it is "issues.renewals_count".
> >> >
> >> > PS2: https://schema.koha-community.org/23_11/tables/issues.html 
> >> <https://schema.koha-community.org/23_11/tables/issues.html> says the 
> >> type of this column is tinyint(3) while it actually is tinyint(4).
> >> >
> >> > Best wishes: Michael
> >> > --
> >> > Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. 
> >> Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · 
> >> Schweiz T 0041 (0)61 261 55 61 · E mik at adminkuhn.ch 
> >> <mailto:mik at adminkuhn.ch> · W www.adminkuhn.ch <http://www.adminkuhn.ch>
> >> >
> >> > _______________________________________________
> >> > Koha-devel mailing list
> >> > Koha-devel at lists.koha-community.org 
> >> <mailto:Koha-devel at lists.koha-community.org>
> >> > https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel 
> >> <https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel>
> >> > website : https://www.koha-community.org/ 
> >> <https://www.koha-community.org/> git : 
> >> https://git.koha-community.org/ <https://git.koha-community.org/> bugs 
> >> : https://bugs.koha-community.org/ <https://bugs.koha-community.org/>
> >> >
> >>
> >>
> >> _______________________________________________
> >> Koha-devel mailing list
> >> Koha-devel at lists.koha-community.org 
> >> <mailto:Koha-devel at lists.koha-community.org>
> >> https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel 
> >> <https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel>
> >> website : https://www.koha-community.org/ 
> >> <https://www.koha-community.org/>
> >> git : https://git.koha-community.org/ <https://git.koha-community.org/>
> >> bugs : https://bugs.koha-community.org/ <https://bugs.koha-community.org/>
> >>
> > 
> 
> 
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20240415/44ce81d8/attachment-0001.htm>

More information about the Koha-devel mailing list