Error Enabling MySQL Database

Virtualmin
21

How do we apply this patch? Directly edit file? Where the file is stored? If someone can guide me it would be great.

Any official update via yum to patch previous update?

Thanks in advance

22

Hi
@Neboysha do a search for the file (make a backup copy before editing) once complete restart Webmin and MySQL.

Thank you @JamieCameron for the patch - applied to Debian 9 all working again as expected.

Kind Regards

Brad

23

Hi Brad, thanks for the help! Will try it asap.

24

Hi,
Thank you @JamieCameron for the patch, it works fine.
Debian 9 & MariaDB 10.1.37

Kind Regards
Francesco

25

Hi, Thank you @JamieCameron, patch works on Debian 9.8 & MariaDB 10.1.37
Don’t forget to restart Webmin after patching!

26

Hi

Yes thanks the patch fixed it on Debian 9 but I might have found another issue. After applying the patch and after installing PHPmyAdmin (Script Install) I discovered later I was not able to log into PHPmyAdmin using the credentials I created a few days before, I am getting the following error that just looks like another MariaDB version issue:

mysqli_real_connect(): (HY000/1275): Server is running in --secure-auth mode, but ‘xxxxxxxx’@‘localhost’ has a password in the old format; please change the password to the new format

From what I read, password hash did change in the last MariaDB versions and it seems that Virtualmin does not take this into account when creating databases although it has the server running in --secure-auth mode … I will suppress that option to be able to login and change the password to the new format and then reinstate that option …

Pierre.

27

Thanks! But how about: MySQL version 10.2.22-MariaDB ?

I got this error:
Creating MySQL login …
… MySQL database failed! : mysql::execute_sql_logged failed : SQL insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values (‘localhost’, ‘visualismo123123’, ‘’, ‘’, ‘’, ‘’) failed : Field ‘authentication_string’ doesn’t have a default value at /usr/libexec/webmin/web-lib-funcs.pl line 1478.
Saving server details …
… done

Thank you very much!

28

Hi everybody

I solved it by simply removing STRICT_TRANS_TABLES from sql_mode in /etc/my.cnf :wink:

29

I have an ubuntu 18.04.2 system MySQL version 5.7.26 , can’t find sql_mode in any mysql configuration file, in the meantime I will downgrade virtualmin

30

Same here, when I want to look at a table in mysql it says 404 file not found

31

I have same issue.

Creating MySQL login …
… MySQL database failed! : mysql::execute_sql_logged failed : SQL insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values (‘localhost’, ‘demo’, ‘’, ‘’, ‘’, ‘’) failed : The target table user of the INSERT is not insertable-into at …/web-lib-funcs.pl line 1476

CentOS7 MaruaDB10.4.6
sql_mode = ‘’

32

I am having the exact same issue.

Failed to save user : SQL insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject, plugin, authentication_string) values (‘localhost’, ‘user’, ‘’, ‘’, ‘’, ‘’, ‘mysql_native_password’, password(‘xxxxxxxx’)) failed : The target table user of the INSERT is not insertable-into

CentOS 7
mysql Ver 15.1 Distrib 10.4.6-MariaDB, for Linux (x86_64) using readline 5.1

33

QuickFix for the great Virtualmin and MariaDB 10.2 - 10.4.6

https://www.virtualmin.com/node/66733

regards to all !!!

34

Also read about 10.4.x https://www.virtualmin.com/comment/815163#comment-815163

35

I’m struggling with this too - “Field ‘authentication_string’ doesn’t have a default value at /usr/share/webmin/web-lib-funcs.pl line 1476.”
Maria 10.3.17
Webmin 1.921
Virtualmin 6.07
Ubuntu 18.04

Also, I applied the promising patch documented by Christos above (#33), but no joy.

Obviously I can create a db by hand, but then the backup utility won’t dump it, as it doesn’t think the domain has a db.
Any guidance appreciated.

Perhaps reverting to MySQL instead of Maria…?

36

I tried some temp solution with Virtualmin downgrade

wget http://download.webmin.com/download/virtualmin/wbm-virtual-server-6.05.gpl-1.noarch.rpm

rpm -U --force wbm-virtual-server-6.05.gpl-1.noarch.rpm

and now can create mysql database along with virtual server. I first tried to downgrade to 6.06 but still same error appeared. I think it all has something to do with recent MariaDB update.

37

For a quick fix for this, add default value to mysql.

SSH to mysql

mysql -h localhost -u root -p mysql

apply default value for the field, on error.

ALTER TABLE mysql.user ALTER authentication_string SET DEFAULT '';

Works now for me, (centos user)

38

KikoSeijo’s fix worked for me on the one server I tried it on (Ubuntu 18.04, Maria 10.3.17, Virtualmin 6.07).

39

This is because in version 10.4 of MariaDB, the mysql.user table is now a view, which is either not insert-able, or requires a very particular INSERT statement to not explode like this.
This error occurs in the part where the mysql/mariadb user is created, using insert/alter SQL statements, which is documented in MariaDB as not optimal, and recommends using CREATE USER statements instead.
I have patched my version of the /usr/libexec/webmin/virtual-server/feature-mysql.pl file to use this approach, and it seems to work. I am posting the patch file below for the VirtualMin team to look at, and others to try at their own risk, as it is one way to get around this problem, for now. Note that this fix assumes the password for creation is passed in $plainpass and is not blank. If it is, you’ll have an open mariadb account with no password, so beware.

Big caveat: I am no virtualmin dev; this is my first foray into fiddling with its guts, by necessity and, despite being an “seasoned” software engineer, I am no oracle with this software. So, again, beware.

======================================== <— copy everything between these
— feature-mysql.pl.release 2019-08-19 08:50:59.623648444 -0700
+++ feature-mysql.pl.fixed 2019-08-19 09:15:48.910963467 -0700
@@ -2745,7 +2745,10 @@
my $qpass = &mysql_escape($plainpass);
$encpass = “$password_func(’$qpass’)”;
}
-if (($variant eq “mysql” && &compare_versions($ver, “8”) >= 0 ||
+if ($variant eq “mariadb” && &compare_versions($ver, “10.4”) >= 0) {

  •   return ("create or replace user '$user'\@'$host' identified by '$plainpass'");

  •   }

+elsif (($variant eq “mysql” && &compare_versions($ver, “8”) >= 0 ||
$variant eq “mariadb” && &compare_versions($ver, “10.2”) >= 0) &&
$plainpass) {
my $native = &is_domain_mysql_remote($d) ?
============================================== <— don’t include these lines

Edit: Minor fix for SQL syntax.

40

Works, thanks @KikoSeijo