Errors from AutoMySQLBackup

capote · August 26, 2023, 7:47am

Hello,
I got last days such errors from MySQL Backup:

mysqldump: Couldn't execute 'show create table `wphh_uap_action_logs_view`': SHOW VIEW command denied to user 'backupuser'@'localhost' for table 'wphh_uap_action_logs_view' (1142)

The error is also generated when the command is triggered manually
/sbin/e-smith/runmysqlbackup-rh-mariadb103

The table wphh_uap_action_logs_view is related to my WordPress installation within a vhost and is empty.
It corresponds to the plugin Uncanny Automator.
That plugin works well.

What should I do? Is it a problem with AutoMySQLBackup or UA?

Within this Module, I can delete/reset tables.

Sincerely, Marko

pike · August 27, 2023, 12:39pm

IMVHO…neither

The error is pretty strightfoward.
'backupuser'@'localhost' cannot execute the command show create table wphh_uap_action_logs_view
(remove some of backticks to allow code formatting)

Maybe that user have not the rights to dump tables that seems… logs? IDK, I don’t use Wordpress. Maybe you can simply add that ACL on MySQL for that tables.

stephdl · August 29, 2023, 5:55am

Yes probably a command to allow backupuser to show these tables

stephdl · August 29, 2023, 6:03am

github.com

stephdl/nethserver-automysqlbackup/blob/ns7/root/etc/e-smith/events/actions/automysqlbackup-mysql.conf

#!/bin/bash
SMEDB=automysqlbackup
MYSQLUSER=backupuser

#create backupuser and give rights
MYSQLPASS=$(/sbin/e-smith/config getprop $SMEDB DbPassword)
mysql -e " GRANT EVENT,SELECT,LOCK TABLES ON *.* TO $MYSQLUSER@'localhost' "
mysql -u root -e "SET PASSWORD FOR $MYSQLUSER@localhost = PASSWORD( '$MYSQLPASS' ) "
mysqladmin flush-privileges

#protect the backup folder
chmod -R 700 /var/lib/nethserver/automysqlbackup

I have never allowed the show command. I think i never needed it. Does it is your only issue or others mysql database are with the same issue ?

capote · August 29, 2023, 12:16pm

Hi Stephane, that’s the only one system with this error,

Certainly clearly related to the WordPress module. Thanks for the tips.
I will try it.

Sincerely, Marko

stephdl · August 29, 2023, 8:07pm

github.com

stephdl/nethserver-automysqlbackup/blob/ns7/root/etc/e-smith/events/actions/automysqlbackup-rh-mariadb103.conf

#!/bin/bash
MYSQLUSER=backupuser
MYSQLPASS=$(/sbin/e-smith/config getprop automysqlbackup DbPassword)
MARIADB103=$(/sbin/e-smith/config getprop rh-mariadb103-mariadb status)

if [[ $MARIADB103 == 'enabled' ]]; then
    #create backupuser and give rights
    mysql103 -e "CREATE USER $MYSQLUSER@localhost IDENTIFIED BY '$MYSQLPASS'; GRANT EVENT,SELECT,LOCK TABLES ON *.* TO $MYSQLUSER@localhost; FLUSH PRIVILEGES;" > /dev/null 2>&1 &


    #protect the backup folder
    chmod -R 700 /var/lib/nethserver/automysqlbackup-rh-mariadb103/ > /dev/null 2>&1 &
fi

capote · August 29, 2023, 8:36pm

I granted backupuser to the WP-DB

ariaDB [(none)]> SHOW GRANTS FOR 'backupuser'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for backupuser@localhost                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, LOCK TABLES, EVENT ON *.* TO `backupuser`@`localhost` IDENTIFIED BY PASSWORD '*AB078BC771F653B32E9A58DA' |
| GRANT ALL PRIVILEGES ON `dbhegeringwp01`.* TO `backupuser`@`localhost`                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------+

But the error still remains, a little bit different.

ERROR 1045 (28000): Access denied for user 'backupuser'@'localhost' (using password: YES)

best regards, Marko

capote · October 19, 2023, 7:58am

I have to reopen the issue, because the error persists.
Daily I get mail with following information:

checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//daily" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//weekly" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//monthly" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//latest" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//tmp" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//fullschema" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//status" ... exists.
 
# Testing for installed programs
pigz ... found.
mysql ... found.
mysqldump ... found.
mail ... found.
 
# Parsing databases ... Note: Parsed config file /etc/automysqlbackup/rh-mariadb103.conf.
Error: The mysql server is empty, i.e. no databases found. Check if something is wrong. Exiting.

and

ERROR 1045 (28000): Access denied for user 'backupuser'@'localhost' (using password: YES)

As I wrote before, I granted the backupuser with credentials.

What can I do?

Sincerly, Marko

pike · October 19, 2023, 8:30am

Deja read? Did you not already had this kind of issue on ‘backupuser’@‘localhost’ ?

capote · October 19, 2023, 10:57am

Yes, but the solution didn’t help.

I could not take care of it the whole time and have now taken up the problem again.

capote · October 27, 2023, 8:24am

now I removed and reinstalled the application.

The error still remains when executing /sbin/e-smith/runmysqlbackup-rh-mariadb103

checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//daily" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//weekly" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//monthly" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//latest" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//tmp" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//fullschema" ... exists.
checking directory "/var/lib/nethserver/automysqlbackup-rh-mariadb103//status" ... exists.
 
# Testing for installed programs
pigz ... found.
mysql ... found.
mysqldump ... found.
mail ... found.
 
# Parsing databases ... Note: Parsed config file /etc/automysqlbackup/rh-mariadb103.conf.
Error: The mysql server is empty, i.e. no databases found. Check if something is wrong. Exiting.

and 2nd Mail

ERROR 1045 (28000): Access denied for user 'backupuser'@'localhost' (using password: YES

config show automysqlbackup:

config show automysqlbackup
automysqlbackup=configuration
    Backup_local_files=
    Backupdir=/var/lib/nethserver/automysqlbackup
    DbPassword=<not changed PW>
    Dbencrypt_password=01234567899876543210
    Encrypt=no
    Hour=3
    Mailcontent=log
    Mailto=<mymail>
    Minute=1
    Sizemail=8000

Any ideas are highly welcome.

Thank you all, Marko