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

Discussion in 'Installation/Configuration' started by zenny, Feb 18, 2022.

  1. zenny

    zenny Member

    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! :D 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,
     
    Last edited: Feb 18, 2022
  2. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

  3. zenny

    zenny Member

    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,
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    There is no bug here, you just seem to have missed exporting MySQL user and db records from old system and import them in the new one and you might have to configure MySQL to be able to deal with old-style passwords as hashed passwords can not be re-hashed as there is no known cleartext password for that. Using the Migration Tool as @Th0m suggested it would have been easier indeed.
     
    zenny likes this.
  5. zenny

    zenny Member

    @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.
     

Share This Page