Hi Pablo,
Probably easier to have a conversation on Mattermost
https://chat.koha-community.org/ but I’ll reply to you here.
Yes, the warning is important. Don’t just blindly run whatever
audit_database.pl prints out. I wrote it to only be a guide. I never intended
people to actually run them *as is*. You’re just supposed to be inspired by
them.
I don’t know what you mean by “shouldn’t these queries be atomic” in this case.
Audit_database.pl is just showing a programmatically generated difference
between kohastructure.sql and the DB schema. It’s not a database repair tool.
Ugh, I wasn’t aware of “Run the following SQL to fix the database” from
about.pl. We should remove that. I’ve raised a report for that:
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40370
The database audit tool isn’t perfect. There are cases where the real
difference isn’t clear. If you think you’ve found a bug about a difference that
is being detected which shouldn’t be, of course report it as a bug.
Regarding /usr/share/koha/bin/maintenance/audit_database.pl, if you’d read the
code you would’ve seen that you need to provide a --filename parameter. The
database audit was originally created as a CLI-only tool, and it originally
defaulted to the kohastructure.sql found in the Koha git repo. It appears that
someone removed that. The feature was later adapted to the about.pl web UI.
Anyway, hopefully that answers your questions. I’d suggest following up on
Mattermost, since I’m more active there than the email lists.
David Cook
Senior Software Engineer
Prosentient Systems
Suite 7.03
6a Glen St
Milsons Point NSW 2061
Australia
Office: 02 9212 0899
From: Koha-devel On Behalf Of
Pablo Bianchi via Koha-devel
Sent: Tuesday, 15 July 2025 10:31 AM
To: Koha-devel
Subject: [Koha-devel] Issues with database audit
Some Koha instances (24.11.06), regularly updated successfully to stable since
20.05 present hundreds of ALTER TABLE/DROP TABLE on Database audit tab.
On one hand, we have the warning:
> Warning! These commands are only suggestions. They are not a replacement for
> the database update scripts run during installations and updates.
> Review the database, the atomic update files and the table definitions in
> kohastructure.sql before running any of the commands below:
It would be a huge task to review case by case. kohastructure.sql have thousand
of rows. Also, shouldn't these queries be atomic? Applying some of them
couldn't leave on an unstable state?
On the other hand, we have
Run the following SQL to fix the database:
-- Convert schema '' to '':;
BEGIN;
ALTER TABLE
...
COMMIT;
If I just run this .sql I usually came across some errors:
1. Using reserved words such as `schema` (biblio_metadata) and `database`
(`erm_usage_databases`) as column names without escaping them leads to syntax
errors, specifically error 1064.
2. `ALTER TABLE erm_eholdings_packages DROP FOREIGN KEY erm_packages_ibfk_1`,
but FK is `erm_eholdings_packages_ibfk_1`, not `erm_packages_ibfk_1`.
But even being able to run .sql, after fixing those issues, database audit
keeps suggesting hundreds of ALTER TABLE, changes which are already being made.
It is not exactly the same list of queries. I diff between after/before. The
ones which were done and not asked again are:
- DROP FOREIGN KEYs
- DROP/ADD INDEX
- DROP TABLEs
I'm doing something wrong, or should I file a bug?
Furthermore, `bin/maintenance/audit_database.pl <http://audit_database.pl> `
don't find `kohastructure.sql`. Looking at the code, it seems is not meant to
run on terminal, right?
Regards,
Pablo
___
Koha-devel mailing list
[email protected]
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/
