USER in the dbispconfig gets borked when migrating from Debian10 to 11

Hi,

I deployed a new instance of Debian 11 and installed ISPconfig using autoinstaller with the command:

Code:

wget -O - https://get.ispconfig.org | sh -s -- --use-nginx --no-dns --no-mail --no-roundcube --no-firewall --no-pma --no-mailman --no-quota --no-ntp --use-php=5.6,7.4,8.1 --use-ftp-ports=40110-40210 --unattended-upgrades

All went well as follows:

1. Dumped the dbispconfig
2. Copied necessary files and folders (.acme.sh, nginx/sites-available/sites-enabled, uids and gids)
3. Manually fixed php-fpm related issues (form php7.3-fpm to php7.4-fpm) in the configuration files (https://www.howtoforge.com/communit...es-502-error-after-upgrade.88330/#post-431640).
4. Resynchronizeded all services, databases and users in the Tools > Resync menu which went well, too.

So far so good! The ISPConfig3 panel was accessible, but the db USER in the dbispconfig (from Debian 10) appeared to have borked in Debian 11 as of below:

Mysql root user can access the database from the command line as well as the newly created database users (nuser), too:

Code:

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2546
Server version: 10.5.12-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye

~# mysql -u c2nuser -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8119
Server version: 10.5.12-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye

Whereas all old db users (created in the Debian10 server) fail to connect to relevant databases with strings of message that reads. They were all working in the old Debian 10 server without a glitch (except php7.3 as default):

Code:

# mysql -u c2olddbuser -p
Enter password:
ERROR 1045 (28000): Access denied for user 'c2olddbuser'@'localhost' (using password: YES)

Mysql server is running alright and the relevant root credentials in /usr/local/ispconfig/server/lib/mysql_clientdb.conf as well as in the separate installation matches.

Code:

# service mysql status
● mariadb.service - MariaDB 10.5.12 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mysql.service.d
             └─limits.conf
     Active: active (running) since Fri 2022-02-18 08:02:27 UTC; 52min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 2793 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 2794 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 2795 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-env>
    Process: 2851 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 2852 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 2839 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 13 (limit: 4643)
     Memory: 128.0M
        CPU: 10.703s
     CGroup: /system.slice/mariadb.service
             └─2839 /usr/sbin/mariadbd

Feb 18 08:54:14 server3 mariadbd[2839]: 2022-02-18  8:54:14 17157 [Warning] Access denied for user 'c2olddbuser1'@'localhost' (using password: YES)
Feb 18 08:54:20 server3 mariadbd[2839]: 2022-02-18  8:54:20 17162 [Warning] Access denied for user 'c2olddbuser1'@'localhost' (using password: YES)
Feb 18 08:54:27 server3 mariadbd[2839]: 2022-02-18  8:54:27 17169 [Warning] Access denied for user 'c2olddbuser1'@'localhost' (using password: YES)
Feb 18 08:54:28 server3 mariadbd[2839]: 2022-02-18  8:54:28 17170 [Warning] Access denied for user 'c2olddbuser2'@'localhost'
Feb 18 08:54:33 server3 mariadbd[2839]: 2022-02-18  8:54:33 17173 [Warning] Access denied for user 'c2olddbuser1'@'localhost' (using password: YES)
Feb 18 08:54:48 server3 mariadbd[2839]: 2022-02-18  8:54:48 17184 [Warning] Access denied for user 'c2olddbuser1'@'localhost' (using password: YES)
Feb 18 08:54:54 server3 mariadbd[2839]: 2022-02-18  8:54:54 17189 [Warning] Access denied for user 'c2olddbuser1'@'localhost' (using password: YES)
Feb 18 08:55:00 server3 mariadbd[2839]: 2022-02-18  8:55:00 17194 [Warning] Access denied for user 'c2olddbuser1'@'localhost' (using password: YES)
Feb 18 08:55:02 server3 mariadbd[2839]: 2022-02-18  8:55:02 17201 [Warning] Aborted connection 17201 to db: 'unconnected' user: 'unauthenticated' host: 'localhost' (This co>
Feb 18 08:55:05 server3 mariadbd[2839]: 2022-02-18  8:55:05 17204 [Warning] Access denied for user 'c2olddbuser1'@'localhost' (using password: YES)

On other occasions it outputs errors that reads:

Code:

Feb 18 09:32:59 server3 mariadbd[6688]: 2022-02-18  9:32:59 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Feb 18 09:32:59 server3 /etc/mysql/debian-start[6704]: Looking for 'mysql' as: /usr/bin/mysql
Feb 18 09:32:59 server3 /etc/mysql/debian-start[6704]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Feb 18 09:32:59 server3 /etc/mysql/debian-start[6704]: Version check failed. Got the following error when calling the 'mysql' command line client
Feb 18 09:32:59 server3 /etc/mysql/debian-start[6704]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Feb 18 09:32:59 server3 /etc/mysql/debian-start[6704]: FATAL ERROR: Upgrade failed
Feb 18 09:32:59 server3 /etc/mysql/debian-start[6714]: Checking for insecure root accounts.
Feb 18 09:32:59 server3 mariadbd[6688]: 2022-02-18  9:32:59 5 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Feb 18 09:32:59 server3 debian-start[6717]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Feb 18 09:32:59 server3 mariadbd[6688]: 2022-02-18  9:32:59 0 [Note] InnoDB: Buffer pool(s) load completed at 220218  9:32:59 

In reference to I reset the mysql root password several times and reflected them in the relevant configuration files, too but the issue persists!

The ISPConfig log keeps on reporting:

Code:

Failed to make backup of database cXolddbY, because mysqldump failed

I guess there is a significant changes in db USER table between Mariadb v10.3 (default in Debian 10) and v10.5 (default in Debian 11). Somebody has suggested to rename the old users instead (https://stackoverflow.com/questions...l-user-references-invalid-tables-or-columns-o with pointer to https://mariadb.com/kb/en/rename-user/ ), yet I prefer to know the ISPConfig way. ;-)

UPDATE: Renaming an old db user helped! But it could be too tedious to rename each db user name. Any better way?

Have been trying to overcome the issue by searching online without any pointers. Appreciate any input to overcome the situation!

Have a nice weekend and cheers,

 

Understood, I have specific case and have to do that manually. I do not find too much of trouble to do things in command line, yet the only glitch is the incompatibilities between the dbuser which does not seem to reflect in the new version of MariaDB/MySQL.

The situation is like this. The remote production server that is hosting ISPConfig went out of space without possibility to upgrade. So it has to be relocated in a different remote server, running Debian11, in different geographical region, fyi.

Above all, the reply deviated away from OT.

@Th0m Let me state again that my issue is with dbuser which does NOT work when imported after dumping dbispcongfig from the old version of MariaDB whereas creating a new dbuser WORKS. It should be considered a kind of a bug, imho.

Cheers,

 

@till Thanks.

Is there a specific config files (mysql) that needs to be changed so that MySQL be able to deal with old-style passwords as hashed passwords? I am exactly trying to locate the specific configuration, fyi. Appreciate that.

 

Did you copy the "mysql" database?