The downloadMail script is having trouble download a specific message (see attached).

The error is:

MySQL Error:<br/> Incorrect string value: '\xC2 \xE2\x80\x94 ...' for column 'headers' at row 1

Any thoughts?

Thank you

Attachments:

email-source.txt 5K

Hi,

I see your Support Ticket email so I will reply to that one for now and post back here after the issue is resolved.

---------------------------------------------------
Cheers,
Damon Edis
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Thank you

Thank you!

Thank you!!

Thank you!!!

By Brownleather - December 11, 2013 - edited: December 11, 2013

Hi Dave,

The download mail plugin has been running smoothly for a while, thank you for the previous fix.

We received an email today that is chocking up the parser (see attached)

The error is: Incorrect string value: '\xF0\x9F\x8D\x80Ke...' for column 'text' at row 1

I truly appreciate your help

Attachments:

uid-2431.txt 14K

Hi Brownleather, 

You are doing an amazing job of finding edge cases where the plugin fails.  Thanks for that, and sorry it's not working.

This sequence "\xF0\x9F\x8D\x80" is a 4 byte unicode UTF8 symbol for a green leaf clover:
http://www.charbase.com/1f340-unicode-four-leaf-clover

And the problem is, MySQL's version of UTF8 doesn't support these 4 byte characters until version 5.5:
http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-sets.html

So the simplest option would probably be to just remove 4-byte sequences, other options would be to require MySQL 5.5+ and switch to utf8mb4 encoding.

Questions:

- What version of MySQL do you have (listed under: Admin > General at the bottom)
- Would it work to just strip out those characters? (They are things like emoji's, smileys, symbols, etc).

Let me know, thanks!

References: 

Dave Edis - Senior Developer
interactivetools.com

Thanks for your reply.

We are running 5.5.32

So I'd like to switch to utf8mb4 encoding.

Whats the next step?

Thank you

Hi brownleather,

There's a post here on how to switch over: 
http://dba.stackexchange.com/questions/8239/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5

And here on mysql.com: 
http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html

If you download this free plugin it has an easy interface for entering MySQL commands:
http://www.interactivetools.com/add-ons/detail.php?MySQL-Console-1011

So I'd try the following steps: 

  1. Under: Admin > General: Backup the table "_incoming_mail"
  2. Check your "Table Prefix" at the bottom of the page (usually cms_)
  3. In the MySQL Console Plugin enter this (using your table prefix name):  ALTER TABLE cms__incoming_mail CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  4. Note that your table prefix ends with a _ and the _incoming_mail table starts with one.  You need two underscores.
  5. Test downloading mail and check for any problems (if you have problems, restore backup of individual _incoming_mail table

Let me know if that works for you or if you run into any issues.

Thanks!

Dave Edis - Senior Developer
interactivetools.com

Thanks.

I'll let you know how it goes.