Ubuntu 20.04 LTS: Update to latest ISPConfig spams GRANT errors, using mysql root

Discussion in 'Installation/Configuration' started by dfumagalli, Jan 7, 2023.

  1. dfumagalli

    dfumagalli Member

    Hello,
    Configuration: Multiserver setup with [Web server + ISPConfig] / DB server / Mail server. No DNS servers on this setup this time :p.
    The web server, the master machine, is an Ubuntu 20.04 LTS machine, running ISPConfig Version: 3.2.7p1.
    I want to upgrade to the latest ISPConfig before upgrading the machine to Ubuntu 22.04 LTS.

    This is just one of several multiserver setups I have created, by using various PDF manuals I bought here over the years.
    I usually (including 2 days ago) don't have major issues upgrading ISPConfig.
    However, this time I get a lot of GRANT errors when I choose to "Reconfigure Permissions in master database", as follows:

    Code:
    >> Update

Please choose the update method. For production systems select 'stable'.
WARNING: The update from GIT is only for development systems and may break your current setup. Do not use the GIT version on servers that host any live websites!
Note: On Multiserver systems, enable maintenance mode and update your master server first. Then update all slave servers, and disable maintenance mode when all servers are updated.

Select update method (stable,nightly,git-develop) [stable]: stable

Operating System: Ubuntu 20.04.5 LTS (Focal Fossa)

This application will update ISPConfig 3 on your server.

Shall the script create a ISPConfig backup in /var/backup/ now? (yes,no) [yes]:

Creating backup of "/usr/local/ispconfig" directory...
Creating backup of "/etc" directory...
Creating backup of "/etc/letsencrypt" directory...
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Checking ISPConfig database .. mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
OK
mysql: [Warning] Using a password on the command line interface can be insecure.
Starting incremental database update.
Loading SQL patch file: /tmp/update_runner.sh.t5vwvnepSK/install/sql/incremental/upd_dev_collection.sql
Reconfigure Permissions in master database? (yes,no) [no]: yes

WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`dnssec_initialized`, `dnssec_info`, `dnssec_last_signed`, `rendered_zone`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`status`, `error`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`updated`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE (`ssl`, `ssl_letsencrypt`, `ssl_request`, `ssl_cert`, `ssl_action`, `ssl_key`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE (`action_state`, `response`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT , DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`dnssec_initialized`, `dnssec_info`, `dnssec_last_signed`, `rendered_zone`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`status`, `error`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`updated`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE (`ssl`, `ssl_letsencrypt`, `ssl_request`, `ssl_cert`, `ssl_action`, `ssl_key`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE (`action_state`, `response`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT , DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`dnssec_initialized`, `dnssec_info`, `dnssec_last_signed`, `rendered_zone`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`status`, `error`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`updated`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE (`ssl`, `ssl_letsencrypt`, `ssl_request`, `ssl_cert`, `ssl_action`, `ssl_key`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE (`action_state`, `response`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT , DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`dnssec_initialized`, `dnssec_info`, `dnssec_last_signed`, `rendered_zone`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`status`, `error`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`updated`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE (`ssl`, `ssl_letsencrypt`, `ssl_request`, `ssl_cert`, `ssl_action`, `ssl_key`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE (`action_state`, `response`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT , DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, DELETE ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, UPDATE(`dnssec_initialized`, `dnssec_info`, `dnssec_last_signed`, `rendered_zone`) ON ?? TO ?@?
 Error:
WARNING: Unable to set rights of user in master database: dbispconfig
 Query: GRANT SELECT, INSERT, UPDATE ON ?? TO ?@?
 Error:
Service 'mail_server' has been detected (currently disabled) do you want to enable and configure it?  (yes,no) [no]:
    I've already checked /usr/local/ispconfig/server/lib/mysql_clientdb.conf and it holds the local machine MySQL root user. I've upgraded ISPConfig on this server in the past as well, so it should really "be it".
    I have checked the other users by using PHPMyAdmin. They look as follows:

    2023-01-07_001.png
    The 10.133 IPs and the "ams3" subdomain are the private network used by the servers to talk to each other.
    The 10.18 IPs are used by a VPN.
    Both of the above have never been an issue in the past, this is like my 10th multiserver setup I have created like this.

    When I install a new multiserver, I add the grants "by hand" as per the PDF manual instructions, plus I add the same grants for the other IP ranges, as the snippet below (only some private network users + one public user shown for brevity):

    Code:
    CREATE USER 'root'@'lx-srv-db-ams3-01.ams3.foXXXXXXXXXX.eu' IDENTIFIED BY 'Same-Password-Here';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'lx-srv-db-ams3-01.ams3.foXXXXXXXXXX.eu';
GRANT GRANT OPTION ON  *.* TO 'root'@'lx-srv-db-ams3-01.ams3.foXXXXXXXXXX.eu';
CREATE USER 'root'@'10.133.5.156' IDENTIFIED BY 'Same-Password-Here';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.133.5.156';
GRANT GRANT OPTION ON  *.* TO 'root'@'10.133.5.156';
CREATE USER 'root'@'lx-srv-db-ams3-01.foXXXXXXXXXX.eu' IDENTIFIED BY 'Same-Password-Here';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'lx-srv-db-ams3-01.foXXXXXXXXXX.eu';
GRANT GRANT OPTION ON  *.* TO 'root'@'lx-srv-db-ams3-01.foXXXXXXXXXX.eu';
CREATE USER 'root'@'167.NN.NN.130' IDENTIFIED BY 'Same-Password-Here';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'167.NN.NN.130';
GRANT GRANT OPTION ON  *.* TO 'root'@'167.NN.NN.130';
    Any suggestion or a way to debug this? I enabled "Debug Mode" in the ISPConfig web interface but no error nor warning appear.
     
    dfumagalli, Jan 7, 2023
    #1
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    The error means that the MySQL root user that you used to connect from the slave server to the master server is not allowed to run grant commands on the master server. To debug this, you can try o connect from slave to master MySQL server using MySQL command like this:

    mysql -h master.server.tld -u root -p

    And then try to run any kind of grant command to see if you get an error.

    You can find the most recent multiserver guide here btw. The commands to create the root user on master have changed over time due to requirements of newer MariaDB versions:

    https://www.howtoforge.com/tutorial/ispconfig-multiserver-setup-debian-ubuntu/
     
    till, Jan 7, 2023
    #2
  3. dfumagalli

    dfumagalli Member

    Hello Till,
    I am confused. How am I connecting from a slave server if I am running the above on the master server? The master server happens to be the web server and holds the ISPConfig control panel as well.

    I am running the upgrade from the master server first and I am reconfiguring the permissions on master server's database as per instructions.

    Am I getting something wrong here?
     
    dfumagalli, Jan 7, 2023
    #3
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    The update option:

    Reconfigure Permissions in master database? (yes,no) [no]: yes

    It is typically chosen on one of the slaves as it's about the connection of the slave to the master via ispcsrv* users. So you can choose No there on the master.
     
    till, Jan 7, 2023
    #4
    dfumagalli likes this.
  5. dfumagalli

    dfumagalli Member

    Thank you, I'll do that.
    I am still baffled at the fact the MySQL localhost root user (that lets me add grants "by hand") cannot grant on its own server.
    It's also suspicious how the error is spitting out "?" instead of the actual parameters.
    Anyway, thank you again!
     
    dfumagalli, Jan 7, 2023
    #5
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    That's perfectly ok and expected in this case as that's not the query that is run, the ? are place holders.
     
    till, Jan 7, 2023
    #6

Share This Page