Transfer or restore virtual server errors on overwrite

ftrojahn commented 4 years ago

[ ] fails creating mariadb user, if there is a "-" oder "." in the name (other cases not tested), then stops. IMHO it works, if only letters in mysql user name
[ ] mysql feature enabled on old server/in backup, does not enable mysql on new server/restore
[ ] php_admin_value in apache2 conf not corrected, if mod_php is disabled -> no error notice! -> apache2 fails on next restart

Systems used:

old: Debian 8 jessie, all updates, virtualmin 6.08.gpl
new: Debian 10 buster, all updates, virtualmin 6.08.gpl and mariadb-server 10.3.17-0+deb10u1

History: did transfer server image from old to better, newer server (kvm with raid10 hdd -> kvm with raid10 ssd). On new system: made upgrades to stretch, then buster. As there are several weeks gone now due to other errors: had to transfer 5 of 40 vhosts/websites from old to new server, overwriting vhosts, due to changes. Btw: I really appreciate the transfer feature of full vhosts, one of the big reasons that I use Virtualmin.

Details:

for mysql, on transfer (from old system) or restore from backup (from same or old system) I get the following errors, if mysql feature is enabled on target:

Restore failed : SQL create user 'mysuperlongdo-ma'@'localhost' identified by '*xxxxxxxxxxxxxxxxxxxxxxxxxxxx' 
failed : Operation CREATE USER failed for 'mysuperlongdo-ma'@'localhost'

After that, user permissions were missing. Database permissions after that:

    mysuperlong\_domain     mysuperlongdo-ma    localhost   All

Deleting database permissions before transfer/restore does not make a difference.

full log

``` Transferring mysuperlong-domain.de to new.server.tld .. Backing up to destination system .. .. backup done Validating backup .. .. all domains transferred successfully Restoring backup on destination system .. .. restore failed : Checking for missing features .. .. all features in backup are supported Checking for errors in backup .. .. no errors found Starting restore.. Extracting backup archive files .. .. done Restoring backup for virtual server mysuperlong-domain.de .. Restoring virtual server password, quota and other details .. .. done Updating administration password and quotas .. .. done Restoring Cron jobs .. .. done Extracting TAR file of home directory .. .. done Setting ownership of home directory .. .. done Re-creating records in DNS domain .. .. done Restoring Apache virtual host configuration .. .. done Checking restored PHP execution mode .. .. mode FCGId OK for this system Updating home directory in PHP configuration .. .. done Restoring Apache log files .. .. done Restoring SSL Apache virtual host configuration and certificate .. .. done Restoring Logrotate configuration .. .. done Restoring allowed MySQL hosts .. .. done Deleting old MySQL databases .. .. done Re-loading MySQL database mysuperlong-domain .. Creating MySQL database mysuperlong-domain .. .. done .. done Error: SQL create user 'mysuperlong-do'@'localhost' identified by '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' failed : Operation CREATE USER failed for 'mysuperlong-do'@'localhost' Error ----- SQL create user 'mysuperlong-do'@'localhost' identified by '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' failed : Operation CREATE USER failed for 'mysuperlong-do'@'localhost' ----- .. transfer failed ```

Workaround for now: disable mysql feature on target (disable/enable was not enough). After transfer or restore, reenable mysql feature (user and permissions are created correctly), then restore db from (my own) database dump. This seemed easier to me instead of trying to create the mysql user (and perhaps db permissions) manually

while this workaround is fine in this case, question remains: if I transfer or restore a vhost with "mysql feature enabled", using "over-write existing domain", the mysql feature on the target system is not reenabled. Why is this?
feature request: like on restore, may be we could have some options to select, which features should be transfered to new system? This would make it much better, if e.g. only mails, database, files or whatever should go to the other system.

regarding the php_admin_value (and php_value) problem, as mentioned in #96 - this hits us here, too. I can only check using ssh or console:


:~# apache2ctl -t && apache2ctl graceful
AH00526: Syntax error on line 44 of /etc/apache2/sites-enabled/my-domain.de.conf:
Invalid command 'php_admin_value', perhaps misspelled or defined by a module not included in the server configuration
Action '-t' failed.
The Apache error log may have more information.

and remove these by hand. If transferring/restoring multiple domains, I use something like this:

perl -i -p -e 's/php_admin_value engine Off//g;' /etc/apache2/sites-available/.conf perl -i -p -e 's/^php_value.//g;' /etc/apache2/sites-available/*.conf


Sorry for the long description, but IMHO all this belongs together.

iliajie commented 3 years ago

Good to know. Keep us posted please. We want this to be sorted out.

Yorkki commented 3 years ago

@ftrojahn @Yorkki - do you still see this issue with the latest feature-mysql.pl file?

Unable to test it again, sorry, was in a hurry and moved the site the "old fashion way". - Yorkki

julienld commented 3 years ago

I had this issue with 6.12. The latest version of feature-mysql.pl works like a charm. Thank you @iliarostovtsev

iliajie commented 3 years ago

Good to know! Thanks for the feedback!

julienld commented 3 years ago

Maybe I spoke too soon. There is a problem when the domain already exists and I try to restore an incremental backup. I get this while re-creating mailboxes :

Error: SQL create user &#39;mysql.forum-perroquet&#39;@&#39;localhost&#39; identified  by password &#39;*REDACTEDHASH&#39; manqué : Operation CREATE USER failed for &#39;mysql.forum-perroquet&#39;@&#39;localhost&#39;
Erreur
-----
SQL create user &#39;mysql.forum-perroquet&#39;@&#39;localhost&#39; identified  by password &#39;*REDACTEDHASH&#39; manqué : Operation CREATE USER failed for &#39;mysql.forum-perroquet&#39;@&#39;localhost&#39;
-----

I had to : (1) do a full backup (2) delete the domain (3) delete manually the leftover databases and permissions (4) do a restore-domain with the full backup.

bold-gman commented 3 years ago

I am sorry for being absent, first I was out with a cold and then my day job demanded more attention.

I have the same problem as @julienld. I had removed every server, every database and every db user I could find, so I had a clean state. I restored from a full backup and everything worked.

But when I tried to restore the last incremental backup, I got this error:

Starting restore of 22 domains from local file /backup_sfo/2020-10-04-tgl ..

Extracting backup archive files ..
.. done

Restoring backup for virtual server adiunctus-grabgestaltung.de ..

    Restoring virtual server password, quota and other details ..
    .. done

    Updating administration password and quotas ..
    .. done

    Restoring Cron jobs ..
    .. done

    Extracting TAR file of home directory ..
    .. done

    Setting ownership of home directory ..
    .. done

    Restoring allowed MySQL hosts ..
    .. done

    Deleting old MySQL databases ..
    .. done

    Re-loading MySQL database adiunctus_grabgestaltung_de ..
        Creating MySQL database adiunctus_grabgestaltung_de ..
        .. done

    .. done

    Restore failed : SQL create user 'adiunctus-grabge'@'localhost' identified by password '*<hash goes here>' failed : Operation CREATE USER failed for 'adiunctus-grabge'@'localhost'

Now, as you can see, I have 22 domains I want to restore. The error only shows up when I restore main servers with the db feature (about 7), but the restore works when I select only sub-servers or aliases (15). At first I thought it was only related the default admin users, that are automatically created with every server, but I get the error with other users of the main-servers, too.

iliajie commented 3 years ago

What Virtualmin version does this happen on? Had a backup been created on Virtualmin 6.12? Does this happen when hashed passwords are turn on in configuration? Is there a way I could have a download link for a backup(s) which fail sent to me using my website contact form?

What happens, on the system which fails, if you try logging in to database server using console:

First:

mysql -u root

Second:

mysql -u root -p123

iliajie commented 3 years ago

FYI - There were more changes and improvements made to support password change for root user when it's unknow and when Webmin itself cannot connect to database server.

Tomorrow I will test restore with hashed passwords. I would like to point out, that apparently, it will not be possible to restore hashed password when target system has different hashing mechanism.

@jcameron I assume this is why we have to store plaintext MySQL user password even if upon wizard setup a user chosen to use hashed passwords? Isn't it what we're doing already on the latest Virtualmin 6.12+?

jcameron commented 3 years ago

Yes, we need to store the plaintext passwords for MySQL so that Virtualmin can login to MySQL as the domain user. This is the way we've been doing it forever, even when Unix passwords are only stored hashed.

iliajie commented 3 years ago

Okay, great, Jamie, thanks for clearing this up!

@bold-gman Can you answer my question in https://github.com/virtualmin/virtualmin-gpl/issues/114#issuecomment-703504993, please. I will do more tests and fixes tomorrow in this regard. I wanna really put all issues with databases to an end.

@julienld

Maybe I spoke too soon. There is a problem when the domain already exists and I try to restore an incremental backup. I get this while re-creating mailboxes :

I will test tomorrow with that case too.

If you guys have any other bugs, report it rather now than then. :slightly_smiling_face:

iliajie commented 3 years ago

I found few bugs already - upon server restore the the permissions are not set correctly and privileges are not flushed.

iliajie commented 3 years ago

Restore failed : SQL create user 'adiunctus-grabge'@'localhost' identified by password '*' failed : Operation CREATE USER failed for 'adiunctus-grabge'@'localhost'

In regard to this issue, it appears that '*<hash goes here>' contains a quote char on the inside, time after time, which made it hard to catch. Working on these issues now. When finish the tests and fix it, will let you know.

iliajie commented 3 years ago

Okay all mentioned issues have been fixed.

Again, in regard to this issue, '*<hash goes here>' - it was something else eventually, however, it helped me to look first into other direction, where I as well discovered and fixed a bug. Original issues is also fixed, as it wasn't using the right MariaDB version after all.

Anyway, you're all welcome to give it a shot:

Fixed:

Using encrypted SHA2 passwords proper escaping
Fixed missing database permissions for other virtual server databases, when grant statement used
Fixed using correct MariaDB create user statement depending on version

iliajie commented 3 years ago

@jcameron Maybe it was just a coincidence that the user ending with a .c ran into this issue?

Yes, I assume so.

Usernames with dots in MySQL are perfectly fine, considering we escape those on executing a query (which we do).

Anyone who had a problem restoring a backup with a username ending with .c, if you comment out this line on Virtualmin 6.13+ machine, restart Webmin and try restoring it again, does it still fail?

ftrojahn commented 3 years ago

Hi,

atm I'm on the way to move lots of vhosts from a Debian Jessie to Debian buster server. Both have recent versions: webmin:all/virtualmin-universal 1.962 uptodate, webmin-virtual-server:all/virtualmin-universal 6.14.gpl

This is the error I get when trying to transfer a virtual host:

Backing up to destination system ..
.. backup done

Validating backup ..
.. all domains transferred successfully

Restoring backup on destination system ..
.. restore failed : Checking for missing features .. .. WARNING - 
The following features were enabled for one or more domains in the backup, but do not exist on this system. 
Some functions of the restored domains may not work : AWstats reporting Checking for errors in backup .. .. 
no errors found 
Starting restore.. Extracting backup archive files .. .. done 
Restoring backup for virtual server archiv.xxxxx.de .. 
Restoring virtual server password, quota and other details .. .. done 
Updating administration password and quotas .. .. done Restoring Cron jobs .. .. done 
Extracting TAR file of home directory .. .. done Setting ownership of home directory .. .. done 
Restoring Apache virtual host configuration .. .. done 
Checking restored PHP execution mode .. .. mode FCGId OK for this system 
Updating home directory in PHP configuration .. .. done Restoring Apache log files .. .. done 
Restoring Webalizer configuration files and Cron job .. .. done 
Restoring SSL Apache virtual host configuration and certificate .. .. done 
Restoring Logrotate configuration .. .. done Restoring allowed MySQL hosts .. 
Error: SQL create user 'dbuser.archiv'@'localhost' identified by password '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' failed : 
Operation CREATE USER failed for 'dbuser.archiv'@'localhost' Error ----- 
SQL create user 'dbuser.archiv'@'localhost' identified by password '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' 
failed : Operation CREATE USER failed for 'dbuser.archiv'@'localhost' -----

This 'dbuser.archiv' has been created in the first try to transfer, which failed. Means: yes, the user shouldn't exist there (even after a failed transfer or restore)? But even then - shouldn't the restore overwrite it if "overwrite" is checked?

So I'll try now

backup vhosts without files
delete this mysql user, then try to recreate the vhost/s on new server from backup
if that succeeds, transfer again using full files with in overwrite mode, or transfer the files manually.

iliajie commented 3 years ago

Before retrying a restore, could you reload MySQL server?

ftrojahn commented 3 years ago

I had already deleted the mysql users "archiv" "dbuser.archiv" and the database archiv:

archiv.xxxxxxx.de (Server does not exist, will be re-created) Virtual server password, description and other details Administration user's password, quota and Cron jobs Mail/FTP users and mail aliases Apache webserver configuration Apache SSL webserver configuration and certificate Logrotate configuration for log file Contents of server's MySQL databases Webmin ACL files Server's home directory and web pages AWstats configuration file

Extracting backup archive file .. .. done

Re-creating virtual server archiv.xxxxx.de .. .. a clash was detected : A unix user named archiv already exists - try selecting a different administration username

.. failed! See the progress output above for the reason why.

:~# deluser archiv
Entferne Benutzer »archiv« ...
userdel: user archiv is currently used by process 257
/usr/sbin/deluser: »/usr/sbin/userdel archiv« gab den Fehlercode 8 zurück. Programmende.

archiv     257  0.0  0.5 472492 12352 ?        S    19:41   0:00 php-fpm: pool 13648296243948

:~# find /etc/php* -name 13648*
/etc/php/7.3/fpm/pool.d/13648296243948.conf
:~# find /etc/php* -name 13648* -delete
:~# service php7.3-fpm restart
:~# deluser archiv
~# delgroup archiv

(no mysql restart, yet)

.. a clash was detected : A webmin user called archiv already exists - try selecting a different administration username

Failed to delete users : One of the selected users is marked as non-editable

This Webmin user should not be edited as it is managed by the Virtualmin Virtual Servers module. Click here to bypass this warning and edit the user anyway - but beware that any manual changes may be over-written!

After deleting the webmin user, restore was successful - while showing these errors:

.. configuration failed : Failed to open /home/archiv/fcgi-bin/php7.2.fcgi.webmintmp.7743 : Permission denied

.. AWstats reporting failed! : virtualmin-awstats::feature_setup failed : Failed to open /home/archiv/cgi-bin/awstats.pl.webmintmp.7743 : Permission denied

So, maybe - the only problem is here, if a failure occurs, that the already restored users won't be deleted.

Perhaps - before restoring, there could be more checks, if anything exists, what cannot be overriden?

BTW: apache2 is not running after successful restore. Reason: "php_admin_value engine off" in archiv.xxxx.de.conf.

The reason for the stop of first restore which led to the dbuser.archiv mysql user problem: one of the php fcgi files in fcgi-bin/ was missing. This time: the line has been deleted when restoring.

virtualmin / virtualmin-gpl

Transfer or restore virtual server errors on overwrite #114