MySQL configuration question

jamesinc

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.

vrijvlinder

Maybe reading something about mysql would help

http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

jamesinc

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.

Bleistivt

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

jamesinc

@Bleistivt, thanks very much for the clear explanation!

phreak

@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

Bleistivt

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

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';";

dangerdonkey

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 !

dangerdonkey

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

whu606

Well, at least it was to say something nice!