Japanese character is garbled when we run the query

[LookerExperts]

Knowledge Drop

Last tested: Oct 18, 2020

The Problem

There was an issue that the query which is filtered with Japanese character returned no result even though there is. We realized that Japanese character is garbled when we run the query with Japanese character as the following screenshot. Therefore none of the data matched with the filter value.

Also, they set the global character set as "latin1" and created the database. This character set could be caused the character garbled issue.

A Solution

Note: This is a solution which we solved with Amazon Aurora Database.

1. Check the global character parameter set in RDS console.
   
   A.Check which parameter group is assigned to your database
   B.Check if the following five parameters are set to utf8mb4. If not, change them to utf8mb4.
   •character_set_client
   •character_set_connection
   •character_set_database
   •character_set_results
   •character_set_server
   C.Restart the RDS in order to reflect the new setting.
    
2. Double check with the following command if the global character set is changed.
   
   show global variables like 'character%';
    
3. Check the default character set of the database with the following command. We can see the default character is latin1 in the following screenshot.
   
   SHOW CREATE DATABASE <DATABASE NAME>
   
   
4. Change the database default character set to utf8mb4 with the following command.
   
   ALTER DATABASE <DATABASE NAME> default character set utf8mb4;
    
5. Double check with the following command if the character set is changed in the database.
   
   show variables like 'char%';
    
6. Run "SELECT <Japanese Character>" and check if you can see the Japanese character correctly.
   
   
   
    
7. All set.

This content is subject to limited support.