search

Leantime / leantime

Leantime is a goals focused project management system for non-project managers. Building with ADHD, Autism, and dyslexia in mind.
https://leantime.io
GNU Affero General Public License v3.0
4.41k stars 578 forks source link

Issue with displaying UTF-8 data #71

Closed GhaziTriki closed 4 years ago

GhaziTriki commented 4 years ago

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

marcelfolaron commented 4 years ago

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 commented 4 years ago

Yes not only this table but some other tables too.

GhaziTriki commented 4 years ago 
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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

Fixed in V2.0.4