Handle charset that are declared per column

[bschmalhofer]

Having the charset utf8 declared on the individual columns is an interesting case.

First, let's sanity check that charset utf8 vs. utf8mb4 is really the problem. The problematic emoji is indeed encoded in four bytes:

$ uni -8 😊
😊 - U+1F60A - F0 9F 98 8A - SMILING FACE WITH SMILING EYES

So, this theory is confirmed.

I assume that you used the streamlined migration method for migrating the data from OTRS to OTOBO:
https://doc.otobo.org/manual/installation/10.0/en/content/migration-from-otrs-6.html#optional-step-streamlined-migration-of-the-database .
Checking the script scripts/backup.pl, I found that there is an oversight. The charset on the tables is adapted and the COLLATE on columns is removed in the subroutine MySQLBackupForMigrateFromOTRS(), see https://github.com/RotherOSS/otobo/blob/rel-10_1/scripts/backup.pl#L578. But the CHARACTER SET utf8 is not removed. This oversight seems to be the root cause of the problem.

Now for the fix. The statement alter table article_data_mime convert to character set utf8mb4 collate utf8mb4_general_ci; is likely not enough, as the charset set of the columns have higher priority than the default charset of the table. So the relevant statement is more like:

For each column:

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
See https://mathiasbynens.be/notes/mysql-utf8mb4 .

My preferred solution would be to redo the migration. Either with a fixed script scripts/backup.pl or with using the standard migration method.

Originally posted by @bschmalhofer in #1879 (comment)

[bschmalhofer]

TODO:

• adapt scripts/backup.pl to hande the charset per column case, see https://doc.otobo.org/manual/installation/10.0/en/content/migration-from-otrs-6.html#optional-step-streamlined-migration-of-the-database

[bschmalhofer]

@dennis-dko made the change and tested the adapted SQL output Looks good. PR is merged. Closing.