MySQL With Cloudmin Services

I'm trying to use my MySQL VM with Cloudmin Services. I have it all enabled per the instructions. When trying to add MySQL to an existing host in Virtualmin I get the error:

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

When creating a new host I get this:

... Creating MySQL login on services host .. .. creation error : MySQL login limit exceeded: You are not allowed to setup the MySQL logins feature ... [code]   The user is set to be able to create unlimited databases and logins. What needs to be checked?

Comments

Steffan's picture
Submitted by Steffan on Thu, 09/15/2016 - 23:29 Pro Licensee

Are there changes needed to the default MySQL control panel settings or does the CM Services MySQL override that?

Steffan's picture
Submitted by Steffan on Thu, 09/15/2016 - 23:39 Pro Licensee

Patches welcome, Jamie. I'm happy to test.

Steffan's picture
Submitted by Steffan on Thu, 09/15/2016 - 23:55 Pro Licensee

So, I look at the MySQL databases and I see it says it created one. It's not on the CM Services MySQL Host. I hit delete and get this:

Deleting MySQL database servicestest on services host .. .. deletion failed : unprovision-mysql-database failed : Missing --host parameter Remove a MySQL database on some provisioning system. cloudmin unprovision-mysql-database --database name --host name [--owner name] Exit status: 256

How should the settings look in the MySQL control panel?

Steffan's picture
Submitted by Steffan on Fri, 09/16/2016 - 00:39 Pro Licensee

Ok, so digging more... I went to the CM Services page in Virtualmin and hit save. I get this:

Checking login to services host xx.com.. .. done Checking Cloudmin services limits .. .. your limits are : 99999 DNS zones, 0 DNS slave zones, 0 MySQL logins, 0 MySQL databases, 0 Virus clients, 0 Spam clients Saving Cloudmin services settings .. .. done

This led me to check the user in CM. I go there and the entries I made to the amount of databases and users is blank. I change it then go back to look and its back to zero. Seems like there's a bug with the errors being shown and then also in CM because it's not saving the amount of dbs and logins.

Steffan's picture
Submitted by Steffan on Fri, 09/16/2016 - 00:39 Pro Licensee

Unchecking the box that says use limits allowed me to actually have values save in CM and then a limit to show in Virtualmin. This may not be the right way but it did get me further.

Creating MySQL database servicestest on services host .. .. creation error : No provisioning record for client XX.XX.XX.XX, owner XXXXX and user servicestest found Updating Webmin user .. .. done Re-loading Webmin .. .. done

What does that error mean?

Steffan's picture
Submitted by Steffan on Fri, 09/16/2016 - 00:45 Pro Licensee

Figured out the limits thing. That's a bit odd. I'll see about adding it to the docs. I just can't figure out that provisioning record error.

Steffan's picture
Submitted by Steffan on Fri, 09/16/2016 - 08:18 Pro Licensee

So what is this?

creation error : No provisioning record for client XX.XX.XX.XX, owner XXXXX and user servicestest found

Sorry for all the issues you're seeing there Steffan. That's lots of good troubleshooting you have going on there :-)

It sounds like Jamie may need to comment on some of those issues, but just to get one additional information for him --

On the host that's running the database where you received the " MySQL database failed" error in your initial post, what MySQL or MariaDB version is that using?

Steffan's picture
Submitted by Steffan on Fri, 09/16/2016 - 10:16 Pro Licensee

If I had a clue what the error meant without digging in source code, I'd get to it faster. :-)

[root@mysql-1 ~]# mysql --version mysql Ver 14.14 Distrib 5.7.15, for Linux (x86_64) using EditLine wrapper

Ah, this may be one of those issues we say with MySQL 5.7.x changing the way passwords are stored.

I should be easy to fix though - would you like a patch?

Steffan's picture
Submitted by Steffan on Fri, 09/16/2016 - 22:23 Pro Licensee

As stated above, absolutely and everytime. You know I'll test it!

Steffan's picture
Submitted by Steffan on Sat, 09/17/2016 - 00:13 Pro Licensee

You wanna patch and test on the server or me to do it? Either way works. Let me know and I'll provide the credentials if needed.

Ok, I'm working on a fix for this issue now. Is your Cloudmin master running on CentOS?

Steffan's picture
Submitted by Steffan on Sat, 09/17/2016 - 13:59 Pro Licensee

CentOS 6 same credentials in my private Consultation thread you're on.

Ok, I've emailed you an updated Cloudmin Services RPM package for installation on your master system. Let me know how it goes..

Steffan's picture
Submitted by Steffan on Sat, 09/17/2016 - 17:31 Pro Licensee

On the client I tried again to add a DB to an existing site/user and get the following:

Creating MySQL database servicestest on services host .. .. creation error : No provisioning record for client X.X.X.X, owner joedirt and user testingaccount found Updating Webmin user .. .. done Re-loading Webmin .. .. done
Steffan's picture
Submitted by Steffan on Sun, 09/18/2016 - 00:33 Pro Licensee

I disabled and re-enabled the feature on a virtual server. It then worked. I deleted that virtual server and tried to enable it on another existing virtual server and got this error:

Failed to create virtual server : DBI connect failed : Access denied for user 'o_joedirt'@'x.x.x.x' to database 'mysql'

I didn't know if this was because this was because the virtual server was there before I enabled CM Services. I created another virtual server as a test and received the same error.

Failed to create virtual server : DBI connect failed : Access denied for user 'o_joedirt'@'x.x.x.x' to database 'mysql'
Steffan's picture
Submitted by Steffan on Sun, 09/18/2016 - 00:59 Pro Licensee

Oh, forgot to mention, the o_joedirt (CM Services user) is still there in MySQL after I deleted that particular virtual server if that matters. It did not delete the old user (the virtual server user) it created when I deleted the virtual server.

Steffan's picture
Submitted by Steffan on Mon, 09/19/2016 - 13:34 Pro Licensee

Any progress on this or anything I can do? My client has asked for MySQL for a WP install but I can't enable the feature even to manually just do it.

Sorry, this one slipped my mind.

Do you mind if I try enabling and disabling MySQL on the problem domain? Does it have any databases that need to be preserved?

Steffan's picture
Submitted by Steffan on Tue, 09/20/2016 - 19:55 Pro Licensee

Please. By all means proceed. You have all the credentials in your email.

Steffan's picture
Submitted by Steffan on Tue, 09/20/2016 - 21:22 Pro Licensee

The dev site as you'll see has no DBs to worry about.

I think I see the issue ... was this domain created before enabling Cloudmin Services on the Virtualmin system?

If so, can delete the domain and re-create it?

Steffan's picture
Submitted by Steffan on Wed, 09/21/2016 - 08:23 Pro Licensee

Yes, as I said earlier on, the site was there before CM Services was enabled. I tried however to create a new virtual server and then enable MySQL only to get those error messages above. So, for testing, I tried enabling MySQL on an existing and new virtual server and it fails every time.

Someone changed something because to see if you made any progress I tried to create a new virtual server and got this:

Failed to create virtual server : DBI connect failed : Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

If it's OK, I'd like to create a new test domain myself and see what's going wrong?

Steffan's picture
Submitted by Steffan on Fri, 09/23/2016 - 08:20 Pro Licensee

Do what you need to do with a new or the test domain there. I need to get this fixed.

Steffan's picture
Submitted by Steffan on Fri, 09/23/2016 - 10:24 Pro Licensee

While you're looking, why do I see this in CM Service through Virtualmin?

Checking Cloudmin services limits .. .. your limits are : 99999 DNS zones, 0 DNS slave zones, 9999 MySQL logins (1 used), 9999 MySQL databases, 0 Virus clients, 0 Spam clients

This particular Virtualmin is only allowed MySQL so why show that it can have DNS zones?

Ok, give it a try now (with a new domain) - I fixed a couple more bugs.

Steffan's picture
Submitted by Steffan on Sat, 09/24/2016 - 00:43 Pro Licensee

Will do. Any chance this can be used to migrate as well? My main hosting server has a bunch of domains on it and I want to move them to CM Services.

Steffan's picture
Submitted by Steffan on Sat, 09/24/2016 - 13:39 Pro Licensee

So far, all tests on existing work fine. I noticed that when you disable MySQL on a virtual server, it does not offer you the ability to preserve dbs but if you delete the virtual server, it does offer you that option.

Questions -

  1. Any chance CM Services can migrate items when activated on existing virtual servers?
  2. Were the bugs on the CM or VM side?
  3. What about Virtualmin showing the wrong messages when enabling CM Services? (showing DNS when it's not allowed for the Virtualmin/VM)
  4. Why are the usernames created as o_username ? Is that preface o_ configurable somewhere?

Thank you!!!

Joe's picture
Submitted by Joe on Sun, 09/25/2016 - 14:42 Pro Licensee

On the migration question, we probably want to start a new issue, as it may be something me or Eric can help with, as well; and it's a whole new and unrelated set of conversations to have, depending on what kind of migration it is--e.g. is it from one Virtualmin system to a new one, or is it migrating a locally hosted database to the Cloudmin Services host, or it is a non-Virtualmin domain being brought into Virtualmin+Cloudmin Services, etc. All of those are possible, but the process will be different in all cases.

Steffan's picture
Submitted by Steffan on Sun, 09/25/2016 - 14:57 Pro Licensee

Ok, I'll split that off. Then all that's remaining:

  1. Were the bugs on the CM or VM side? -- I am setting up a template VM and need to know if there's something additional I have to do or if it's all on the CM Master, I'll be ok for now.
  2. What about Virtualmin showing the wrong messages when enabling CM Services? (showing DNS when it's not allowed for the Virtualmin/VM) -- should this be on it's own thread?
  3. Why are the usernames created as o_username ? Is that preface o_ configurable somewhere?
  1. They were all in Cloudmin - the main cause was the change in the MySQL password format in recent releases.

  2. That message is pretty harmless - it just means that the user you're connecting to Cloudmin as could create up to 9999 DNS domains, even though the particular Virtualmin system isn't making use of that functionality.

  3. The o_ prefix is fixed, and is used to distinguish domain owners from VM owners.

Steffan's picture
Submitted by Steffan on Wed, 09/28/2016 - 21:56 Pro Licensee

Is ok to install wbm-cloudmin-services 1.6-1? Will it overwrite your changes?

It's safe to install, as it incorporates all my fixes - I released it so that other users could benefit from them.

NiltonCastillo's picture
Submitted by NiltonCastillo on Tue, 02/14/2017 - 06:38

I was able to bypass the problem by editing /etc/my.cnf and remove STRICT_TRANS_TABLES from this line:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

resulting: sql_mode=NO_ENGINE_SUBSTITUTION

software: Centos 7 installation with mysql-community-server-5.6.35-2.el7.