utf8mb4 - ready or not ?

hi there,

I recently set up a perfect server with debian 9 and ISPConfig ; all good !

I would like to host a moodle website on one of my vhost.
On install, moodle test various parameters, and strongly advise to have full unicode support.
The moodle help then suggest you to edit my.cf in that way

Code:

[client]
default-character-set = utf8mb4

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

On the principle, I'm happy with that, but I have few questions :
- why this configuration isn't it recommended in the perfect server tutorial ? (what are the flaws or downsides)
would you recomend to set it up server wide
if yes, should it be specified before creating any db otherwise they'll be brocken ?
if no, can it be set for a given vhost only ? and can it be done from ISPConfig ?

IMPORTANTLY, I've also read here that switching to this setting has broken someone email server, and it seem it's the same for me, not 100% sure thought.
is there any walkaround now ? or should this setting be used only for a given vhost ?
or should it be set before installing the mail server ? idealy before instally anathing else ?

Any light on this would be welcomed

Thank's

 

utf8mb4 and utf8mb4_unicode_ci are default in mariadb in Debian 9 anyway. Besides the fine tuning for a mysql server needs to be done when it runs. Nothing speaks against Barracuda and large_prefix for innodb. Those changes can also be done after table exists. But those settings are always for the whole database server and cannot be set per vhost.
Besides making those changes doesn't change existing tables. So I doubt that this breaks current ISPConfig installations. The post you are referring to is over 1 year old.

 

hi again !

ok, well, then, the "mess" is ISPConfig related ==> when I create a new table in ISPConfig, if I don't set any setting for character encoding, the db are in iso-something, if I select utf8 they are in utf8_general_ci, not utf8mb4_general_ci

Ok that's what I understood at first, and ISPConfig and my vhosts and websites using db are all up still.
but I kind of freaked out because of this old post ;-)

thank's

 

Check in phpMyAdmin, you will see the database default and the charset used for the different databases. I have no trouble with ISPConfig and utf8mb4...

 

Ok, I don't have this issue indeed, however I had to use the mysql root account to install moodle as it refused to use the dedicated mysql user I created despite it had grant rights on the db, bot as localhost and %

yes, ok that was my guess as the only change we did for the perfect server install was securing mysql

ok
In PhpMyAdmin, I can see that the type of some tables is MyISAM and while others are InnoDB. I beleive this is consequent to the change of settings I did.

Would it be possible that some CMS like Moodle requires innodb_file_format = Barracuda while other won't work with Baracuda and requires Antelope ?
In other word are we in a logic where "who can do more can do less" or is there incompatibilities/inconsistencies ?
(beyond potential issu to import Antelope db from one server to Baracuda db on an other server)

Thank's

 

Barracuda is downward compatible with Antilope. Besides some CMS or extensions specify an explicit table format. So you might even see MySQL or other formats than utfmb4 in the future...

 

yes of course, I've read some stuff here and here however it's has to say what is the best configuration to set up a server that is "one fits all" or as close as it can be

For instance,
moodle gives precise direction, and the tables it creates in the db I provided it with are utf8mb4_unicode_ci and InnoDB
wordpress ? not really but a fresh install with my server set-up as in post 1, the tables are utf8mb4_unicode_ci and InnoDB
spip ? well not much neither but a fresh install with my server set-up as in post 1, the tables are utf8_general_ci and MyISAM

digging further, I do an sql querry to SHOW TABLE STATUS and it appears that:
wordpress tables are "compact" which means, according to mysql doc above, use Antelope
spip tables are mostyl "dynamic" and sometimes "fixed" which means that they use Baracuda

This said it seems that each cms creates its tables the way it likes, it's obviously right.

The question is should we or why would we care about the setting I set up as mentioned in the first post ?
Do the the db themselves have a format ? I couldn't find wether a db has a statut similar to tables ?
If yes, don't cms care about it ? only certains ?
If not, why did moodle complained that my mysql setting wasn't right, namely Baracuda ? Couldn't it create its own tables its own way ?

One key interest, but also issue, of using ISPConfig is to have an UI to administrate your vhost, db, users, and +.I may not use ISPConfig to create mysql user or db, and do it directly with PhpMyAdmin, but it's sound counter logical.

I obviously reckon that ISPConfig is extremely useful, however I faced some crack, for instance
-if you create an user and a db in ISPConfig an grant right to this user, it do have every right except "grant all" and you have to do it manually
-you end up having one user in ISPConfig but two in PhpMyAdmin, one for localhost and one for %, is it mandatory ?
- also if you rename a database in ISPConfig it won't be renamed in phpmyadmin ?!

In a way the question is how to deal with this variety of cms db requirement in ISPConfig?
And again if "the one who can do more, can do less" applies, what mysql server settings are to be recomended ,

cheers

ps: I may go to far in trying to understand what happens behind the scene,
just click next, until install success, but if I need to change some parameters to install a cms or an other, I'll probably bump in a situation where two setting are inconsistent

 

A short answer: every CMS does what the developer believe is right. However if you didn't enable Baracuda, than moodle cannot use it, thus it complained. Besides you do not need to deal with it in ISPConfig.
Regarding the two users, that is your setup of ISPConfig; I only get one. You can select per database if it is accessible remotely or not.

 

haha,
that's kind of a very generic answer... to a very generic question
ok i'll basically let the setting I set, let its cms do howerver it want's, and if I bump into a warning message, I'll see case by case.

Thank's

I knew the % was for remote access, but I don't really get why should there be two different users

 

localhost is used for connection through pipe and/or socket. % is used for connection from any host through TCP.