Bug 41353

Summary: koha-dump failing on mysqldump PROCESS privileges
Product: Koha Reporter: Djalel CHEFROUR <djalel.chefrour>
Component: Command-line UtilitiesAssignee: Saiful Amin <saiful>
Status: Signed Off --- QA Contact: Testopia <testopia>
Severity: major    
Priority: P3 CC: bibliothek, david, robin, saiful
Version: 25.05Keywords: no-sandbox
Hardware: All   
OS: Linux   
GIT URL: Initiative type: ---
Sponsorship status: --- Comma delimited list of Sponsors:
Crowdfunding goal: 0 Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Attachments: Patch koha-dump to fix mysqldump privs error
Bug 41353: koha-dump failing on mysqldump PROCESS privileges
Bug 41353: koha-dump failing on mysqldump PROCESS privileges

Description Djalel CHEFROUR 2025-12-02 12:33:47 UTC
Created attachment 190076 [details] [review]
Patch koha-dump to fix mysqldump privs error

Hi,

I've found this repeating error message(s) from cron:

/etc/cron.daily/koha-common:
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
tar: var/log/koha/lib/opac-access.log: file changed as we read it


There is already a report open about the mysql cli password warning. I am not addressing it here. I am not concerned by the tar warning neither.

The issue is with the: 
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

I traced it back to koha-dump which is configured to be run in cron.daily. This means these 'automatic' backups are not working on my system, and most likely on other debian or even linux based deployments.  Hopefully, I have my own backups for the whole MySQL server covering this corner. 

As there are other DBs on this MySQL server that hosting Koha too, I do not want to give the koha SQL user (created by koha installation scripts) PROCESS privileges for obvious security reasons. 

The other alternative I found to fix this issue, and therefore the koha-dump backup, is just to add --no-tablespaces to the mysqldump flags in the koha-dump script.


As Koha does not seem to use transportable tablespaces, nor any low-level .ibd file operations! Its backups are logical backups (SQL text). So I figure patching this for every one makes sense.

The one liner patch is attached to this bug report.

Best regards,
Comment 1 Saiful Amin 2025-12-08 15:15:08 UTC
(In reply to Djalel CHEFROUR from comment #0)
> The other alternative I found to fix this issue, and therefore the koha-dump
> backup, is just to add --no-tablespaces to the mysqldump flags in the
> koha-dump script.
> 

You're probably on the right track with that fix. Koha doesn't make use of custom tablespaces as per my knowledge.

Just note that changes shouldn't be made to the installed /usr/sbin/koha-dump script. All code modifications must go through the normal Koha development workflow so they can be reviewed and tested across different environments.

Relevant docs:
https://wiki.koha-community.org/wiki/Development_workflow
https://wiki.koha-community.org/wiki/Submitting_A_Patch
https://gitlab.com/koha-community/koha-testing-docker

The actual file to patch is:
debian/scripts/koha-dump

Please submit the patch with a test plan and change the status to 'Needs Signoff'.
Comment 2 Jan Kissig 2026-02-23 09:25:49 UTC
I have a question about this bug:

I have several debian 12 (bookworm) installations with koha-common installed and the instances created via 'sudo koha-create --create-db username'. The default backup via /etc/cron.daily/koha-common works out of the box and to my knowledge cron.* is still a standard tool to use and works withing debian/ubuntu environments.

I also checked the privileges of one of the created koha users and it does not show the PROCESS privilege, only USAGE is set.

On what type of Koha installation and server environment does this error occur?
Comment 3 Saiful Amin 2026-02-23 11:19:36 UTC
(In reply to Jan Kissig from comment #2)
> On what type of Koha installation and server environment does this error
> occur?

This is specific to MySQL 8.0 related. You are most likely using MariaDB.
Comment 4 Jan Kissig 2026-02-24 12:32:54 UTC
(In reply to Saiful Amin from comment #3)
> (In reply to Jan Kissig from comment #2)
> > On what type of Koha installation and server environment does this error
> > occur?
> 
> This is specific to MySQL 8.0 related. You are most likely using MariaDB.

Thank you for that explanation Saiful, and yes mariadb was running. Now I can confirm on ktd with mysql8.0 running that the error occurs:

kohadev-koha@kohadevbox:koha(main)$ sudo koha-run-backups --days 2 --output /var/spool/koha
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
kohadev-koha@kohadevbox:koha(main)$
Comment 5 Saiful Amin 2026-02-26 14:11:28 UTC
Created attachment 194002 [details] [review]
Bug 41353: koha-dump failing on mysqldump PROCESS privileges

Problem:
Starting from MySQL 5.7.31 and MySQL 8.0.20, mysqldump requires the
PROCESS privilege to access tablespace metadata by default. Without
this privilege, backup operations fail with permission errors.

Solution:
Add the --no-tablespaces flag to the dbflag variable. This prevents
mysqldump from attempting to access tablespace metadata, allowing
backups to complete successfully without requiring the PROCESS privilege.

Test Plan:
  1. Set up a MySQL instance (5.7.31+ or 8.0.20+) without PROCESS privilege for the Koha database user
  2. Run the koha-dump script for a Koha instance:
     sudo koha-dump kohadev
  3. Notice the backup fails with a PROCESS privilege error
  4. Apply the patch
  5. Copy debian/scripts/koha-dump to /usr/sbin/koha-dump
  6. Repeat step 2
  7. Notice the backup now completes successfully without permission errors
  8. Test restoring the dump to ensure it's valid:
     zcat /var/spool/koha/kohadev/kohadev-<date>.sql.gz | tail
  9. Sign-off
Comment 6 David Nind 2026-03-01 18:06:19 UTC
Happy to attempt to test.

However, could you provide the steps/SQL command to change the privileges?

I can start up KTD with MariaDb and MySQL, so no issues with that part.
Comment 7 Saiful Amin 2026-03-01 18:51:55 UTC
(In reply to David Nind from comment #6)
> However, could you provide the steps/SQL command to change the privileges?

There is no need to change the process privilege. The PROCESS privilege is disabled by default for all users (except root) in MySQL version 8.0.20+. I think `ktd pull` will bring MySQL 8.0.45, if I have to guess.

So no extra step needed apart from starting ktd with 'DB_IMAGE=mysql:8.0'
Comment 8 David Nind 2026-03-01 19:58:51 UTC
Created attachment 194249 [details] [review]
Bug 41353: koha-dump failing on mysqldump PROCESS privileges

Problem:
Starting from MySQL 5.7.31 and MySQL 8.0.20, mysqldump requires the
PROCESS privilege to access tablespace metadata by default. Without
this privilege, backup operations fail with permission errors.

Solution:
Add the --no-tablespaces flag to the dbflag variable. This prevents
mysqldump from attempting to access tablespace metadata, allowing
backups to complete successfully without requiring the PROCESS privilege.

Test Plan:
  1. Set up a MySQL instance (5.7.31+ or 8.0.20+) without PROCESS privilege for the Koha database user
  2. Run the koha-dump script for a Koha instance:
     sudo koha-dump kohadev
  3. Notice the backup fails with a PROCESS privilege error
  4. Apply the patch
  5. Copy debian/scripts/koha-dump to /usr/sbin/koha-dump
  6. Repeat step 2
  7. Notice the backup now completes successfully without permission errors
  8. Test restoring the dump to ensure it's valid:
     zcat /var/spool/koha/kohadev/kohadev-<date>.sql.gz | tail
  9. Sign-off

Signed-off-by: David Nind <david@davidnind.com>
Comment 9 David Nind 2026-03-01 20:01:29 UTC
Thanks Saiful!

Testing notes (using KTD):
1. For step 5: sudo cp debian/scripts/koha-dump /usr/sbin/koha-dump
2. After the patch, database dump works successfully without errors for MySQL and MariaDB

Database dump file listing before patch (MySQL):
-rw-r----- 1 root kohadev-koha 1064076 Mar  1 19:28 kohadev-2026-03-01.sql.gz
-rw-r----- 1 root kohadev-koha 3990254 Mar  1 19:28 kohadev-2026-03-01.tar.gz

Database dump file listing after patch (MySQL):
-rw-r----- 1 root kohadev-koha 1064077 Mar  1 19:49 kohadev-2026-03-01.sql.gz
-rw-r----- 1 root kohadev-koha 3991362 Mar  1 19:49 kohadev-2026-03-01.tar.gz