1

In my local environment it works fine, in my production environment, it doesn't.

I've been reading a lot of answers on the internet. It seems I didn't get the answer I'm looking for.

I can read Chinese characters from the database. When I write Chinese characters to the database they turn up like '???'. When I query the database to find a Chinese entry in the database, the result set is empty.

I use Java for development, jdk 1.6. MySQL server: 5.0.91

My web application has utf-8 in the header of the web pages. In my servlets I set character encoding to utf-8. My tables and the columns in my tables use utf8_general_ci.

I added ?useUnicode=yes&characterEncoding=UTF-8 to the URL of my database. I added SET NAMES 'UTF8' COLLATE 'utf8_general_ci' before I do a query.

Character_set_connection = utf8

I noticed that on the production server, the character_set_server = latin1. However, when I changed character_set_server in my local environment to latin1, I could still insert Chinese characters.

In my production environment, they tell me that others don't have any problem.

I think I pretty much covered what has been suggested by many. Anyone any idea as to what else could be the problem?

Update: quick temporary fix for those who need it, in the absence of a good solution.

To insert the variable name in the database:

Create a new column name_as_bytes using datatype varbinary()

Convert name to a bytes array
byte[] nameAsBytes = name.getBytes("UTF-8");

Insert nameAsBytes in name_as_bytes in the database

Copy the name_as_bytes column to the name column, using convert() function

update myTable set name = CONVERT(nameAsBytes USING utf8) where ID = id;

With the extra query being performed, and the extra column added, this is not an ideal solution, but it helps to get your website going while looking for a solution.

user20850
  • 11
  • 4
  • 1
    Did you try to debug it step by step and check if the string you send to the database has the proper chinese charcter? If you pass the strings around and use them in streams etc the streams must also be made with UTF8 and if you miss that sometimes during manipulation you lose the characters – Veselin Davidov Oct 19 '17 at 12:52
  • When I debug step my step in my local environment, the strings all show up with proper Chinese characters, and they get inserted in the database as Chinese characters. – user20850 Oct 20 '17 at 13:31
  • I don't know how to debug step by step in my production environment though, I don't have my own server. I used logback to write the strings to an output file just before I insert them in the database, and they show up fine in the output file. To make sure I am sending utf-8 encoded strings I tried something like: String nameAsUtf8 = new String(name.getBytes("UTF-8"), Charset.UTF-8), where name is the string that I want to insert in the database. No luck with that either. – user20850 Oct 20 '17 at 13:44

1 Answers1

1

I finally solved this problem by two operations.
1. to do with database, create table/database with character set such as CREATE DATABASE name CHARACTER SET 'utf8mb4';
2. to do with JDBC, add characterEncoding and useUnicode properties after the connection url such as jdbc:mysql://ip/dbname?useUnicode=yes&characterEncoding=UTF-8

P.S. using utf8mb4 rather than utf8 because the latter one support less chinese characters.

SEN XU
  • 11
  • 1