[SOLVED] Error Backup failed Misuse of shell builtins : mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227) .

[MoralCode]

Originally posted this on discord but sharing it here for future reference/better visibility to search engines:

I appear to have misconfigured something when setting up or migrating my install.

I was using an unsupported docker container (the linuxserver one) but just switched to the official one. nothing has changed in the database between the two (i checked and there are no pending migrations. both versions were relatively recent).
I'm ultimately trying to create a backup.

When i try to do so via the UI, Im seeing a HUGE error pop up at the top that talks about needing RELOAD permissions in the DB:

Error Backup failed because The dump process failed with exitcode 2 : Misuse of shell builtins : mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227) . #0 /var/www/html/vendor/spatie/db-dumper/src/DbDumper.php(263)
[truncated for brevity]

I tried a DB command like GRANT ALL PRIVILEGES ON snipe TO 'snipe'; (snipe is my database name and also the name of the user account) while logged into the DB as root, but that doesn't seem to have done anything, even though i think RELOAD is part of ALL permissions (and i thought this account already had access because backups used to work).
I might just be inexperienced with setting up databases tho

[MoralCode]

should have googled the error first. The permission needs to be granted globally via a command like GRANT ALL PRIVILEGES ON *.* TO 'snipe'@'%';. I found this answer via https://stackoverflow.com/questions/9972651/flush-tables-access-denied

Edit: i also saw someone on discord solve this with the command GRANT RELOAD ON * .* TO 'your_user'@'localhost'; in case GRANT ALL PRIVILEGES is scary for folks who use the same DB server for many things

[karthik8223]

I tried but failed.

[karthik8223]

Yes mate, Im on root.

[MoralCode]

oh i think this is similar to what I tried with my GRANT ALL command in my initial post which specified a specific database/table. While this permission is indeed part of ALL PRIVILEGES, it seems like it only works globally, so in your case, if the administrator user is the user that your snipeit install uses, youll probably want the command to be:

GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'stilsnipeit';

Obviously use caution though if the database server is used for other databases though. I personally have a fresh mariadb docker container just for snipe. Feel free to reference the stackOverflow issue i linked for more context too

[karthik8223]

Finally able to solve it. My bad, i used the wrong username and host.

solved it by using

1. select User,HOST, Process_priv,Reload_priv from mysql.user;
2. found the correct user that I registered earlier
3. GRANT ALL ON * . * TO 'dbadmin'@'localhost';
4. Now backup is back to successful.

[karthik8223]

Thanks alot for the help mate!

[MoralCode]

no problem!