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

By Damon - October 24, 2013

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

By Dave - December 12, 2013

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

By Dave - December 17, 2013

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.