[Koha-devel] [Koha] Error 500 when searching in Koha 23.11.0
Michael Kuhn
mik at adminkuhn.ch
Mon Apr 15 14:47:27 CEST 2024
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/>
>>
>
More information about the Koha-devel
mailing list