I seem to recall that a long, long time ago MediaWiki was using UTF-8 internally but storing the data in 'latin1' fields in MySQL.

I notice that there is now the option to use either 'utf8' or 'binary' columns (via the $wgDBmysql5 setting), and the default appears to be 'binary'.[1]

- it cites MediaWiki as the reason) and stores its UTF-8 data in latin1 tables. I need to upgrade it to a more modern data infrastructure, but I'm hesitant to simply switch to 'utf8' without understanding the reasons for this initial implementation decision.

On the latest discussions, there are proposals to increase the minimum mediawiki requirements to MySQL/MariaDB 5.5 and allow binary or utf8mb4 (not utf8, 3 byte utf8), https://phabricator.wikimedia.org/T161232.

should be enough for most uses (utf8 will not allow for emojis, for example), although I am not up to date with the latest unicode standard changes and MySQL features supporting them.

On Tue, May 2, 2017 at 9:24 PM, Brian Wolff &lt;bawolff(a)gmail.com&gt; wrote: I am sorry I wasn't clear before, MySQL's utf8 IS NOT international standard generally known as UTF-8, it is a bastardization of 3-byte max UTF-8. MySQL's utf8mb4 is UTF-8: Proof: ``` mysql> use test Database changed mysql> CREATE TABLE test (a char(1) CHARSET utf8, b char(1) CHARSET utf8mb4, c binary(4)); Query OK, 0 rows affected (0.02 sec) mysql> SET NAMES utf8mb4; Query OK, 0 rows affected (0.00 sec) mysql> insert into test VALUES ('\U+1F4A9', '\U+1F4A9', '\U+1F4A9'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\xF0\x9F\x92\xA9' for column 'a' at row 1 | +---------+------+--------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> SELECT * FROM test; +------+------+------+ | a | b | c | +------+------+------+ | ? | 💩 | 💩 | -- you will need an emoji-compatible font here +------+------+------+ 1 row in set (0.00 sec) mysql> SELECT hex(a), hex(b), hex(c) FROM test; +--------+----------+----------+ | hex(a) | hex(b) | hex(c) | +--------+----------+----------+ | 3F | F09F92A9 | F09F92A9 | +--------+----------+----------+ 1 row in set (0.00 sec) ``` To avoid truncations: ``` mysql> set sql_mode='TRADITIONAL'; -- https://phabricator.wikimedia.org/T108255 Query OK, 0 rows affected (0.00 sec) mysql> insert into test VALUES ('\U+1F4A9', '\U+1F4A9', '\U+1F4A9'); ERROR 1366 (22007): Incorrect string value: '\xF0\x9F\x92\xA9' for column 'a' at row 1 ``` More info at: https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8.html vs. https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html -- Jaime Crespo <http://wikimedia.org> _______________________________________________ Wikitech-l mailing list Wikitech-l(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Hi all, I seem to recall that a long, long time ago MediaWiki was using UTF-8 internally but storing the data in 'latin1' fields in MySQL.