Problem using html text fields - possible corruption?

rocketman

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

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

rocketman

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

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

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

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

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

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

BSV and instructions sent

Gabbitas

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

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

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

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

Rennur

These are my mysql settings:

Gabbitas

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

rocketman

Mine are all utf8.

Can we have a definitive answer from support as to what these should be set to before I go making changes.

And does anyone know what the effect will be if these are switched on a live database?

rocketman

Found this link which might be useful

http://dba.stackexchange.com/questions/29649/why-default-character-set-server-is-latin1

Why some defaults use utf8 but some default use latin1 ?
This would depend on the OS version of the MySQL Binary. Windows versions may have latin1 while Linux Versions may use utf8.

aware_support

You need to issue SET command:

SET variable_name = value;

see this:
http://dev.mysql.com/doc/refman/5.0/en/set-statement.html

Rennur

MySql Character Set Support

http://dev.mysql.com/doc/refman/5.5/en/charset.html

IMO MySQL should not installed via WAMP server.

There should not be any problems with a default standalone installation of MySQL 5.5 downloaded direct.
http://dev.mysql.com/downloads/mysql/5.5.html#downloads

aware_support

All character sets must be set to UTF-8