utf8mb4 - ready or not ?

Discussion in 'Installation/Configuration' started by jbonlinea, Sep 26, 2017.

  1. jbonlinea

    jbonlinea Member

    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
    default-character-set = utf8mb4
    innodb_file_format = Barracuda
    innodb_file_per_table = 1
    character-set-client-handshake = FALSE
    character-set-server = utf8mb4
    collation-server = utf8mb4_unicode_ci
    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

  2. HSorgYves

    HSorgYves Active Member HowtoForge Supporter

    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.
  3. jbonlinea

    jbonlinea Member

    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 ;-)

  4. HSorgYves

    HSorgYves Active Member HowtoForge Supporter

    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...
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    You misinterpreted that thread. This guy broke his setup by disabling name resolving in MySQL so that all logins for localhost failed as localhost could not be resolved. ISPConfig does not do any special charsets configuration in MySQL, it keeps the standard settings that the Linux distribution is using. I don't see any problems to change them if you want to use other settings.
  6. jbonlinea

    jbonlinea Member

    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

    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)

  7. HSorgYves

    HSorgYves Active Member HowtoForge Supporter

    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...
  8. jbonlinea

    jbonlinea Member

    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 ,


    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
  9. HSorgYves

    HSorgYves Active Member HowtoForge Supporter

    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.
  10. jbonlinea

    jbonlinea Member

    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.


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

    HSorgYves Active Member HowtoForge Supporter

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

Share This Page