Add MySQL Database feature fail on Virtual Server setting

Submitted by VirtualminTomsshk1 on Mon, 07/03/2017 - 23:46

Creating MySQL login .. .. MySQL database failed! : SQL insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('localhost', 'admin_finefoodhk', '', '', '', '') failed : Field 'authentication_string' doesn't have a default value at /usr/libexec/webmin/web-lib-funcs.pl line 1433.

The Virtualmin Pro seems not able to handling the MySQL database management properly.

Status: 
Active

Comments

Submitted by andreychek on Tue, 07/04/2017 - 08:57

Howdy -- thanks for contacting us!

What MySQL version is it that you're using there?

You can determine that with this command:

mysql -V

Submitted by JamieCameron on Tue, 07/04/2017 - 10:35

Also, is MySQL running on the same system as Virtualmin, or a remote server?

Submitted by VirtualminTomsshk1 on Thu, 07/06/2017 - 01:47

Hi andreychek,

mysql -V result is

mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper

Submitted by andreychek on Thu, 07/06/2017 - 10:17

Thanks!

One last question from Jamie -- is MySQL running on the server itself, or is it on a remote server?

Submitted by VirtualminTomsshk1 on Sun, 07/09/2017 - 21:34

Yes, the MySQL DB Server are also running on the same server.

Submitted by VirtualminTomsshk1 on Tue, 07/11/2017 - 05:26

Any information for investigation are also needs?

Anything I can try to solve it?

Submitted by JamieCameron on Tue, 07/11/2017 - 16:26

If you go to Webmin -> Servers -> MySQL Database, what version number does it show at the top of the page?

Submitted by VirtualminTomsshk1 on Wed, 07/12/2017 - 13:51

The MySQL Database version number show at the top of that page is MySQL version 5.6.36

Submitted by VirtualminTomsshk1 on Thu, 07/13/2017 - 20:42

Is it possible to provide root access for support team to figure out the root cause of my issue?

Submitted by JamieCameron on Fri, 07/14/2017 - 00:57

How did you install this MySQL version exactly? Is it the package that came with your Linux distribution, or did you upgrade manually?

Submitted by VirtualminTomsshk1 on Sun, 07/16/2017 - 22:23

I have installed the MySQL by Yum on my CentOS 7 distribution.

Submitted by JamieCameron on Mon, 07/17/2017 - 23:40

Ok, I think we'd need remote root access to debug this further.

Submitted by VirtualminTomsshk1 on Tue, 07/18/2017 - 01:04

How can I let you login?

Submitted by JamieCameron on Wed, 07/26/2017 - 22:16

Ok, it looks like you are running a MySQL package that comes from a third-party source, not your linux distribution - that explains the problem. I have applied a work-around to your system to address though ... let me know if it helps.

Submitted by VirtualminTomsshk1 on Thu, 07/27/2017 - 12:54

Thank you very much. Is it possible to occur problem in future update?

Submitted by JamieCameron on Fri, 07/28/2017 - 00:33

It shouldn't. Did my patch fix your system though?

Submitted by VirtualminTomsshk1 on Sat, 07/29/2017 - 10:22

The patch you have applied can fix my system. But what should I do whil I need to update my Linux or Virtualmin?

Submitted by JamieCameron on Sat, 07/29/2017 - 17:00

The patch will be included in future releases.

Submitted by VirtualminTomsshk1 on Thu, 08/03/2017 - 04:52

I have found a new issue. The new created virtual server are able to create the default database, and I can able to import tables into the new created database. But the auto-created database user/password doesn't work. I have made a simple db connection test php file, it returns error 500.

Submitted by andreychek on Thu, 08/03/2017 - 09:02

Hmm, when you receive that 500 error, what error are you seeing in the error log for the domain?

You can see that log in $HOME/logs/error_log.

Submitted by VirtualminTomsshk1 on Thu, 08/03/2017 - 14:19

Hello,

I have fixed this issue by myself. The MySQL notify me to run and upgrade command to fix all table structures. Everything back to normal finally.

Submitted by pmj on Fri, 06/07/2019 - 07:24

Hi everybody

On my old server everything worked fine but when i switched to a fresh installation i got this error... so i compared the two installations and noticed that i removed STRICT_TRANS_TABLES from sql_mode in /etc/my.cnf on my old server ;) so i gave it a try on my new server and voilà, the error was gone and mysql users were created ;) note: this was for a GPL version of virtualmin but it should work for pro too

Submitted by JamieCameron on Sun, 06/09/2019 - 22:17

Ok, so the issue was likely a mismatch between the MySQL version and it's internal user tables - right now this isn't something Virtualmin is likely to be able to handle automatically.