@mattab opened this Issue on September 15th 2015 Member

Goal: correctly track emoji and all utf8 4-byte characters. This requires a change from our table charset to utf8mb4. See discussion in #7766

@huan086 commented on April 10th 2017

Use utf8mb4_unicode_520_ci for COLLATE for correct sorting. E.g. https://bugs.mysql.com/bug.php?id=2906 happens when utf8mb4_unicode_ci is used

@huan086 commented on April 12th 2017

Quick and dirty hack to enable emoji

In piwik\config\config.ini.php, after

adapter = "MYSQLI"

add

charset = "utf8mb4"

Run the following SQL


ALTER SCHEMA `piwik` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_access` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_archive_blob_yyyy_mm` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci; -- repeat this for all the tables, with yyyy and mm replaced
ALTER TABLE `piwik`.`piwik_archive_numeric_yyyy_mm` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci; -- repeat this for all the tables, with yyyy and mm replaced
ALTER TABLE `piwik`.`piwik_goal` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_action` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_conversion` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_conversion_item` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_link_visit_action` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_profiling` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_visit` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_logger_message` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_option` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_plugin_setting` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_report` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_segment` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_sequence` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_session` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_site` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_site_setting` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_site_url` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_user` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_user_dashboard` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_user_language` CHARACTER SET=utf8mb4, COLLATE=utf8mb4_unicode_520_ci;

ALTER TABLE `piwik`.`piwik_access` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_archive_blob_yyyy_mm` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci; -- repeat this for all the tables, with yyyy and mm replaced
ALTER TABLE `piwik`.`piwik_archive_numeric_yyyy_mm` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci; -- repeat this for all the tables, with yyyy and mm replaced
ALTER TABLE `piwik`.`piwik_goal` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_action` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_conversion` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_conversion_item` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_link_visit_action` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_profiling` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_log_visit` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_logger_message` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_option` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_plugin_setting` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_report` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_segment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_sequence` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_session` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_site` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_site_setting` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_site_url` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_user_dashboard` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `piwik`.`piwik_user_language` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
Powered by GitHub Issue Mirror