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?
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.
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
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.
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 SizeDowngrading: wbm-virtual-server noarch 2:6.05.gpl-1 virtualmin-universal 2.2 M
Transaction SummaryDowngrade 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
Please take a look into this https://www.virtualmin.com/node/59032 it seems related.
its not the same issue..
Same issue here.
Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6
Hi! Syntax "alter user" is only available for MariaDB 10.2.0
Documentation: https://mariadb.com/kb/en/library/alter-user/
Temporary solution as described: Downgrade to Virtualmin 6.05 gpl
[sudo] apt-get install webmin-virtual-server=6.05.gpl
=)
Yeah, works, thanks. I lost so much time with this bug..
Nice, 6.05 works. :)
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.
I can confirm as well that going to 6.0.5 works. I did not uninstall 6.0.6, just installed 6.0.5.
I can confirm as well that going to 6.0.5 works.
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?
''
Operating system: Debian Linux 9 64-bit
MySQL version: 10.1.37-MariaDB-0+deb9u1
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
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
You can get a quick fix for this problem by applying this patch : https://github.com/virtualmin/virtualmin-gpl/commit/a45a2b839bc3ffc80f25...
''
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
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
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
Hi Brad, thanks for the help! Will try it asap.
Hi, Thank you @JamieCameron for the patch, it works fine. Debian 9 & MariaDB 10.1.37
Kind Regards Francesco
Hi, Thank you @JamieCameron, patch works on Debian 9.8 & MariaDB 10.1.37 Don't forget to restart Webmin after patching!
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 formatFrom 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.
Hi everybody
I solved it by simply removing STRICT_TRANS_TABLES from sql_mode in /etc/my.cnf ;)
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
Same here, when I want to look at a table in mysql it says 404 file not found
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 = ''
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
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.
does this work on 6.07 and mariadb 10.4.37
And is there a cleaner version of the above fix
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
Also read about 10.4.x https://www.virtualmin.com/comment/815163#comment-815163
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...?
Sam Moore smoore@resonetrics.com https://resonetrics.com https://www.linkedin.com/in/sammooreatresonetrics https://drupal.org/user/57956
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.
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)
KikoSeijo's fix worked for me on the one server I tried it on (Ubuntu 18.04, Maria 10.3.17, Virtualmin 6.07).
Sam Moore smoore@resonetrics.com https://resonetrics.com https://www.linkedin.com/in/sammooreatresonetrics https://drupal.org/user/57956
Works, thanks @KikoSeijo
Simple fix, worked for me aswell.
CentOS 7.7.1908 // Virtualmin 6.07 // MariaDB 10.2.27
-- Thanks KikoSeijo!
You can do a simple password reset on the mysql console as a temporary solution. For example:
# mysql -p-> Enter your mysql passwordReset 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.
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
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
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 passworduse 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...