Error Enabling MySQL Database

46 posts / 0 new
Last post
#1 Sat, 02/16/2019 - 20:12
en3r0

Error Enabling MySQL Database

I am using MySQL version 10.1.37 and trying to enable the MySQL Database feature on a virtual server.

I get this error:

Creating MySQL login .. .. MySQL database failed! : mysql::execute_sql_logged failed : SQL alter user 'username'@'localhost' identified by 'fswCyQ1SSoP48Xe' 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 'user 'username'@'localhost' identified by 'fswCyQ1SSoP48Xe'' at line 1 at /usr/share/webmin/web-lib-funcs.pl line 1478.

Saving server details .. .. done

I initially installed webmin and add the virtualmin module. This is also on a RaspberryPi running Raspbian, but I don't think is directly related here.

Any thoughts?

Sun, 02/17/2019 - 08:31
henkaman

Same problem here:

Creating MySQL login ..
.. MySQL database failed! : mysql::execute_sql_logged failed : SQL alter user '****'@'localhost' identified by '**9*' 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 'user '****'@'localhost' identified by '****'' at line 1 at /usr/share/webmin/web-lib-funcs.pl line 1478.

I have enabled MySQL for other sites on the same machine before, but that was before I updated every package yesterday.

Sun, 02/17/2019 - 09:58
Ned

I have the same problem!

I get this error:

Creating MySQL login .. .. MySQL database failed! : mysql::execute_sql_logged failed : SQL alter user 'apazadito'@'localhost' identified by 'xxxxxxxxxxxxx' 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 'user 'apazadito'@'localhost' identified by 'xxxxxxxxxxxxx'' at line 1 at /usr/share/webmin/web-lib-funcs.pl line 1478.

Updating Webmin user .. .. done

Updating Webmin user .. .. done

Saving server details .. .. done

Re-loading Webmin .. .. done

Sun, 02/17/2019 - 15:05
g77

I made fresh install of system and virtualmin.. (debian 9, 2gb rab) and ...

Creating MySQL login .. .. MySQL database failed! : mysql::execute_sql_logged failed : SQL alter user 'gfdn'@'localhost' identified by 'test123' 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 'user 'gfdn'@'localhost' identified by 'test123'' at line 1 at /usr/share/webmin/web-lib-funcs.pl line 1476.

Mon, 02/18/2019 - 05:27
Neboysha

Same issue here, server made 7 days ago and was able to made new mysql database along with new account.

However after update of server few days ago this error came out

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', 'test', '', '', '', '') failed : Field 'authentication_string' doesn't have a default value at ../web-lib-funcs.pl line 1478.

So the problem is in version 6.06 that came out few days ago. As a temporary solution I did yum rollback of that upgrade

=====================================================================================================================================================

Package Arch Version Repository Size

Downgrading: wbm-virtual-server noarch 2:6.05.gpl-1 virtualmin-universal 2.2 M

Transaction Summary

Downgrade 1 Package

and downgrade to Virtualmin 6.05

Just for the record I use latest Mariadb form mariadb repo

MySQL version 10.3.12

and it worked so far on all servers.

Thanks, Neboysha

Mon, 02/18/2019 - 05:37
Neboysha

Please take a look into this https://www.virtualmin.com/node/59032 it seems related.

Mon, 02/18/2019 - 07:50 (Reply to #6)
g77

its not the same issue..

Mon, 02/18/2019 - 07:29
geissweb

Same issue here.

Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

Mon, 02/18/2019 - 07:53
knightmr

Hi! Syntax "alter user" is only available for MariaDB 10.2.0

Documentation: https://mariadb.com/kb/en/library/alter-user/

Mon, 02/18/2019 - 08:20
knightmr

Temporary solution as described: Downgrade to Virtualmin 6.05 gpl

[sudo] apt-get install webmin-virtual-server=6.05.gpl

=)

Mon, 02/18/2019 - 09:10 (Reply to #10)
g77

Yeah, works, thanks. I lost so much time with this bug..

Mon, 02/18/2019 - 09:34
geissweb

Nice, 6.05 works. :)

Mon, 02/18/2019 - 09:55
bhelm

yes, downgrading to 6.0.5 works, the other option is to upgrade mysql.

webmin should check the mysql version or use the old sql commands.

Mon, 02/18/2019 - 11:00
en3r0

I can confirm as well that going to 6.0.5 works. I did not uninstall 6.0.6, just installed 6.0.5.

Mon, 02/18/2019 - 12:09
Ned

I can confirm as well that going to 6.0.5 works.

Mon, 02/18/2019 - 12:11
JamieCameron

This looks like a bug in the way password changes are handled in Virtualmin 6.06.

People seeing this problem - what MySQL version are you running, and on which Linux distribution?

''

Mon, 02/18/2019 - 12:33 (Reply to #16)
Ned

Operating system: Debian Linux 9 64-bit

MySQL version: 10.1.37-MariaDB-0+deb9u1

Mon, 02/18/2019 - 15:01 (Reply to #17)
Neboysha

MySQL: MySQL version 10.3.12 (Mariadb from mariadb repo installed before Virtualmin as always I'm doing in last 2 years, first install latest mariadb from repo https://downloads.mariadb.org/mariadb/repositories/ and then install Virtualmin that recognize MySQL was already installed)

OS: Centos 7 latest

Mon, 02/18/2019 - 12:42
knightmr

Debian 9 MariaDb 10.1.x

Syntax "alter user" is only available for MariaDB 10.2.x

The solution: upgrade mariadb to 10.2.x Or downgrade to virtualmin 6.05gpl: [sudo] apt-get install webmin-virtual-server=6.05.gpl

Mon, 02/18/2019 - 18:18
JamieCameron

You can get a quick fix for this problem by applying this patch : https://github.com/virtualmin/virtualmin-gpl/commit/a45a2b839bc3ffc80f25...

''

Wed, 02/20/2019 - 09:43 (Reply to #20)
Neboysha

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

Tue, 02/26/2019 - 16:08 (Reply to #21)
turquijo

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!

Tomás

Thu, 02/21/2019 - 03:33
brad100

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

Thu, 02/21/2019 - 03:47 (Reply to #23)
Neboysha

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

Fri, 02/22/2019 - 10:41
fcforum

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

Kind Regards Francesco

Sat, 02/23/2019 - 03:25
3k

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

Sat, 02/23/2019 - 10:16
Pierrot

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.

Tue, 06/04/2019 - 12:16
pmj

Hi everybody

I solved it by simply removing STRICT_TRANS_TABLES from sql_mode in /etc/my.cnf ;)

Sun, 06/16/2019 - 21:13 (Reply to #28)
leofishman

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

Thu, 06/27/2019 - 19:43
haydrionrayel
haydrionrayel's picture

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

Sun, 07/07/2019 - 21:06
irieyuusuke

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 = ''

Wed, 07/24/2019 - 16:02 (Reply to #31)
dcmoody

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

Mon, 08/19/2019 - 12:55 (Reply to #32)
Talarus Luan

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.

Fri, 09/06/2019 - 11:02 (Reply to #33)
krustylicious

does this work on 6.07 and mariadb 10.4.37

And is there a cleaner version of the above fix

Wed, 07/31/2019 - 08:41
Christos_Tzeremes

QuickFix for the great Virtualmin and MariaDB 10.2 - 10.4.6

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

regards to all !!!

Christos Tzeremes IT Expert @ nasa.gr Thessalonikh GREECE [MACEDONIA] ctzeremes[at]nasa.gr

Wed, 07/31/2019 - 10:43
Mon, 08/12/2019 - 19:26
Sam Moore

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

Tue, 08/13/2019 - 04:42
Neboysha

I tried some temp solution with Virtualmin downgrade

wget http://download.webmin.com/download/virtualmin/wbm-virtual-server-6.05.g...

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.

Tue, 08/13/2019 - 06:59
KikoSeijo

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)

Tue, 08/13/2019 - 18:55 (Reply to #39)
Sam Moore

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

Sat, 08/24/2019 - 08:36 (Reply to #40)
g77

Works, thanks @KikoSeijo

Fri, 10/11/2019 - 16:27 (Reply to #41)
pfrota

Simple fix, worked for me aswell.

CentOS 7.7.1908 // Virtualmin 6.07 // MariaDB 10.2.27

-- Thanks KikoSeijo!

Tue, 10/15/2019 - 09:21
lexo_ch

You can do a simple password reset on the mysql console as a temporary solution. For example:

# mysql -p -> Enter your mysql password

Reset the password of the user that does not work anymore like this:

set password for 'youruser@domain.tld'@'localhost' = PASSWORD('your-new-password');

This will store the password in the new format into the database.

Tue, 11/05/2019 - 19:43
leofishman

I tried downgrading to 6.05 restarting webmin and still have the 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, plugin, authentication_string) values ('localhost', 'newsletter', '', '', '', '', 'mysql_native_password', password('XXXXXXXXX')) failed : Your password does not satisfy the current policy requirements at ../web-lib-funcs.pl line 1477.

Ubuntu 18.04 MySQL version 5.7.27

Tue, 11/05/2019 - 19:48
leofishman

This is what I did to fix it in Ubuntu 18.04 MySQL version 5.7.27

# mysql -h localhost -u root -p

mysql> SET GLOBAL validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> uninstall plugin validate_password;
Query OK, 0 rows affected (0.00 sec)

#systemctl restart mysql
Wed, 12/11/2019 - 00:43 (Reply to #45)
lexo_ch

Personally I would not uninstall the password validation plugin. It's only a matter of time until Virtualmin has upgraded their system and reached compatibility. Currently I manually reset the database passwords after creating/updating a host like this:

mysql -p <-- enter password

use mysql;
set password for 'db-user'@'localhost' = PASSWORD('');
set password for 'db-user'@'localhost' = PASSWORD('the-password-you-set-in-virtualmin');
flush privileges;

This works for now. Theoretically you could put these commands into a bash-script and have it executed automatically on every CREATE or UPDATE vHost command. How to create such a file and where to set it in Virtualmin we've described (by an example with another service) here:

https://www.lexo.ch/blog/2016/12/howto-setting-up-virtualmin-with-buddyn...