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://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-u... cheers david 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/ or https://dba.stackexchange.com/questions/152387/altering-a-column-null-to-not...
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@adminkuhn.ch · W www.adminkuhn.ch
-----Original Message----- From: Koha-devel <koha-devel-bounces@lists.koha-community.org> On Behalf Of Michael Kuhn via Koha-devel Sent: Saturday, 13 April 2024 9:28 AM To: koha@lists.katipo.co.nz; Koha-devel <koha-devel@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 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@adminkuhn.ch · W www.adminkuhn.ch
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/