im surprised the column/table names are not quoted.
but i just ran the script myself and identifiers are not quoted. even after explicitely setting `sqlt_args => { quote_identifiers => 1 }`
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
Hi David
You wrote:
> could it be that "schema" is a reserved word and it has to be quoted?
>
>
>
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
> 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
>>
>> 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
>>
>>
>>
>> > -----Original Message-----
>> Michael Kuhn via Koha-devel
>> > Sent: Saturday, 13 April 2024 9:28 AM
>> > 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".
>> >
>> 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 ·
>> >
>> > _______________________________________________
>> > Koha-devel mailing list
>> >
>>
>>
>> _______________________________________________
>> Koha-devel mailing list
>>
>