Suitecrm: Support UTF8MB4 charset and collation in MySQL

[B-Prod]

Issue

It is not possible to create a custom field in quote.

Expected Behavior

When I create a custom field, I expect that it is created when I click on the "save" button.

Actual Behavior

Currently when I click the "save" button, I have an error window without any message, only the title "An error has occurred".

Possible Fix

With debug, it appears that the error comes from the missing table aos_quotes_cstm. This table was not there while trying to save the field.
If using the "repair" system, the table is no more created as there is no custom field at this time and the check against $focus->hasCustomFields() return FALSE.

What I suggest would be to add a pre action (so pre_SaveField method) that ensures the _cstm table exists and creates it if necessary.

Steps to Reproduce

1. Perform a fresh install without Lead and Marketing packages
2. Create a new quote custom field
3. Save

[B-Prod]

After more investigation, it appears the method DynamicField::addFieldObject() create the table if not exists.

So I look further and the issue is caused by a wrong query:
"CREATE TABLE aos_quotes_cstm (id_c char(36) NOT NULL , PRIMARY KEY (id_c)) CHARACTER SET utf8 COLLATE utf8mb4_general_ci"

This is due to a invalid Charset, as utf8 is not compatible with utf8mb4_general_ci

So the issue resides in the MysqlManager::createTableSQLParams() method that use a fixed charset. It should be possible to have an option to define the charset.

[B-Prod]

I changed the issue title, as the real issue is caused by a missing support of UTF8MB4...

Here is a patch that fixes the issue for MySQL and MySQLi managers.

Once this patch was applied, I was able to save the field as expected.

suitecrm-utf8mb4-mysql-support-3192.patch.zip

[chris001]

@B-Prod Have you ever made a Pull Request ?

[chris001]

Good patch. Is the utf8mb4 because your users save text with emoticons?

[B-Prod]

Pull request: yes, but never on Github and I use to do this on branch in the same repository. How should I process with this repo? What is the workflow (fork? create a new branch? other...)?

UTF8MB4: a client need to store some data in asian languages so I want to avoid issues when performing a search into MySQL.

[chris001]

To do pull request:

1. Click Code.
2. Click Branch, change to the "Hotfix" branch.
3. For each file you want to edit, go to the page with the source code.
4. Click Pencil icon at top right of page.
5. Edit the source code file.
6. At the bottom of page enter comment line about the edit, and extra details.
7. Click Save.
8. Go back to 3 until you have edited all files.
9. On Code page click New Pull Request.
10. Fill in form about the pull request.
11. Click Create.

[B-Prod]

I created a new branch (feature/utf8mb4-3192) with the patch applied and a little improvement on the install_utils.php script.
Maybe we should also update the installConfig.php script to save the charset setting in the config file, but I am not sure of the better way to handle this.

But I am not authorized to push the new branch to the repository, so I cannot create a pull request.

EDIT : I just saw your new message, so I will follow the steps.

[chris001]

It's OK if you're not authorized to push the new branch. The pull request is just a request to pull the changes back into this repository. You can make a pull request, anyone can.

[B-Prod]

I tried but each time I modify one file and click on "Propose file change", I am no more able to change another file. If so, I loose the previous modifications.
How to process so I can edit the 3 files before making the pull request?

[chris001]

You can do a "Squash merge", this combines your forked repo's two commits (your two branches) into one branch. Then you do a Pull Request of your branch, to this repo's Hotfix branch.

[B-Prod]

After a long long way (I am really not a big fan of UI and the existing topics on Github are not really clear), I finished to create a valid pull request (phew!): https://github.com/salesagility/SuiteCRM/pull/3196

[chris001]

@B-Prod I agree the github web user interface isn't clear for this type of thing. They assume you have experience with the terminology and the consequences of various types of commits and merges etc. from having used the git command line a lot.

[mwilborne]

This patch allows using emojis anywhere, great job! I noticed a scheduled job failing in cron because of incoming emojis in an email/sms module, however, this patch fixed it no problem.

Notes: I also had to update one particular custom module's table/column to utf8mb4 as well. Furthermore, I did not use the standard utf8mb4 type but the utf8mb4_unicode_520_ci colum/table type that the most-current version of WordPress is also using.