[BlueOnyx:16560] Re: Feature Request MySQL

[Michael Stauber]

Hi Dirk,

> Do you see a possibility to change mysql settings within one
> site in that way that you can add up to n databases (maybe
> configurable) for one site and not only one?

I've been working on an overhaul of the GUI modules for the new GUI to
make them compatible with BlueOnyx 5209R on EL7. The one I'm currently
working on is coincidentally base-mysql.

EL7 uses MariaDB instead of MySQL. It is "mostly" compatible and most
people using it won't notice a difference. For me it makes a great deal
of differences as far as the GUI is concerned. Because I have to cover
all the eventualities in one module.

As is the GUI module not only needs to handle the differences between
different MySQL versions, but also between MySQL and MariaDB and their
respective sub-versions.

With that in mind it has a lot of similarities with the relationship
status of a teenager on Facebook:

It's complicated.

Multiple MySQL databases for Vsites? Generally: This is a good idea. In
practice: Difficult due to the way how we handle things. It's contrary
to a design limitation of the GUI and CODB and the way MySQL-support was
added.

A Vsite has a CODB object.

Every "feature" related to a Vsite has a "namespace" that resides below
that object. A Vsite object typically has these "namespaces" or
"sub-objects":

105 NAMESPACE solbackup			X
105 NAMESPACE SiteStats
105 NAMESPACE Shell
105 NAMESPACE FTPNONADMIN
105 NAMESPACE UserDefaults
105 NAMESPACE SSL
105 NAMESPACE magento			X
105 NAMESPACE CGI
105 NAMESPACE AnonFtp
105 NAMESPACE subdomains
105 NAMESPACE DNS
105 NAMESPACE Compass_webapps		X
105 NAMESPACE roundcubemail		X
105 NAMESPACE PHP
105 NAMESPACE USERWEBS
105 NAMESPACE PHPVsite
105 NAMESPACE ApacheBandwidth
105 NAMESPACE waphpMyAdmin		X
105 NAMESPACE piwik			X
105 NAMESPACE Disk
105 NAMESPACE MYSQL_Vsite
105 NAMESPACE SSI
105 NAMESPACE Java

The ones marked with an "X" are extra namespaces that were added by
third party modules.

Say the Vsite has Object ID 35. Then the features for PHP and for MySQL
of that Vsite are addressed like this:

Object 35 . PHP
Object 35 . MYSQL_Vsite

The data for Object 35's MYSQL_Vsite might look like this:

GET 35 . MYSQL_Vsite
102 DATA NAMESPACE = "MYSQL_Vsite"
102 DATA username = "vsite_hBkIJD7"
102 DATA CLASSVER = "1.0"
102 DATA pass = "4tF1nWR"
102 DATA host = "localhost"
102 DATA hidden = "1415043529"
102 DATA destroy = ""
102 DATA DB = "vsite_hBkIJD7_db"
102 DATA port = "3306"
102 DATA create = "1415043529"
102 DATA enabled = "1"

As you can see: The key "DB" (for the database name) can store the value
in form of a variable for one database.

The benefit of doing things this way are these: When a Vsite is deleted,
we "destroy" the object for the Vsite in CODB. That will then also
"destroy" all features that this Vsite has enabled and will run all
handlers required for removing those as well:

1.) Deleting all users for that Vsite
2.) Removing email aliases
3.) Removing mailing lists for that Vsite
4.) Removing the Vhost container from Apache
5.) Removing the FTP settings from proftpd.conf (if need be)
6.) Removing the MySQL database and user

And a couple of other things.

Now how can we allow multiple MySQL databases? There are a few ways and
each has its own complication. One would be to define "DB1", "DB2", DB3"
and so on in the schema for the "MYSQL_Vsite" object of Vsites. But that
would limit the maximum allowed number of MySQL databases to as many
"DBx" entries as I add beforehand. This number cannot be changed at
runtime or dynamically.

The second option is to manage the names of the MySQL databases outside
of the Vsite's MYSQL_Vsite object. The WebApp-Installer does something
like that. Doing this for Vsites is not ideal, as these settings would
not survive a CMU-Migration without a massive overhaul of CMU, too.

The third option that comes to mind is to turn the database field for
"DB" in MYSQL_Vsite into an array, into which multiple database names
can be stored.

As is it looks like this:

102 DATA DB = "vsite_hBkIJD7_db"

In the future (if this was turned into an array) it would look like this
for one database:

102 DATA DB = "&vsite_hBkIJD7_db&"

And like this for three:

102 DATA DB = "&vsite_hBkIJD7_db&vsite_FkItZ_db&vsite_kB52ot_db&"

There are some length restrictions, but as is I'm sure we could store a
fair number of database names in it without any problems. This option
would be fully compatible with CMU and - by far - the easiest to implement.

But there is a catch (as always): If I change the existing "DB" database
field from just allowing variables to now allow an array instead, then
this might possibly result in data loss. Which we want to avoid. So
there needs to be a transition that allows the existing variables in the
"DB" field to be tuned from string to array.

I'll look into it and I think the last option would be ideal, provided I
can avoid potential data loss on the transition. If need be, I will
create a new field named "DBs" that takes arrays and will let the old
"DB" field become dormant after the data from "DB" has been moved over
into the new "DBs" field.

No promises on how fast this will be implemented and when it will become
available. But it'll come.

-- 
With best regards

Michael Stauber