Characters appear as question marks in MySQL

Question

I have a problem saving unicode characters in MySql.

Initially my database character set was set to latin1 and unicode strings were saves as quotation marks. After doing some research I added the following lines to my.cnf:

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

and executed the query:

ALTER DATABASE <my_database> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

After restarting mysql, I get an error:

"org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Could not execute JDBC batch update; uncategorized SQLException for SQL ... Incorrect string value: '\xD0\xBA\xD1\x81\xD0\xB5...' for column 'first_name' at row 1"

Query mysql> show variables like 'char%'; returns the result:

Variable_name              | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/

and query mysql> show create database <my_database> gives:

| Database  | Create Database                                                       |
+-----------+-----------------------------------------------------------------------+
| my_database | CREATE DATABASE `my_database` /*!40100 DEFAULT CHARACTER SET utf8mb4 */

I know this question was answered many time but I tried everything I found in google and still couldn't fix it. Any help is appreciated!

UPDATE

After querying SHOW CREATE TABLE, I saw that DEFAULT CHARSET of the table was latin1.

I altered table with ALTER TABLE my_table CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

After that each column had CHARACTER SET set to latin1, while DEFAULT CHARSET at the end of the query result was utf8mb4

After altering the column with ALTER TABLE my_table MODIFY my_column VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, I got rid of the error when saving the value, but it went back to saving the string with question marks. Still haven't found the problem

Answer 1

1. Make sure you have the correct encoding while inserting into DB. Add the following to your jdbc URL

   ?useUnicode=yes&characterEncoding=UTF-8
   

   So your final URL will look like this

   jdbc:mysql://HOSTNAME:PORT/DATABASE_NAME?useUnicode=yes&characterEncoding=UTF-8
   

2. Make sure you create the table with utf8 character set and an appropriate collation (like utf8_general_ci)

Answer 2

So 2 days later i was able to figure it out with the help from comment and other posts.

The issue with unicode was on 2 levels: 1) How it was saved in MySql 2) How Freemarker was displaying the value

These are the steps I did that solved the issue:

STEP1: Add the following lines to my.cnf file:

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

STEP2: Run a query

ALTER TABLE my_table MODIFY my_column VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL

for each column that need to accept utf-8. That fixed MySql encoding. Note: altering the whole database or the table didn't seem to work.

STEP 3: Add the following property to ContentNegotiatingViewResolver bean :

<beanclass="org.springframework.web.servlet.view.freemarker.FreeMarkerViewResolver">
        <property name="contentType" value="text/html;charset=UTF-8"/>
    </bean>

Problem solved.

Answer 3

In my case, the problem with replacement of cyrillyc letters by question marks was easily solved by deleting the table and creating it again with adding to the end of a 'CREATE TABLE' statement the following:

DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

For example:

CREATE TABLE IF NOT EXISTS People (id int NOT NULL AUTO_INCREMENT, name varchar(50), gender char(1), birthDate date, profession varchar(50), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

More detaled informaton on encoding issues is provided on the official MySQL website.