Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Try Vanilla Forums Cloud product
Please upgrade here. These earlier versions are no longer being updated and have security issues.

MySQL configuration question

This discussion is related to the Native Emoji addon.

Hey,

I would like to know why set time_zone='+0:0' is part of the configuration options listed for the addon. My MySQL instance is set to slave to the OS's timezone. I don't know a whole lot about what set time_zone actually does, and can't find much in the way of definitive answers on the Internet. I was wondering if you could clear that up for me.

Comments

  • jamesincjamesinc Sydney ✭✭

    Yeah, that's the documentation I was looking at before. What I don't understand is the material impact it will have on the schema update.

    I can see three possible scenarios:

    1. It doesn't really do anything (but then why include it at all?)
    2. It does do something, but is safe to use as-is in all use cases (i.e. MySQL slave to system time, or MySQL or system not using UTC)
    3. It does do something, and is not safe to use where MySQL is slave to system time or MySQL/system are not using UTC.

    I ran it on a dev copy of my database and it seems okay, but as I said I probably don't appreciate the significance of it when updating the schema of a database, when the database isn't already configured to use UTC.

    vrijvlinder
  • Vanillas default MySQL charset is utf8, which is not actually UTF-8, because it stores 3 bytes per character at max, while 4 would be required to fully support all characters, including emoji. utf8mb4 was made to fix that.

    $Configuration['Database']['ConnectionOptions'][1002]
    

    This config value sets options for the SQL driver when creating a new database handle. I found out that I needed to change that on my vanilla instance to support native emoji.

    The default value is: "set names 'utf8'; set time_zone = '+0:0';"

    So unless you have changed this in the past, it won't change the timezone. Vanilla already sets the time to UTC on every connection.

    You can do a simple check if you need to set any of these config values. Just copy an emoji from the "Native column" on this page and try to post it to your forum:
    http://apps.timwhitlock.info/emoji/tables/unicode

    (You can see that it doesn't work here...).

    My themes: pure | minusbaseline - My plugins: CSSedit | HTMLedit | InfiniteScroll | BirthdayModule | [all] - PM me about customizations

    vrijvlinderhgtonightjamesincShadowdare
  • jamesincjamesinc Sydney ✭✭

    @Bleistivt, thanks very much for the clear explanation!

  • phreakphreak VanillaAPP - White label iOS and Android App MVP
    edited September 2016

    @Bleistivt: May i hang in here? Does this plugin help with the problem that when someone writes an EmoJi from a smartphone in a post that the post will not get cut any longer at the emojis place when saving to the database. This was an issue that i have every time on my boards. Post text gets cut where the Emoji is placed.

    Or could we consider this fixed with the following Vanilla version:
    https://github.com/vanilla/vanilla/issues/4309

  • No, this won't help with the problem, but the fix for your problem is in the plugin description :D

    Unfortunately, Vanilla doesn't support UTF-8 emoji out of the box.
    To enable native emoji support you need to add the following lines to your conf/config.php and visit yourforum.com/utility/structure

    $Configuration['Database']['CharacterEncoding'] = 'utf8mb4';
    $Configuration['Database']['ExtendedProperties']['Collate'] = 'utf8mb4_unicode_ci';
    $Configuration['Database']['ConnectionOptions'][1002] = "set names 'utf8mb4'; set time_zone = '+0:0';";
    

    My themes: pure | minusbaseline - My plugins: CSSedit | HTMLedit | InfiniteScroll | BirthdayModule | [all] - PM me about customizations

    whu606RiverShadowdare
  • I had my time zone set explicitly in the past so when enabling this plugin I used

    $Configuration['Database']['ConnectionOptions']['1002'] = 'set names \'utf8mb4\'; set time_zone = \'America/New_York\';';

    The plug in works fine. Great, even! We've been waiting for native emoji support inVanilla so this is a boon to my community. Thanks @Bleistivt !

    Bleistivt
  • ...and I'm just realizing this plug-in is almost two years old and I just necromanced the thread. whoops!

    jamesinc
  • whu606whu606 I'm not a SuperHero; I just like wearing tights... Moderator

    Well, at least it was to say something nice!

    jamesinc
Sign In or Register to comment.