Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm running into a problem that seems to be an issue w/ mysql 5.1s limitations on UTF8 characters.

From http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-utf8.html

Currently, MySQL support for UTF-8 does not include 4-byte sequences.

Our app is throwing periodic exceptions like

java.sql.SQLException: Incorrect string value: '\xF4\x80\x82\x8C  ...'

The ultimate solution looks to be migrating to 5.5 which supports UTF-8 > 3 bytes

Unfortunately this wouldn't be as simple as bouncing the instances under newer binaries. Being a major version dump we'd need to do a full dump reload which will require some scheduled downtime.

Has anyone else had to deal this situation before? Are there any good work arounds?

The naive approach seems to be have the app search and replace multi-byte sequences with question marks or � . This seems pretty hacky and not a very palatable option to me or the developers.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

Opinion

It's probably better to throw an exception during your applications input checking and not pass the buck to the database.

Workaround

There is a "workaround" but your mileage may vary: http://forge.mysql.com/worklog/task.php?id=3780

Brute Force?

You could convert your front end table VARCHAR field to a BLOB and store as binary data to cure the current problem. ...and of course that invites a host of other problems by using BLOBs instead of VARCHAR.

Upgrading

UTF32 may solve the problem and upgrading to 5.5.x isn't has hard as you'd think. Create a replicated slave (farm), sync up and promote it to master.

share|improve this answer
1  
Somewhat thankfully, upper powers that be had independently arrived at your same opinion so the handling and more application side logging will be put in place for a bit. –  atxdba Jul 12 '12 at 3:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.