Email Inserts failing - new installation

[Land]

I have just set up Sugar and I'm testing email.
I went to the group email and clicked on check mail; Sugar retreived a test email successfully from our POP3 server but failed to post it to the database. The following error appeared:

Query Failed:INSERT into emails set id='1e1e7590-f685-5faf-2aa1-4658a1da114d', date_entered='2007-05-26 21:08:41', date_modified='2007-05-26 21:08:41', assigned_user_id='4d10d3fb-903a-b827-0b52-46589eb5b7c4', modified_user_id='1', created_by='1', name='test 1 for SUgar', date_start='2007-05-26', time_start='20:59:00', description='Sugar 1\r\n\r\n_________________________________________ ________________________\r\nPC Magazine?s 2007 editors? choice for best Web mail?award-winning Windows \r\nLive Hotmail. \r\nhttp://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_pcmag_0507\r\n \r\n', description_html=null, from_addr='xxxx@hotmail.com ', from_name='XXX XXXX ', to_addrs='support@mydomain.com', cc_addrs=null, bcc_addrs=null, type='inbound', status='unread', message_id='support@www.mydomain.com', reply_to_name='XXX XXXX', intent='pick', mailbox_id='5b38c1dc-b143-af85-df52-46589ee5bec5', raw_source=null, deleted='0'::MySQL error 1366: Incorrect string value: '\x92s 200...' for column 'description' at row 1Error in my_thread_global_end(): 1 threads didn't exit

NOTE: I have removed personal email name and domain information above.

Update 5/26/07 5:14pm PDT: I cleared the above messge from my ISP's inbox (Sugar did not remove it) and tested sending in messages from other accounts. Those emails are arriving and posting to MySQL OK. I sent another email from hotmail.com and the same problem occured. So this problem seems to be limited to mail from hotmail.com.

Any thoughts?

Sugar Version (4.5.1d)
Sugar Edition (SugarSuite Opensource)
Operating System (Windows XP SP2)
PHP Version (5.2.2)
Database (MySQL 5.0.41)
Web Server (IIS 5.1)

[francisgg]

I have had this problem for a long time. I'm still using 4.5.0h so I was hoping 4.5.1d would solve this issue, I guess I'll wait before I upgrade.

Here is more info for the developers.

05/15/07 18:48:42,157 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE8free+...' for column 'raw_source' at row 1
05/18/07 11:50:35,558 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE8free+...' for column 'raw_source' at row 1
05/20/07 20:10:43,846 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:11:13,531 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:11:56,532 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:13:31,951 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:14:48,875 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:21:21,018 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:27:29,786 [1336] FATAL SugarCRM - unable to rename file in cache/upload/
05/20/07 20:27:29,786 [1336] FATAL SugarCRM - unable to rename file in cache/upload/
05/20/07 20:27:39,545 [1336] FATAL SugarCRM - unable to rename file in cache/upload/
05/20/07 20:27:39,545 [1336] FATAL SugarCRM - unable to rename file in cache/upload/
05/20/07 20:29:26,940 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:30:10,050 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:30:57,214 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:33:42,394 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:39:54,620 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:46:23,151 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:49:09,325 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:50:29,712 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:50:42,038 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:52:51,689 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:53:53,724 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:54:39,148 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/20/07 20:54:44,746 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE9crit ...' for column 'description' at row 1
05/23/07 12:47:40,903 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xF1ez sa...' for column 'description' at row 1
05/23/07 13:43:27,759 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xF1ez sa...' for column 'description' at row 1
05/23/07 13:43:55,455 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xF1ez sa...' for column 'description' at row 1
05/23/07 13:44:48,050 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xF1ez sa...' for column 'description' at row 1
05/23/07 23:23:56,816 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\x97with ...' for column 'description_html' at row 1
05/23/07 23:26:31,955 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\x97with ...' for column 'description_html' at row 1
05/24/07 12:35:10,497 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xF1ez\x0D\x0Ac...' for column 'description' at row 1
05/24/07 12:36:08,973 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xF1ez\x0D\x0Ac...' for column 'description' at row 1
05/24/07 13:02:03,650 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\xE8free+...' for column 'raw_source' at row 1
05/24/07 23:41:46,235 [1336] FATAL SugarCRM - MySQL error 1366: Incorrect string value: '\x92ll lo...' for column 'description_html' at row 1

Sugar Version (4.5.0h)
Sugar Edition (SugarSuite Opensource)
Operating System (Windows Server 2003)
PHP Version (5.1.4)
Database (MySQL 5.0.41)
Web Server (Apache 2.0.59)

[Land]

I wonder if it has someting to do with apostrophe's turning into question marks? Perhaps MySQL is blowing up when there is a "?" or "'" in the string?

In my example above, the SQL error is in the description field. The error message is saying the data that's bad is \x92s 200...

The first occurance of "s 200" in the description field is whre the data has '"\nPC Magazine?s 2007"

That probably should be "\n PC Magazine's 2007". The apostrophe has been changed to a question mark.

\x92 in Hex is 146 in decimal. ASCII character 146 is ' or apostrophe.

So the apostrophe is either being translated wrongly or MySQL is seeing it as an open or close of a string literal and blowing up. Informix used to do the same thing when $ was embedded in a string. One would have to parse the string and turn any $ into \$ or $$. So whoever wrote the routine to insert mail into MySQL (or whoever wants to own this issue) probably needs to figure a way to protect apostrophe's with \' or '' in order for a string that contains one to post.

In fact if you look at the INSERT statement above. The data for the description field is surrounded by single quotes. So the fact that there is a single quote (apostrophe) in the data is probably terminating the string prematurely.

description='Sugar 1\r\n\r\n_________________________________________ ________________________\r\nPC Magazine's 2007 editors'

As an alternative, if the strings are surrounded by double quotes it might be OK (unless there is a double quote in the data, of course). So again, the best way to prevent this sort of problem is to pre-parse the string in Sugar, protect embedded single quotes by turning them in double-single quotes (''), preceding them with a backslash \' (which works in C and C++, I am not a PHP programmer), or use an ASCII function to append the quote into the string ASC(146).

That's my best guess at the cause and a possible solution anyway.

[kbrill]

Update 5/26/07 5:14pm PDT: I cleared the above messge from my ISP's inbox (Sugar did not remove it) and tested sending in messages from other accounts. Those emails are arriving and posting to MySQL OK. I sent another email from hotmail.com and the same problem occured. So this problem seems to be limited to mail from hotmail.com.

Any thoughts?

Sugar Version (4.5.1d)
Sugar Edition (SugarSuite Opensource)
Operating System (Windows XP SP2)
PHP Version (5.2.2)
Database (MySQL 5.0.41)
Web Server (IIS 5.1)

Can you send me the email in question? I'd like to see it's raw code. Just send it to kbrill@sugarcrm.com.

[Land]

Kenneth,

I just sent the original test message and a followp explaining who I am. Both are coming to you from my hotmail account. Thanks for looking into this issue.

[milescook]

I've installed MySQL 5 myself and was getting this error.

If you run the instance MySQL configuration wizard and reconfigure, when you get the option to uise strict settings turn this off, and it may work - it did on mine.

WARNING: I lost 2 years of sugar data due to corrupted a database after running the configuration wizard on MySQL 4.1, so stop the service, and use phpMyAdmin or something top export all your sugar data into an sql script you can restore if need be!!!!