${PREFIX} returning whole domain name for database names

17 posts / 0 new
Last post
#1 Sun, 06/18/2017 - 05:24
adelphia
adelphia's picture

${PREFIX} returning whole domain name for database names

I am trying to setup my servers and I want to use the following as my template.

domain.co.uk as domain name with a MySQL database named domain (without the .co.ok). I also want to set it to utf8_general_ci but I can't fathom out how to do it.

Whenever I create a new site, the domain is created correctly but the database is created using the full domain name with a substitution of _ for . and is always created in latin_swedish.

Can someone please tell me which settings I need to change to ensure the database just uses the ${PREFIX} and not the full name and where I set the default schema to utf8_general_ci.

Sun, 06/18/2017 - 11:03
ksihota

Interesting. I'm running Centos7 and mine is using the default settings under Server Templates and the database name using ${PREFIX} is just the domain name without the .com etc.

Default database name : ${PREFIX}
Prefix for additional databases : None
Allowed MySQL client hosts: None
Create database as well as login?: Yes
Keep MySQL and administration usernames in sync?: Yes
MySQL password for new domains: Sync with administration login
Set group ownership of MySQL database files?: Yes
Default database character set : <MySQL default>
Default database collation order: <MySQL default>
Maximum connections for domain owners: Unlimited
Maximum connections for users: Unlimited


You can change the Default database character set to whatever you want or you could always change the MySQL default settings I suppose.

Sun, 06/18/2017 - 15:24 (Reply to #2)
adelphia
adelphia's picture

I'll try those settings and see what I get - I forgot that's where to set the MySQL character set as I left it as default. No idea why it chose latin_swedish though...

Chris: Adelphia Interactive
Desktop: Windows 10 Pro x64
Server: Ubuntu 16.0.4.2LTS
Webmin/Virtualmin: Latest (daily updates)

Sun, 06/18/2017 - 15:37 (Reply to #3)
ksihota

According to Mariadb.com (I use mariadb) the default character set is latin1, and the default collation is latin1_swedish_ci
I don't believe I have ever had to change this for any of my db's but mine are pretty basic.

Sun, 06/18/2017 - 16:56
adelphia
adelphia's picture

It depends if you need data to be read back into a web page and displayed. The db character set must be the same as the web page character set or you get weird results for things like the £ sign.

I've just noticed, I don't have

MySQL password for new domains: Sync with administration login

...I'm on VM version 5.99

Chris: Adelphia Interactive
Desktop: Windows 10 Pro x64
Server: Ubuntu 16.0.4.2LTS
Webmin/Virtualmin: Latest (daily updates)

Sun, 06/18/2017 - 22:47 (Reply to #5)
ksihota

This is my setup:

Operating system CentOS Linux 7.3.1611
Webmin version 1.844
Usermin version 1.711
Virtualmin version 5.99
Theme version Authentic Theme 18.49-8
Firewall version ConfigServer Security & Firewall 10.13  
Time on system Sunday, June 18, 2017 8:46 PM
Kernel and CPU Linux 4.9.15-x86_64-linode81 on x86_64
Mon, 06/19/2017 - 06:29
adelphia
adelphia's picture

I'm just recreating one of my servers to see if I've figured out the workaround. I think I have to use a manual creation of the database name to have it as I want during the creation phase which is not too much of a biggie (if it works). Are you using the pro version BTW? I'm still using the free one.

Chris: Adelphia Interactive
Desktop: Windows 10 Pro x64
Server: Ubuntu 16.0.4.2LTS
Webmin/Virtualmin: Latest (daily updates)

Mon, 06/19/2017 - 10:23
ksihota

I am only using the free version as my accounts are personal or free, donated hosting. I moved from WHM/cPanel when I downgraded from a full hosted server to a cheap VPS. I am just learning how to use Virtualmin and I find it quite awesome.
I used WHM/cPanel for quite some time as it was always included in my hosting and was simple to use. Webmin/Virtualmin is a little different but overall I think I like it better although probably more difficult for an end user with no knowledge.
I haven't tried the paid version as it is an additional cost that I can't justify for my purposes.

Mon, 06/19/2017 - 16:37
adelphia
adelphia's picture

OK. You're basically the same as me then as I downgraded from a paid server which used Plesk and I tried to replicate it on a local server but Plesk was just too buggy with Let's Encrypt and they refused to listen to numerous requests by its paying customers so I dumped them for this. I must say, this is much more comprehensive than Plesk and, although takes a little longer to get used to, has much, much more than the wed admin version for Plesk does and, obviously, it's free.

Could you just check one other setting on your configuration for me please. What setting do you have (under Virtualmin) for System Settings -> Server Template -> Virtual server connection -> Prefix for alias domain? I currently have it set to Entire domain name and this maybe what is causing this issue. Do you have it set to First part of domain by any chance?

Chris: Adelphia Interactive
Desktop: Windows 10 Pro x64
Server: Ubuntu 16.0.4.2LTS
Webmin/Virtualmin: Latest (daily updates)

Mon, 06/19/2017 - 16:53
ksihota

I tried Plesk but hated it.
Here are my settings:

Virtual server template details (Virtual server creation)

Automatically create alias domain: No
Prefix for alias domain: Entire domain name
Mon, 06/19/2017 - 17:25
adelphia
adelphia's picture

This is SO weird! The only difference I can see (unless I'm missing something obvious) is that you are using CentOS 7.3 and I'm using Ubuntu 16.04 but surely that won't make a difference as to why I get databases named the way I do?? I also notice we seem to be having a private conversation here ;)

Chris: Adelphia Interactive
Desktop: Windows 10 Pro x64
Server: Ubuntu 16.0.4.2LTS
Webmin/Virtualmin: Latest (daily updates)

Mon, 06/19/2017 - 18:30
ksihota

I really have no idea but maybe there is a difference in the domain variable between Linux distributions. I think you will probably need someone who knows what they are talking about to chime in.
I am surprised at the low level of activity on the forums here but remember that this is under the Newbie area so maybe people that know are not bothering to look here.

Mon, 06/19/2017 - 19:13
ksihota

Okay, Sorry about this but I just realized something. My databases are being created using the username not the domain name.
Now I really feel stupid.

Now I will have to run some tests to see why that is. Maybe I manually changed them. I know I manually created some but I don't think I did that for all of them.
EDIT
I think it was a case of the username and domain being the same in some cases. If it was an awkward domain name then I created a username that was similar but easier to deal with. In the case of the database it made sense to match the username in those cases and never paid any more attention to it.

Mon, 06/19/2017 - 19:07
ksihota

Just created a new account and the Default database name is the username I used to make the site even though I used Default Name and ${PREFIX} is in the Template.
You can manually enter a DB name when you create the site under Advanced

Advanced options
Contact email address: Administrator's mailbox or Other address..
Group for domain users: Automatic or Custom group name
Prefix for mail usernames: Automatic  or create your own
Default database name: Automatic   or create your own


If you are hosting you would probably want to set a Template for a prefix when other databases are created.

By default, Virtualmin will allow server owners to create additional databases with any names. However, this field can be used to enforce a prefix. A good value is ${PREFIX}_ , which will force the database name to start with the first part of the domain name.

Other substitutions available here include ${DOM} for the domain name, ${USER} for the administration user, ${UID} for Unix user ID number and ${GID} for the group ID.
Mon, 06/19/2017 - 19:18
ksihota

Here is the info on the Default Name setting:

Virtualmin allows very flexible database creation and permissions. This field allows you to specify the default name of databases created as part of domains created from this template. By default Virtualmin uses the ${PREFIX} variable, which is the first part of the domain name, and will generally also be the domain owner login name for Webmin. It may be useful to change this, in some circumstances to include a prefix that indicates the template from which it was generated, or to include additonal information about the database. It is possible to combine variables and any characters that are allowed in a PostgreSQL and/or MySQL database (depending on which databases are available).

Other substitutions available here include ${DOM} for the domain name, ${USER} for the administration user, ${UID} for Unix user ID number and ${GID} for the group ID.
Mon, 06/19/2017 - 19:27
ksihota

I changed my template to ${DOM} and I got the same result as you
sub_domain_com for the database name
When I use ${PREFIX} I get the username as the database name.

Tue, 06/20/2017 - 06:06 (Reply to #16)
adelphia
adelphia's picture

Thanks. That would explain it then. I use the username too and had the database as default. What I'll do is the manual database name as the admin mail account will never exists (or be forwarded to my actual admin account). At lest we've got to the bottom of this now. Thanks for all the help and info.

Chris

Chris: Adelphia Interactive
Desktop: Windows 10 Pro x64
Server: Ubuntu 16.0.4.2LTS
Webmin/Virtualmin: Latest (daily updates)

Topic locked