13

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
yinjia
  • 804
  • 2
  • 10
  • 20

4 Answers4

10

  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)

XOR
  • 314
  • 5
  • 11
  • This worked in my case. I had to migrate some columns to `utf8` with `utf8_general_ci`. Also bear in mind that the client machine has its own collation. For example, in my case, for a related issue, for a Java application I had to set these flags `-Dfile.encoding="UTF-8" -Dsun.jnu.encoding="UTF-8"`. There were issues in regards to Input and Output streams and these two solutions solved it. – tzortzik May 27 '21 at 17:40
9

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.

yinjia
  • 804
  • 2
  • 10
  • 20
1

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.

Roman Karagodin
  • 740
  • 2
  • 11
  • 16
0

Fir you need to open mysql/my.cnf file with any editor after that in mysqld section enter these values like =

enter code here

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

after this restart your databse mariadb with command

systemctl restart mariadb 
systemctl stop mariadb;
systemctl start mariadb,

after this lgin in databse by mysql -u root -p after login into databse and run command

show variables like "character_set_database";

check chracter_set_database and you saw the value of character databse changes latin1 to utf8mb4 and problem has been solved

Rohit Gautam
  • 1