Spring Boot import.sql encoding issue

Question

I am using Spring Boot 1.5.2.RELEASE, and when trying to use the import.sql file to add demo data with Arabic letters as follows:

INSERT INTO city (active, name_arabic,name_english) VALUES (b'1', 'الرياض','Riyadh');

The value is inserted in the database as : Ø§Ù„Ø±ÙŠØ§Ø¶

I tried adding the following to application.properties with no luck:

spring.datasource.connectionProperties=useUnicode=true;characterEncoding=utf-8;autoReconnect=true;useSSL=false
spring.datasource.sql-script-encoding=UTF-8

The database I am using is MySQL and the collation is UTF8_GENERAL_CI.

This issue happens only when I run the project on server, if run a unit test or build the application with Maven the issue doesn't happen.

can you try with executing this ALTER TABLE city CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci ? — Youcef LAIDANI
– Youcef LAIDANI, Commented Jun 25, 2017 at 17:11
Are your database and table set to accept UTF8_GENERAL_CI??? — harshavmb
– harshavmb, Commented Jun 25, 2017 at 17:12
@harshavmb , yes the database and all tables are set to UTF8_GENERAL_CI — Mahmoud Saleh
– Mahmoud Saleh, Commented Jun 25, 2017 at 23:51

Mahmoud Saleh · Accepted Answer · 2017-08-19 12:09:07Z

3

Issue was solved by using the following maven plugin:

        <plugin>
           <groupId>org.apache.maven.plugins</groupId>
           <artifactId>maven-surefire-plugin</artifactId>
           <configuration>
              <argLine>-Dfile.encoding=UTF8</argLine>
           </configuration>
        </plugin>

the above plugin will solve encoding issue for command line build.

UPDATE: to solve the issue when you run project on server:

1- Run As

2- Run Configurations

3- Change the encoding for the server to be UTF-8 as in the image below

edited Aug 19, 2017 at 12:09

answered Jun 26, 2017 at 0:01

Mahmoud Saleh

33.2k123 gold badges354 silver badges512 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anshul Sharma · Accepted Answer · 2017-06-25 17:16:14Z

0

you have to use ?useUnicode=yes&characterEncoding=UTF-8 into db-url.

The error you were getting in your XML was most likely due to using '&' which is not an allowed entity in XML. You should definitely encode the connection, so the work connection url should be something like

<property name="url" value="jdbc:mysql://localhost:3306/DB_Name?useUnicode=true&amp;characterEncoding=UTF-8"/>

You can also make sure that your server is configured properly, for tomcat for instance adding URIEncoding to connector

<connector port="8080" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" URIEncoding="UTF-8"/>

will specify the character encoding used to decode the URI. You should find an equivalent for your server

answered Jun 25, 2017 at 17:16

Anshul Sharma

3,5321 gold badge14 silver badges18 bronze badges

1 Comment

Mahmoud Saleh Over a year ago

i am not using xml, i am using application.properties and the spring.datasource.connectionProperties is equivalent to adding the properties to the db url

Rick James · Accepted Answer · 2017-07-02 19:52:05Z

0

Mojibake. See http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for discussion of what causes it, and what needs changing in your code.

To further verify, do SELECT HEX(...) FROM ...; that string should have hex C398C2A7C399E2809EC398C2B1C399C5A0C398C2A7C398C2B6

If, instead, you get C383CB9CC382C2A7C383E284A2C3A2E282ACC5BEC383CB9CC382C2B1C383E284A2C385C2A0C383CB9CC382C2A7C383CB9CC382C2B6, then you have "double encoding".

answered Jul 2, 2017 at 19:52

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Spring Boot import.sql encoding issue

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related