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@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 m...@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 m...@adminkuhn.ch <mailto:m...@adminkuhn.ch> > >> · W www.adminkuhn.ch <http://www.adminkuhn.ch> > >> > >> > >> > >> > -----Original Message----- > >> > From: Koha-devel <koha-devel-boun...@lists.koha-community.org > >> <mailto:koha-devel-boun...@lists.koha-community.org>> On Behalf Of > >> Michael Kuhn via Koha-devel > >> > Sent: Saturday, 13 April 2024 9:28 AM > >> > To: k...@lists.katipo.co.nz <mailto:k...@lists.katipo.co.nz>; > >> Koha-devel <koha-devel@lists.koha-community.org > >> <mailto: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 > >> <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 m...@adminkuhn.ch > >> <mailto:m...@adminkuhn.ch> · W www.adminkuhn.ch <http://www.adminkuhn.ch> > >> > > >> > _______________________________________________ > >> > Koha-devel mailing list > >> > Koha-devel@lists.koha-community.org > >> <mailto:Koha-devel@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@lists.koha-community.org > >> <mailto:Koha-devel@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@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/
