Converting your MySQL database to UTF8

This document describes how to convert your MySQL database from the latin1 charset to UTF8 (utf8mb4). WackoWiki requires that your database is now UTF8 and will not upgrade if your database is not.

WackoWiki applications used in multi-language mode require a on record conversion.
1. iconv('ISO-8859-1', 'UTF-8', $text)
2. iconv('ISO-8859-2', 'UTF-8', $text)
3. iconv('ISO-8859-7', 'UTF-8', $text)
4. iconv('CP1251', 'UTF-8', $text)
5. iconv('CP1257', 'UTF-8', $text)
Unicode entities also require a conversion to UTF-8.
1. $output = preg_replace_callback('/(&#[0-9]+;)/', function($m) { return mb_convert_encoding($m[1], 'UTF-8', 'HTML-ENTITIES'); }, $input);

Migrating to Unicode

database:

 ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_520_ci;

each table:

ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_520_ci;

each column:

 ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_520_ci;

Modify connection, client, and server character sets

Make sure to set the client and server character set as well.
/etc/my.cnf:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_520_ci

Check if these settings work correctly:

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

Repair and optimize all tables

Run the following MySQL queries for each table you want to repair and optimize:

REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

This can easily be done in one go using the command-line mysqlcheck utility:

 $ mysqlcheck -u root -p --auto-repair --optimize --all-databases