MariaDB character set and collation on windows vs freeBSD

Question

I'm having some trouble tracking down why my windows development installation of MariaDB doesn't match my FreeBSD installation. Here's some outputs from my test db on freeBSD:

MariaDB [www]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | latin1             |
| character_set_connection | latin1             |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | latin1             |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | latin1_swedish_ci  |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

MariaDB [www]> SHOW FULL COLUMNS FROM items;
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id          | int(11)      | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| description | varchar(255) | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.01 sec)

MariaDB [www]> select description from items where id=15;
+------------------------+
| description            |
+------------------------+
| 15 kΩ  1/8W 1%  (smd) |
+------------------------+
1 row in set (0.00 sec)

And here's the equivalent on windows:

MariaDB [www]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | latin1             |
| character_set_connection | latin1             |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | latin1             |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | latin1_swedish_ci  |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+
10 rows in set (0.01 sec)
MariaDB [www]> SHOW FULL COLUMNS FROM items;
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id          | int(11)      | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| description | varchar(255) | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.01 sec)

MariaDB [www]> select description from items where id=15;
+------------------------+
| description            |
+------------------------+
| 15 kΩ  1/8W 1%  (smd) |
+------------------------+
1 row in set (0.00 sec)

So my question is, what am i missing in terms of settings comparisons that could be the source of the mangled character (Ω) on windows and what does it need to be changed to in order to get the same characters that are on my FreeBSD server. FreeBSD installation is slightly newer 10.1.23-MariaDB FreeBSD Ports vs 10.1.19-MariaDB mariadb.org binary distribution.

Answer 1

Problem #1: Use utf8 (or utf8mb4) throughout. latin1 has no Ω. You were lucky to see it at all on either system. See "Best practice" in Trouble with utf8 characters; what I see is not what I stored

Problem #2: On Windows, cmd does not necessarily default to supporting UTF-8. The command "chcp" controls the "code page". chcp 65001 provides UTF-8, but it needs a special charset installed, too. To set the font in the console window: Right-click on the title of the window → Properties → Font → pick Lucida Console .

Check: To see if the data is stored correctly (after changing to utf8mb4 throughout), SELECT HEX(...) ... to see if you get CEA9 for the Omega. If you get C38EC2A9, you have "double encoding". Other Greek letters will be CExx or CFxx when encoded in UTF-8.