I was able to get it to work to my liking using one of the recipes on the web page I referred toā¦
The accents/special characters were already mangled and I was willing to correct the entries which had them so as long as I was not losing anything else I was ok with correcting any mangled entryā¦
I also had a very recent backup (yesterday)ā¦
I did many tries (restoring from my backup after each).
Essentially what I ran was this:
DB="asterisk"
(
echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_unicode_ci;'
mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names \
| xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;'
) \
| mysql "$DB"
and
DB="asteriskcdrdb"
(
echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_unicode_ci;'
mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names \
| xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;'
) \
| mysql "$DB"
I modified the script I had to use the UTF-8 collate sequence documented here: [FREEPBX-8057] Wrong charset in CDR records/connection - Sangoma Issue Tracker
The first time I tried this it didnāt work, I got the following error message
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
This was caused by the table asterisk.faxpro_hook_core which is used by the āFax Configuration Professionalā module. Wanting to play it safe I restored from my backup and I then deinstalled it the module (so that it would remove the table cleanly).
I reran both scripts.
This time it completed successfullyā¦
I then reinstalled the āFax Configuration Professionalā module and disabled it (this is what I do with modules I donāt currently use but are part of the distro, I donāt normally uninstall them.
It recreated the table but now the page and id fields (which are part of the key) are no longer 255 characters long but 150ā¦ I guess with the conversion to UTF-8 the key was now too long (the max is 1000 for the type of index used on this table apparently) and has been shortenedā¦ As this table was created before I switched to FreePBX 13 it had the old lengthā¦
I then tried to enter characters which were not supported by Latin1 (ISO-8859-1)ā¦
I tried characters which needed ISO-8559-16 (or a variant of Unicode) and even Cyrillic and both were correctly handledā¦
(I have a very basic understanding of Romanian and Russianā¦)
I then fixed all the entries which had their characters mangled because of the initial upgrade to FreePBX 13, there werenāt that manyā¦
The only problem I noticed is that some screens seem not to handle the apostrophe (ie ā ) correctly but I donāt think this is related to runing this script, they probably need to escape them. The IVR screen (IIRC) did but you see the backslash it precedes the apostrophe with in some places while the time group screen seems to discard an entry that has it altogether (probably because the SQL fails to execute because of that (most likely unescaped) apostropheā¦)ā¦
Obviously this is not for everyone but as for me I am happy with the resultsā¦ The accents/special characters were already mangled to begin with so as long as I wasnāt losing anything else I was ok with correcting them afterā¦
Thank you and have a nice day!
Nick
