OroCommerce Won't Sync with Incoming Emails

Question

No matter what I do I can't get OroCommerce to sync with incoming emails. I installed OroCommerce using the docs, I optimized my mysql for utf8mb4 / unicode, I checked my doctrine dbal settings for utf8mb4, yet simply no emails will sync. At first I was trying thsi with OroCrm and I was getting errors with message consume, some reason OroCommerce doesn't give those issues... Or maybe just reinstalling every 30+ times did that.

In anycase, in one posting I found a couple years ago mentioned to try bin/console oro:cron:imap-sync. That gives me a clue, and thats why I tried optimizing my database and making all the settings correct for utf8mb4 (followed the guide @ https://doc.oroinc.com/backend/setup/system-requirements/database-optimization/ and then reinstalled OroCommerce).

Here's the current error set I'm getting via the sync command:

[myaccount@vegas crm]$ bin/console oro:cron:imap-sync
Resetting hanged email origins ...
Updated 0 row(s).
Finding an email origin ...
Found "[email protected] (vegas.myserver.net)" email origin. Id: 39.
Get folders enabled for sync...
Got 2 folder(s).
The folder "INBOX" is selected.
Previous max email UID "0"
Found 33 email(s).
The synchronization failed. Error: An exception occurred while executing 'INSERT INTO oro_email (serialized_data, created, subject, from_name, sent, importance, internaldate, is_head, message_id, multi_message_id, x_message_id, x_thread_id, refs, acceptLanguageHeader, body_synced, from_email_address_id, thread_id, email_body_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["Tjs=", "2021-04-29 03:14:09", "Your contacts in Senders Company \ud83d\udcc7", "Senders Name <[email protected]>", "2021-03-12 16:00:07", 0, "2021-03-12 16:01:25", 1, "<[email protected]>", null, "", "", null, "", null, 107, null, null]:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x93\x87' for column 'subject' at row 1
The synchronization failed. Error: An exception occurred while executing 'INSERT INTO oro_email (serialized_data, created, subject, from_name, sent, importance, internaldate, is_head, message_id, multi_message_id, x_message_id, x_thread_id, refs, acceptLanguageHeader, body_synced, from_email_address_id, thread_id, email_body_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["Tjs=", "2021-04-29 03:14:09", "Your contacts in Senders Company \ud83d\udcc7", "Senders Name <[email protected]>", "2021-03-12 16:00:07", 0, "2021-03-12 16:01:25", 1, "<[email protected]>", null, "", "", null, "", null, 107, null, null]:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x93\x87' for column 'subject' at row 1
Finding an email origin ...
Found "[email protected] (vegas.myserver.net)" email origin. Id: 39.
Exit because all origins have been synchronised.

In AbstractEmailSynchronizer.php line 622:
                                                                   
  The email synchronization failed for the following origins: 39.  
                                                               

My Server setup: CentOS Linux release 7.9.2009 (Core) mysql version: mysqld Ver 5.7.34 php 7.4.16 cpanel

My.cnf:

[mysqld]
innodb_file_per_table = 0
wait_timeout = 28800
performance-schema=0
optimizer_search_depth=0
character-set-server=utfmb4
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1


[client]
default-character-set = utf8mb4

Anyone that can help me with this I would greatly appreciate it. I've been kicking orocommerce repeatedly for the past 3 days. Sometimes I get slightly different errors, but this one seems to be pretty consistent, and I'd just love to get past the install phase.

Thanks

Answer 1

First of all, it looks like you have a typo in the Mysql configuration:

character-set-server=utfmb4
character-set-server=utf8mb4

should be replaced with

character-set-server=utf8mb4

Once it's fixed and the MySQL server is restarted, try to check whether the configuration value is applied properly by running below SQL query:

SHOW VARIABLES LIKE 'character_set_%';

If the encoding is not correct - you have to fix the configuration first.

Also, you may try to update the application configuration (config/config.yml file) with the below value, that should enforce the default encoding:

doctrine:
    dbal:
        connections:
            default:
                options:
                    # PDO::MYSQL_ATTR_INIT_COMMAND
                    1002: 'SET NAMES utf8mb4, @@SESSION.optimizer_switch="block_nested_loop=off";'

Anyway, if you created the database before changing the default character set, it is possible that the database still uses the old setting and you have to convert your database encoding, like in this example: How to convert an entire MySQL database characterset and collation to UTF-8?