Problem using html text fields - possible corruption?

rocketman · Post by **rocketman** » Tue Jan 24, 2012 8:51 am

When trying to enter a message in an HTML plain text attribute, a user has reported getting the following error message.

Internal error. Error persisting business object BulkMail Incorrect string
value: '\xE2\x80\x8Band...' for column 'Message' at row 1

At first I thought he had been trying to copy and paste from MS Word - which always gives me nightmares - but he claims not. He may have used cut and paste in a prior email which has put some spurrius characters in the database.

Anybody got any ideas?

ACDC · Post by **ACDC** » Tue Jan 24, 2012 9:09 am

I once before had similar problem and had to do with UTF 7 as opposed to UTF 8 coding in a received email

rocketman · Post by **rocketman** » Tue Jan 24, 2012 1:22 pm

Further to my first post. It would seem that if I copy and then click right mouse button and select 'Paste as plain Text' I can at least get the formatting right, I can also save and send the message via email. HOWEVER as soon as I try to edit the text in any way - even by one character I get the message described above

Internal error. Error persisting business object BulkMail Incorrect string
value: '\xE2\x80\x8Band...' for column 'Message' at row 1

nlarson · Post by **nlarson** » Tue Jan 24, 2012 4:38 pm

Yes, I am not alone!! Sorry you have to share my pain.

For me, this annoying little gem is related to pasting bullet points and other special characters form MS Word. You can save the record once, but the database does not encode it correctly, so when you re save the record it throws an invalid char string.

I gave up and just started using this script on a nightly basis to clean it up. Not perfect, but so far so good.

UPDATE `versegoal_goals` SET Description = replace(Description,"•","-") WHERE `ID` > '0';

UPDATE `versegoal_goals` SET Description = replace(Description,"‘"," ") WHERE `ID` > '0';

UPDATE `versegoal_goals` SET Description = replace(Description,"“"," ") WHERE `ID` > '0';

Gabbitas · Post by **Gabbitas** » Sun Oct 27, 2013 10:13 am

Hi All,

I am now also getting this error crop up. Its strange because my users have been happily copying and pasting from MSWord or email for more than a year without problem and then last week the error popped up and has 3 or 4 times since! This is causing real problems for me now as its locking out BO's and preventing schedulers from loading.

The exact error I'm getting is below:

Internal error. Error persisting business object Appointment Incorrect string
value: '\xC2\x96 cou...' for column 'Description' at row 1

I have recently upgraded to MySQL 5.6 and was wondering if that might be the problem but after PM's with Rocketman I don't think that is the cause.

nlarson, where/how do you run the script that you mention above? Do you run it in a DB management tool? Is it possible to run it inside of AwareIM somewhere so that it triggers when a user enters and saves a description.

Support, is it possible for you to take a look at this and is it possible for you to fix it? Can you let me know what it might cost to get this fixed if needed?

Thanks

rocketman · Post by **rocketman** » Sun Oct 27, 2013 4:47 pm

Part of my PM with Gabbitas

I never did get to the bottom of this one - in the end I had to just warn my user against copying and pasting. At first I thought it was just copying and pasting from other documents but I've even hit the problem when moving bits (cut and paste) from within the text I'm trying to get right itself.

It's as if the act of cut/copy and paste puts spurious characters in the html which AwareIM doesn't like.

In answer to your question, I have Mysql 5.1.6 on my production server and 5.6.14 on my Mac testing server

One thought I had was that at some time in the distant past, a user generated an email with some really whacky code in it which is now affecting the entire table. The only real way to test this theory though would be to delete the entire table and regenerate it manually which I'm reluctant to do in my production environment.

_________________

ACDC · Post by **ACDC** » Sun Oct 27, 2013 7:21 pm

Some time back I was able to solve this problem by installing a UTF7 utility jar file in tomcat. It seemed to resolve the problem at the time, maybe it could resolve your problem.

http://jutf7.sourceforge.net/

aware_support · Post by **aware_support** » Sun Oct 27, 2013 10:54 pm

If anyone can reliably reproduce this problem in a test BSV please send this BSV to us with the description how to reproduce the problem

Gabbitas · Post by **Gabbitas** » Sun Oct 27, 2013 11:28 pm

BSV and instructions sent

Gabbitas · Post by **Gabbitas** » Thu Oct 31, 2013 10:29 pm

Support e-mailed me this morning and asked the following questions:

"Are you using MySQL? Can you check what the MySQL encoding on your system is? To do this issue the following SQL command: SHOW VARIABLES; and then check character_set variables."

I haven't had a chance to check this out and get back to them yet but I will this weekend. Perhaps it might assist them further if others could run this check and post their results?

Thanks

Rennur · Post by **Rennur** » Thu Oct 31, 2013 11:35 pm

I'm not getting this error. I've copied + paste the entire Word document page with bullets, tables etc into plain text HTML attribute and it is saved in AIM exactly as it is in Word.

MySQL Encoding: 65001 ( UTF-8 )

You can check encoding in Navicat, right-click on your MySQL Connection > Connection Information.

Gabbitas · Post by **Gabbitas** » Sat Nov 02, 2013 1:58 pm

I have now run a few tests to find out about the char set used.
If I run the SHOW VARIABLES command I get the following:

character_set_client :cp850
character_set_connection :cp850
character_set_database :utf8
character_set_filesystem :binary
character_set_results :cp850
character_set_server :utf84
character_set_system :utf8

If I use navicat as suggested by rennur it shows the encoding as 65001 (UTF-8)[/img]

aware_support · Post by **aware_support** » Sun Nov 03, 2013 10:39 pm

Please try changing all variables to use UTF-8 encoding and see if you are still getting this problem.

Rennur · Post by **Rennur** » Sun Nov 03, 2013 10:48 pm

Gabbitas · Post by **Gabbitas** » Mon Nov 04, 2013 5:54 am

Does anyone know the commands I need to run to make these changes?

I've tried adding the new variables to the my.ini file but it prevents mySQL from starting!

Thanks