Unable to store special character on Google Cloud SQL (MySQL)

user_2797757 0
Viewed 300

I have two MySQL instances: locally and on Google Cloud SQL. The same database is created on both with charset utf8mb4, which can be verified when I run:

show variables like "character_set_database";

Also for both Databases, I import data from the same csv file.

One of the text columns has special characters like this: āŒ› ABC. When my webpage retrieves from the local instance, it can display fine, but when I switch to retrieve data from the Google Cloud instance, I got ?āŒ›? ABC. How do I fix that?

google-cloud-sql mysql
1 Answers

As described in this post In order to convert a DB encoding, you will need to write ALTER commands on the database.

This SQL script can be useful for this

# Convert the DB first
ALTER DATABASE <database_name> 
CHARACTER SET = utf8mb4 
COLLATE = utf8mb4_unicode_ci;
# Convert each table
ALTER TABLE <table_name> CONVERT TO 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;
# Convert each column
# All varchar columns should be converted to stay consistent
ALTER TABLE <table_name>
  CHANGE <varchar_col1> <varchar_col1> VARCHAR(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  CHANGE <varchar_col2> <varchar_col2> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# Repair each table
REPAIR TABLE <table_name>
# Optimize each table
OPTIMIZE TABLE <table_name>

After this update the character_set_server flag in your Cloud SQL instance to utf8mb4

Now restart your Cloud SQL Instance and that's all

Please make a backup of your data before making any changes!

user_115049090
Related