Problem using html text fields - possible corruption?

nlarson

Rennur wrote
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

I have this issue, I used the stand alone and I am all UTF-8 except for BLobs.

'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'utf8'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'utf8'
'character_set_system', 'utf8'

nlarson

That said, I can not recreate this in the CRM sample app hosted by the Aware Team, so there must be some config option somewhere.

aware_support

Sample applications use Derby, not MySQL, which proves that it is some setting in MySQL. It must be some character set setting...

kklosson

Could this be a client browser issue? I note that Chrome auto detects the encoding (Tools, Encoding). When I use my app site, it detects UTF-8. But maybe people have their encoding set to something else...

I will start polling my users as this arises....

kklosson

Here's an update that may or may not help us understand this issue.

My customer reported the issue. There was already text in the text attribute and whenever he saved, with our without changes, he got the error. Not sure how the text was ever saved since he's the only one in the record. Even when he copied the text to Notepad and back to the text window, he got the error. Then I had him save it in Notepad and use ANSI encoding, then copy to the text field and it saved fine.

Also, I had him send me both files before and after saving with ANSI encoding, and neither gave me any error. I have yet to ever see this error on my computer. There does seem to be something in the local machine.

I also checked his browser encoding which was set to UTF-8. I tried setting it to Western (ISO) but still got the error. Interestingly, ANSI is not an encoding option in Chrome at least.

I hope AwareSoft and this community will work to nail this down and find a solution.

Rennur

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

Did some search on

\xE2\x80\x8B

http://www.marathon-studios.com/unicode/U200B/Zero_Width_Space

Maybe the font is not installed on the local machine or the browser is too old?

kklosson

My guy has Win 7 - probably some home edition, and was using latest Chrome.

aware_support

It is very unlikely to be a client browser issue - it's most likely MySQL encoding issue. So once again whoever is having this problem please check that all MySQL character settings are set to UTF-8. And if they are install the evaluation version of Aware IM with Derby somewhere separately and test if you have this issue. If you don't, there must be some other MySQL setting that affects it.

We cannot reproduce this - on our systems everything works perfectly. So if you want to resolve this issue please perform the steps above and report back.

kklosson

More research. I think that this post comes pretty close to explaining what is happening. It is specific to JDBC connections in MySQL. There may not be an answer in here, but it seems to point to the actual problem. I post this in hope that AwareSoft may find it a useful pointer to an ultimate solution.

The one thing that perplexes me is that two different users can get two different outcomes. In my app, the design is that one person enters one text attribute and another user enters a different text attribute on the same business object. The first user enters a load of text into one attribute and it saves fine. Then the second user cannot save the record at all due to receipt of this error caused by the text initially entered by the first user. And while some of my users experience this problem, I have never experienced it - same as AwareSoft Support. There's a story in there somewhere.

Here's the link:
http://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc

Rennur

It seems like everything points to a MySQL problem. AwareIM connects to MySQL via Connector/J with within itself has countless releases & bug fixes.

http://dev.mysql.com/doc/relnotes/connector-j/en/

If you click on the 'About' button on the server control panel you'll notice that Aware uses Conncector/J 3.1.12 and the latest release is 5.1.27. The changes & bug fixes between the two versions are significant.

I've been using v 5.1.x + for a quite a while without a single issue.
I don't know if this would be the cause of the issue but naturally you'd want to have the latest working versions available.

To use the latest version:

1. Download platform independent version 5.1.xx from the MySQL website
1. Extract the files and rename mysql-connector-java-5.1.27-bin.jar to mysql-connector-java-3.1.12-bin.jar
2. Copy and replace the existing file in C:\AwareIM\Tomcat\lib
3. Restart AwareIM server

Aware will now use the latest version of Connector/J to communicate with MySQL.
Like I've said, I've been doing this for a while without any problems but perhaps Support can give us their opinions/thoughts on this.

Cheers

aware_support

Well, if you believe the post from Stack Overflow site, then the problem has nothing to do with Connector J or JDBC, but again with MySQL settings - either a special UTF-8 (UTF-8 mdb something...) needs to be set in MySQL or UTF-8 has to be set on a particular table individually where this problem happens.

kklosson

Thank you. This is encouraging. It gives the community something to try and provides AwareSoft with a potential option for integrating into future releases.

Gabbitas

I went ahead as per supports suggestion to change all character sets to utf8. This didn't make any difference and I was still getting the same error when I copied and paste a bullet list from ms word into Aware.
Support also asked to install Aware with Derby database; I have been unable to run a test on this as all of my machines have already got an expired evaluation version installed and I don't want re-install Aware with Derby on my production machine.

I followed the following link posted by kklosson:

http://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc

I then changed character_set_server to utf8mb4 and also character_set_filesystem to utf8mb4
This has now stopped the error from appearing and the characters that would throw the error are now replaced with a '?' instead. This is an improvement but still not the ideal so thanks for your suggestion kklosson!

Next I changed the character_set_server and also character_set_filesystem back to utf8 and proceeded to follow the post by Rennur.

In short it seems that changing the mysql-connector-java to the latest version solves the problem 🙂 I can now copy from ms word and the text and special characters are saved and retrieved exactly as you would expect. That is to say that I save as bullet points and they are re-displayed as bullet points and no errors are thrown.

I really hope that this is the fix to this problem and if it is, great work Rennur, thank you. Perhaps others who are getting this problem could try this solution and report back their findings.

Thanks

Rennur

I can now copy from ms word and the text and special characters are saved and retrieved exactly as you would expect. That is to say that I save as bullet points and they are re-displayed as bullet points and no errors are thrown.

Same here.

Nlarson & rocketman, I'm curious to know if this something you are willing to test on your systems.

Cheers

nlarson

Busy schedule lately, but i will definately give this a try as soon as i get time. Nothing worse than telling users not to copy and paste. :/

kklosson

1. Download platform independent version 5.1.xx from the MySQL website

Extract the files and rename mysql-connector-java-5.1.27-bin.jar to mysql-connector-java-3.1.12-bin.jar

Copy and replace the existing file in C:\AwareIM\Tomcat\lib

Restart AwareIM server

I have instituted this modification which was quite easy. I've not been able to get a definitive test, but I've not had a single instance of this error reported.

Rennur

Hi kklosson,

I've not been able to get a definitive test, but I've not had a single instance of this error reported.

That's a good sign. So you're saying you've had the new version running for a few days now & no one has reported the error?

Also, I had him send me both files before and after saving with ANSI encoding, and neither gave me any error. I have yet to ever see this error on my computer. There does seem to be something in the local machine.

Have you got in touch the user that was experiencing this issue by any chance?

kklosson

Have not been able to but I have a use in close contact who has experienced this and have asked him to bang hard on it. Will certainly keep all apprised.

nlarson

Solved! Many thanks again Ren!

kklosson

Does that mean you did as I did?

« Previous Page Next Page »