MariaDB version 10.4.11 error MySQL Database Server/Create User and HOME folder

[lisandi]

.../mysql/save_user.cgi?xnavigation=1

Error message:
Failed to save user : SQL insert into user (host, user, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, Delete_history_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, authentication_string) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) failed : The target table user of the INSERT is not insertable-into

It is impossible to create a new database user via

Webmin - Mysql Database Server - User Permissions - Create new user

When creating a new Virtual Server and activating MySQL in Virtualmin, the Database user gets created

Username for MySQL database | ab-c
Password for MySQL database | AbCD1eFghhK2Lm3n

Besides that, creating a new Virtualmin Server 'ab-c.123' results in a Virtualmin user/group/folder like 'ab-c' and not 'ab-c.123' and a Database user 'ab-c'

Creating administration group ab-c ..
.. done
Creating administration user ab-c ..
.. done
Creating aliases for administration user ..
.. done
Adding administration user to groups ..
.. done
Creating home directory ..
.. done
Creating mailbox for administration user ..
.. done
Creating MySQL login ..
.. done
Creating MySQL database ab_c ..
.. done
Saving server details ..
.. done

When trying to apply changes it gives a correct warning:
WARNING: This MySQL user (ab_c) is the administration login for Virtualmin domain ab-c.123. Changing any settings here may cause errors in Virtualmin.

BUT problem occurs as in the old server the folder in home was called 'ab-c.123' and now only a user/group/folder 'ab-c' gets created.

EVEN a message when creating a username with a sign not allowed states that DOTs are allowed:
Failed to create virtual server : Missing or invalid domain name - only letters, numbers and the dot, dash and underscore characters are allowed

ADDITION: just realized that the underscore also doesn't get accepted in a username! Only the Hypen is OK!

When importing a sub-server now to this newly created Virtualmin/Webmin User the sub-server Database gets created the sub-server can't get imported because of those differences in the user/group-name and home folder name.

Starting restore of 1 domains from local file /BACKUP/abc.tld.tar.gz ..
Extracting backup archive file ..
.. done
Re-creating virtual server abc.tld ..
Creating home directory ..
.. done
Adding new virtual website ..
.. done
Performing other Apache configuration ..
.. configuration failed : Failed to copy /etc/php/7.4/cgi/php.ini to /home/ab-c.123/domains/abc.tld/etc/php7.4/php.ini : cp: cannot create regular file '/home/ab-c.123/domains/abc.tld/etc/php7.4/php.ini': No such file or directory
Creating SSL certificate and private key ..
.. SSL website failed! : Failed to open /home/ab-c.123/domains/abc.tld/ssl.cert.webmintmp.27766 : No such file or directory at /usr/share/webmin/web-lib-funcs.pl line 1495, line 1.
Setting up log file rotation ..
.. done
Creating MySQL database ab_abc_tld ..
.. done
Applying web server configuration ..
.. done
Saving server details ..
.. done
Restoring backup for virtual server abc.tld ..
Restoring virtual server password, quota and other details ..
.. done
Extracting TAR file of home directory ..
.. TAR failed!

/bin/sh: 1: cd: can't cd to /home/ab-c.123/domains/abc.tld
.. failed! See the progress output above for the reason why.

BUT when importing (backup locally uploaded and then Restore Backup) a Virtual-Server from another Webmin installation which runs MySQL version 10.1.43-MariaDB-0ubuntu0.18.04.1 to the new Ubuntu 18.04.1 server with MariaDB version 10.4.11 in no user creation and when importing a subserver with Database to that virtualmin user it results in an empty database but all server content gets created.

Starting restore of 1 domains from local file /BACKUP/ab-c.123.tar.gz ..
Extracting backup archive file ..
.. done
Re-creating virtual server ab-c.123 ..
Creating administration group ab-c.123 ..
.. done
Creating administration user ab-c.123 ..
.. done
Creating aliases for administration user ..
.. done
Adding administration user to groups ..
.. done
Creating home directory ..
.. done
Creating mailbox for administration user ..
.. done
Creating MySQL login ..
.. MySQL database failed! : mysql::execute_sql_logged failed : SQL create user 'ab-c.123'@'localhost' identified by password('') failed : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('')' at line 1 at /usr/share/webmin/web-lib-funcs.pl line 1495.
Saving server details ..
.. done
Restoring backup for virtual server ab-c.123 ..
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 mail and FTP users ..
.. done
Re-creating mail aliases ..
.. done
Restoring mail and FTP user Cron jobs ..
.. done
Enabling PHP modules for restored scripts ..
.. no PHP modules needed to be installed
.. restore complete.

AND when now importing the same sub-server like above an empty database gets created and all content gets restored as the folder ab-c.123 got created.

Starting restore of 1 domains from local file /BACKUP/abc.tld.tar.gz ..
Extracting backup archive file ..
.. done
Re-creating virtual server abc.tld ..
Creating home directory ..
.. done
Adding new virtual website ..
.. done
Adding webserver user www-data to server's group ..
.. done
Performing other Apache configuration ..
.. done
Creating SSL certificate and private key ..
.. done
Adding new SSL virtual website ..
.. done
Setting up log file rotation ..
.. done
Creating MySQL database ab_abc_tld ..
.. MySQL database failed! : mysql::execute_sql_logged failed : SQL grant all privileges on `ab\_abc\_tld`.* to 'ab-c.123'@'localhost' failed : Can't find any matching row in the user table at /usr/share/webmin/web-lib-funcs.pl line 1495.
Applying web server configuration ..
.. done
Re-starting Webmin ..
.. done
Re-starting Usermin ..
.. done
Saving server details ..
.. done
Restoring backup for virtual server abc.tld```
 ..
Restoring virtual server password, quota and other details ..
.. 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 SSL Apache virtual host configuration and certificate ..
.. done
Restoring Logrotate configuration ..
.. done
Re-creating mail and FTP users ..
.. done
Re-creating mail aliases ..
.. done
Restoring mail and FTP user Cron jobs ..
.. done
Enabling PHP modules for restored scripts ..
.. no PHP modules needed to be installed
Applying web server configuration ..
.. done
Applying web server configuration ..
.. done
.. restore complete.

If the Virtualmin User has no DOT or other non-alphabetical or non-numerical letter in its name the output is:

Starting restore of 1 domains from local file /BACKUP/abc1def.tar.gz ..
Extracting backup archive file ..
.. done
Re-creating virtual server abc1def ..
Creating administration group abc1def ..
.. done
Creating administration user abc1def ..
.. done
Creating aliases for administration user ..
.. done
Adding administration user to groups ..
.. done
Creating home directory ..
.. done
Creating mailbox for administration user ..
.. done
Creating MySQL login ..
.. done
Creating MySQL database abc1def ..
.. done
Creating Webmin user ..
.. done
Re-loading Webmin ..
.. done
Saving server details ..
.. done
Restoring backup for virtual server abc1def ..
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 Webmin ACL files ..
.. done
Re-creating mail and FTP users ..
.. done
Re-creating mail aliases ..
.. done
Restoring mail and FTP user Cron jobs ..
.. done
Updating Webmin user ..
.. done
Enabling PHP modules for restored scripts ..
.. no PHP modules needed to be installed
Re-loading Webmin ..
.. done
.. restore complete.

As a work-around for importing servers you can at least import the content like this, but then would need to move everything to another user and importing the database manually to this specific new user. - Unfortunately, there seems to be no way to import a sub-server to a newly created user and into a newly created home directory of that user.

i.e moving the sub-server abc.tld from its main server 'ab-c.123' to a newly created main server 'ab-c'

Moving virtual server abc.tld under ab-c ..
Moving home directory ..
.. done
Setting ownership for home directory ..
.. done
Updating users ..
.. done
Changing home directory in website configuration ..
.. done
Changing username in website configuration ..
.. done
Adding webserver user www-data to server's group ..
.. done
Updating home directory in PHP configuration ..
.. done
Changing home directory in SSL website configuration ..
.. done
Changing username in SSL website configuration ..
.. done
Updating home directory in Logrotate configuration ..
.. done
Updating user and group in Logrotate configuration ..
.. done
Updating paths in script database ..
.. done
Saving server details ..
.. done
Updating Webmin user ..
.. done
Updating Webmin user ..
.. done
Applying web server configuration ..
.. done
Applying web server configuration ..
.. done
Re-starting Webmin ..
.. done
Re-starting Usermin ..
.. done
Re-loading Webmin ..
.. done

When you change the Domain Name of the main server - which would be another option to get rid of the DOT - another problem occurs when then afterward trying to activate the MySQL Database

Creating MySQL login ..
.. MySQL database failed! : mysql::execute_sql_logged failed : SQL create user 'ab-c.123'@'localhost' identified by password('') failed : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('')' at line 1 at /usr/share/webmin/web-lib-funcs.pl line 1495.
Saving server details ..
.. done

This happens even the Domain name was changed from 'ab-c.123' to 'abc' before! It still tries to create the user 'ab-c.123' which of course results again in failure. So only a manual MOVE of the folder which might contain content you need would be a work-around until those problems get fixed.

In all cases deleting the virtual server results in:

Deleting the server is working:
Output:
Deleting mail aliases ..
.. done
Deleting MySQL database ab_c ..
.. done
Deleting MySQL login ..
.. done
Deleting home directory ..
.. done
Deleting administration user ..
.. done
Deleting administration group ..
.. done
Deleting server details for ab-c.123 ..
.. done

which is correct.
But when deleting the restored sub-server which got restored in the restored main-server the empty database gets NOT deleted and therefore it might cause problems when recreating a server as this database already exists.

I suggest issuing at least a warning for Webmin/Virtualmin users/admin that a username can NOT contain any DOTs or other non-alphabetical or nom-numerical letters/signs to avoid problems with the database user and home folder creation.

After the import the permissions for the database user have to be set/created manually
https://.../mysql/list_dbs.cgi?xnavigation=1
They don't get created by the database creation or changed by a move

Work around:

1. Import the main-server as only that one creates the correct home folder for the sub-server
2. Import the sub-server which will create an empty database but restore all files and folders
3. Create a new main-server and activate the MYSQL Database in that
4. Move the imported sub-server to that new main-server
5. Extract the backup file of the imported sub-server and go here to the .backup folder which holds the MySQL.tar.gz backup file
6. With the MOVE command also the empty database got moved and now open that empty database and import the content from that MySQL.tar.gz backup file to it
7. Go to the database settings of the site and change user/password accordingly like you find all information in Virtualmin main-server Database-Password
8. go to https://.../mysql/list_dbs.cgi?xnavigation=1
   and change/create the database permissions - i.e. choose the database which got created automatically ...
9. ... and then change in the dropdown first row 'selected' the Database you need.

I hope those problems get fixed soon as they are pretty blocking any workflow and automation of processes. The Virtualmin related problem with the database had been fixed already:
([https://github.com/virtualmin/virtualmin-gpl/issues/137])

[iliajie]

Full support for MariaDB 10.4+ and MySQL 8+ has been implemented in Webmin 1.960 and Virtualmin 6.13. Restore MySQL has also been fixed.

[alexbogias]

I upgraded to mariadb 11 in my debian11 11 box and I now cant manage my databases inside webmin:

Is this related ?

[iliajie]

MariaDB 11 deprecated all mysql* command prefixes and replaced it with mariadb*, therefor you would need to manually update Webmin MariaDB module config for things to work.

Also, I don't see any practical reasons in upgrading to MariaDB 11 in production environment.

[alexbogias]

Thanks a lot @iliajie for your fast response. So you suggest to downgrade to mariadb v10.5? You think is this safe to try it now?

[iliajie]

I wouldn't use it in production. There is no point, just let it bake.

Downgrade .. ? I wouldn't do downgrades from a newer version to an older. If you don't have any data used in MariaDB, simply use apt-get purge mariadb* to completely remove everything related to MariaDB. Also, delete /var/lib/mysql directory, and then just install MariaDB with OS provided packages.

Also, I personally use Debian 12 on one of my machines, and see no reason to use Debian 11 nowadays.

[alexbogias]

Thanks a lot for the recommendation @iliajie. It was an old machine with Debian 9 and MySQL low version. I need a specific version of MariaDB for a Magento 2 and that's why I switched to. Everything went smoothly.
Last week I decided to upgrade Debian 9 and reached to 11.
I try to make checks in each version that everything in virtualmin looks good.

I will now try to upgrade to Debian 12 and then try to purge and reinstall mysql.

Can you suggest me the best version to use? Mysql or Mariadb with debian 12 and virtualmin? And whitch specific version for production?

[iliajie]

Can you suggest me the best version to use? Mysql or Mariadb with debian 12 and virtualmin? And whitch specific version for production?

I'd used MariaDB. Also, I wouldn't distro-upgrade. It's much cleaner and simpler to just install Debian 12 and migrate.