Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – It seems that the characters are getting corrupted when stored in the database

I’m working on a project with Spring Boot and MySQL, and I’ve been storing data without any issues. However, in one specific table, data suddenly starts saving with encoding errors only for Korean text. It used to save Korean characters just fine. I’ve double-checked that the MySQL character set is correct, and I’ve even recreated the table, but the issue persists.

1,ãããããã,ãã,1729758442950_스크린샷 2024-08-27 211441.png,1

2,ãããããã,ãã,1729759803302_스크린샷 2024-08-27 211441.png,1

+--------------------------+---------------------------------------------------------+
| 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     | utf8mb3                                                 |
| character_sets_dir       | C:Program FilesMySQLMySQL Server 8.0sharecharsets |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+

SHOW CREATE TABLE

 self_intro | CREATE TABLE `self_intro` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` varchar(1500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `image_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKc409uoed65kpyxaq026593lp4` (`user_id`),
  CONSTRAINT `FKc409uoed65kpyxaq026593lp4` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

SELECT HEX(col)

+------------------------------+--------------------------+
| title_hex                    | content_hex              |
+------------------------------+--------------------------+
| C3ACC2A0C29CC3ABC2B0C29C2F2F | C3A3C285C287C3A3C285C287 |
+------------------------------+--------------------------+
1 row in set (0.01 sec)

project code server

public void createSelfIntroduction(SelfIntroDto selfIntroDto, String userEmail, MultipartFile file) throws IOException {
        User user = userRepository.findByEmail(userEmail) // email
                .orElseThrow(() -> new RuntimeException("User not found with email: " + userEmail));

        String imageUrl = saveFile(file); // file

        SelfIntro selfIntroduction = new SelfIntro();
        selfIntroduction.setTitle(selfIntroDto.getTitle());
        selfIntroduction.setContent(selfIntroDto.getContent());
        selfIntroduction.setImageUrl(imageUrl); // save url
        selfIntroduction.setUser(user);
        System.out.println(selfIntroDto.getTitle());

        selfIntroRepository.save(selfIntroduction);
    }

controller

 @PostMapping(consumes = "multipart/form-data")
    @Secured("ROLE_STUDENT")
    public ResponseEntity<String> createSelfIntroduction(
            @RequestPart(value = "title") String title,
            @RequestPart(value = "content") String content,
            @RequestPart(value = "file", required = false) MultipartFile file,
            HttpServletRequest request) {
        try {
            String userEmail = extractUserEmailFromJwt(request);
            SelfIntroDto selfIntroDto = new SelfIntroDto();
            selfIntroDto.setTitle(title);
            selfIntroDto.setContent(content);

            User user = userRepository.findByEmail(userEmail)
                    .orElseThrow(() -> new RuntimeException("User not found with email: " + userEmail));
            selfIntroDto.setUserName(user.getName());
            selfIntroDto.setUserSchool(user.getSchool());

            selfIntroService.createSelfIntroduction(selfIntroDto, userEmail, file);
            return ResponseEntity.ok("Self Introduction created successfully");
        } catch (Exception e) {
            return ResponseEntity.status(HttpStatus.FORBIDDEN).body("Failed to create self introduction: " + e.getMessage());
        }
    }

Why might this be happening?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Resolved by adding WebMvcConfig code.

    @Configuration

public class WebMvcConfig implements WebMvcConfigurer {
@Override
public void configureContentNegotiation(ContentNegotiationConfigurer configurer) {
    configurer.defaultContentType(MediaType.APPLICATION_JSON, MediaType.TEXT_PLAIN)
              .mediaType("json", MediaType.APPLICATION_JSON)
              .mediaType("text", MediaType.TEXT_PLAIN)
              .parameterName("mediaType")
              .favorPathExtension(true);
}

@Override
public void configureMessageConverters(List<HttpMessageConverter<?>> converters) {
    converters.add(new StringHttpMessageConverter(StandardCharsets.UTF_8));
    converters.add(new MappingJackson2HttpMessageConverter());
}
}

  2. 0

    I guess, it occur encoding issue for non-latin characters like ãããããã,ãã,1729758442950_스크린샷 2024-08-27 211441.png. UTF-8 is mostly supported for 4-byte characters and emogis and also the non-latin characters. When Spring Boot makes transactions with MySQL dbs character encodings happen. At that time, Spring Boot is not exactly set to use UTF-8, and MySQL also not to use UTF-8mb4, then they do not fully support to handle multibyte characters, then in your scenario, the additional configs need to handle UTF-8.

    Then you can try:

    1. Config Spring Boot to use UTF-8 Encoding.

    You can specify the UTF-8 in the application.properties or

    application.ynl file options as,
spring.http.encoding.charset=UTF-8
spring.http.encoding.enabled=true
spring.http.encoding.force=true
    1. Set the MySQL DB to use UTF-8mb4.

    In your db, you need to config the collation as UTF-8mb4.

    ALTER DATABASE your_database CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search