Issue with displaying UTF-8 data

[GhaziTriki]

I created a new user with the family name : Şentürk and I got ?entürk displayed.

[marcelfolaron]

Hey, thanks for submitting.

There is currently a problem with mixed charsets. The user table is still set to latin. This will be a little more involved to fix. In the meantime you can alter the table to utf-8 using:

ALTER TABLE zp_user CONVERT TO CHARACTER SET utf8;

I'll work on updating the tables in the next release.

[GhaziTriki]

Yes not only this table but some other tables too.

[GhaziTriki]

ALTER TABLE leantime.zp_wiki_articles COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_submodulerights COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_canvas COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_wiki_categories COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_tickethistory COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_gcallinks COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_message COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_note COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_timesheets COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_roles COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_projects COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_modulerights COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_wiki_comments COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_punch_clock COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_clients COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_account COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_sprints COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_lead COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_user COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_settings COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_comment COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_stats COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_tickets COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_canvas_items COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_dashboard_widgets COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_file COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_action_tabs COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_relationuserproject COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_calendar COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_read COLLATE utf8mb4_unicode_ci;
ALTER TABLE leantime.zp_wiki COLLATE utf8mb4_unicode_ci;<<

ALTER TABLE `zp_wiki_articles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_submodulerights` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_canvas` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_wiki_categories` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_tickethistory` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_gcallinks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_message` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_note` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_timesheets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_roles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_projects` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_modulerights` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_wiki_comments` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_punch_clock` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_clients` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_account` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_sprints` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_lead` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_settings` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_comment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_tickets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_canvas_items` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_dashboard_widgets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_file` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_action_tabs` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_relationuserproject` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_calendar` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_read` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_wiki` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

[seonghobae]

SQL Error (1071): Specified key was too long; max key length is 767 bytes

This error message occurs when I try to change the username column at the zp_user table.

[marcelfolaron]

The error message is above is caused by an index and a column that are too long for utf8mb4. We can drop the index (not needed anyways) and reduce the size of the column. The column to modify is the email address, so the requirement is that emails are below 175 characters The script to manually update is here:

ALTER TABLE `zp_wiki_articles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_submodulerights` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_canvas` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_wiki_categories` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_tickethistory` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_gcallinks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_message` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_note` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `zp_timesheets` DROP INDEX `Unique`;
ALTER TABLE `zp_timesheets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `zp_roles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_projects` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_modulerights` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_wiki_comments` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_punch_clock` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_clients` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_account` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_sprints` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_lead` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `zp_user` MODIFY username VARCHAR(175);
ALTER TABLE `zp_user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `zp_settings` MODIFY `key` VARCHAR(175);
ALTER TABLE `zp_settings` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_comment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_tickets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_canvas_items` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_dashboard_widgets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_file` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_action_tabs` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_relationuserproject` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_calendar` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_read` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `zp_wiki` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

[marcelfolaron]

Fixed in V2.0.4