Database scrambled. ispconfig@localhost failures.

Because of a php issue, I upgraded my old reliable server from 18.04 to 20.04. I didn't look to see what trouble people had had with it - boy, do I wish I had.
I got MOST of MySQL running, but was constantly being bombarded with "access denied for user 'ispconfig'@'localhost' (using password: YES)"
I can't tell what systems are having the issues, nor can I seem to get the passwords actually set properly. So I exported, uninstalled mysql, and restored mariaDB. I won't get into the nonsense there - maybe later.

The main 'mysql' database didn't want to dump correctly, but that only had the ispconfig user and the root user, and those were easy enough to recreate. Everything else is existing, but not authenticating. I did seem to get the config.inc.php files to work so I can get into the main ispconfig screen, but that doesn't help.

How do I recreate the salted password, or otherwise - the ispconfig upgrade file didn't seem to actually _care_ if it talked to the database or not, and certainly didn't help much with the services. (It would run even if it failed to connect)

(this is incoherent because I've now been up for 24 hours or so)

 

To add an edit, specifically I've modified both the config.inc.php in the interfaces/lib folder and in the server/lib folder, to have the cleartext of the updated ispconfig password. I also put the root password into the mysql_clientdb.conf file.

Running the updater throws 'php warning: mysqli_connect' - then the HY000/1045. Access denied for ispconfig@localhost, using password yes. (tmp/update_runner.sh - random - mysql.lib.php. ) Then mysqli_query() expects 1 to be myql1, bool given in /tmp/update_runner.sh.(random)mysql.lib.php

It's crazy - some stuff is working, other stuff is not, and I constantly am being hit with the access denied.

 

Both work. The only thing I can think of is that there are some files with the 'not changed' passwords - but I'm not sure why the updater is being exploded as well.

 

Possibly related, while postfix and dovecot are functioning, I'm getting temporary mta failure on relaying, from mta() during fwd-connect (all attempts (1) failed connecting to smtp:127.0.0.1:*)

 

I do have the correct passwords in there. in both server lib config.inc.php, and in interface/lib/confing.inc.php - So why would it throw the access denied warning as soon as I hit 'reconfigure services'. (It also then locks up)

 

I'm probably going to just create a new server this weekend, and try to migrate everything to it, but I need to get everything working today.
Additional symptom. I updated a database user, and it it's still holding in the job queue as not yet populated to all servers.

 

It looks like the only really nasty thing I'm working with (other than some sites being down, and I'll delete and recreate users for those if need be) is the postfix/amavis relay problem. Basically, incoming email is happy, and being dropped into place. outgoing email is stuck in queue, with the following error.

relay=127.0.0.1[127.0.0.1]:10026, delay=1219, delays=1213/1.7/4/1, dsn=4.5.0, status=deferred (host 127.0.0.1[127.0.0.1] said: 451 4.5.0 id=13619-03 - Temporary MTA failure on relaying, From MTA() during fwd-connect (All attempts (1) failed connecting to smtp:127.0.0.1:*): id=13619-03 (in reply to end of DATA command))

 

Okay, the error happens here. - As soon as you tell it no, don't make a backup (I have two already), whatever it does next blows up. I haven't dug into the script yet.

This application will update ISPConfig 3 on your server.

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

PHP Warning: mysqli_connect(): (HY000/1045): Access denied for user 'ispconfig'@'localhost' (using password: YES) in /tmp/update_runner.sh.GNhhdgO6dY/install/lib/mysql.lib.php on line 112
PHP Warning: mysqli_query() expects parameter 1 to be mysqli, bool given in /tmp/update_runner.sh.GNhhdgO6dY/install/lib/mysql.lib.php on line 113
Checking ISPConfig database .. OK
Starting incremental database update.
Loading SQL patch file: /tmp/update_runner.sh.GNhhdgO6dY/install/sql/incremental/upd_dev_collection.sql
Reconfigure Permissions in master database? (yes,no) [no]:

Service 'firewall_server' has been detected (currently disabled) do you want to enable and configure it? (yes,no) [no]:

 

Postfix errors might be related to this -
virtual_alias_domains: proxy:mysql:/etc/postfix/mysql-virtual_alias_domains.cf: table lookup problem

No idea what it means, right now.
Also, the update script apparently re-salted the password, stuck it in the configuration files, and broke what was working. (latest Ubuntu MariaDB, 10.3)

 

I'm reading what you're putting down. However that's not what I'm seeing. I changed both config.inc.php files, changed the master file for root@, then ran the updater. The updater then put in (what looks like) the _previous_ password in the ispconfig files and the program configuration files. It _looks_ like a salted or hashed (or both) version of the password I put in.

Then, when it restarted the services? They all broke, immediately.

 

I just checked my logs. I edited both files before running the updater. In fact, now that I think about it, that's the same behaviour I had six hours ago, when I was just trying to get MySQL to work, and various things were working or not. The root password (right now) is the same as the ispconfig user password, just to make things simple.

 

Hm.. The database users for the web sites are also breaking - same access denied.

 

It _looks_ as thought something fiddling with the postfix settings - and deleted my backup copy I made of that folder.

(!)connect to 127.0.0.1:* failed, attempt #1: Can't connect to socket 127.0.0.1:* using module IO::Socket::IP: Connection refused - that's amavis, trying to talk to Postfix to send out, I think.

Not sure what's up with the database users not working from Apache.

 

This seems to be throwing a table lookup problem. I'm not sure if it's hurting things or not. Right now, I have to figure out how to get these emails to leave, even if Amavis is bypassed. (So, I'm going to look for how to disable bypass for outgoing mail)

mysql-virtual_alias_domains.cf
user = ispconfig
password = nji9vfr4
dbname = dbispconfig
hosts = 127.0.0.1
query = SELECT SUBSTRING_INDEX(destination, '@', -1) FROM mail_forwarding
WHERE source = '@%s' AND type = 'aliasdomain' AND active = 'y' AND server_id = 1

 

Okay - two questions. 1) Any idea where to look to figure out why the database users I'm modifying in ispconfig are still saying access denied (but the email users work fine), and 2) where to figure out what's going on? I believe it's Amavis. Postfix is trying to send emails, but that goes through Amavis. Amavis is apparently refusing to take them, despite them being in-house. 4.5.1
For the database users, like the roundcube user, I'm not sure how to test that from command line

 

Bypassed Amavis in main.cf and master.cf, thumped the mail to put it into the main queue, and outgoing mail works. roundcube works again, and I'm trying to figure out if it's the mysql_native_password setting that's keeping ispconfing's database users from working properly. (or at all)