MySQL Collation Connection_Google Cloud SQL Proxy

[john_hannah]

My company recently upgraded to a MySQL8 cloud SQL database. We wanted to make it compatible with 5.7 as well, so we are using utf8mb4_unicode_ci as the collation for the db. We set our collation_connection to match as well. We also have used the skip client handshake flag as well. 

When I connect through the CLI and query the collation, it reflects utf8mb4_unicode_ci, as it takes the server default collation. When we connect through the proxy, it also takes MySQL8 default (utf8mb4_0900_ai_ci), and I am unsure why it doesn't take the default since we ignore the client handshake. 

[ms4446]

The Cloud SQL Proxy does not manage MySQL configuration settings, including collation settings. The collation_connection parameter is a system variable within MySQL, and it cannot be set within the Cloud SQL Proxy configuration because the proxy does not have a configuration file for MySQL settings.

In Google Cloud SQL, you do not have direct access to the MySQL configuration files. The settings are managed by Google Cloud, and you can only change certain settings via the Google Cloud Console or the gcloud command-line tool.

To ensure that your connections through the Cloud SQL Proxy use the utf8mb4_unicode_ci collation, you will need to set this collation for your database within the Google Cloud Console or by using the gcloud command-line tool.

Here's how you can set the character set and collation for a database in Google Cloud SQL:

Using the Google Cloud Console:

1. Go to the Cloud SQL Instances page in the Cloud Console.
2. Click on the SQL instance name to open the Instance details page.
3. Click on the DATABASES tab.
4. To create a new database with a specific character set and collation:
   • Click on the CREATE DATABASE button.
   • Enter the Database name.
   • Choose the Character set utf8mb4 and Collation utf8mb4_unicode_ci.
   • Click Create.
5. To modify an existing database:
   • Click on the database name.
   • Click on the EDIT button.
   • Choose the Character set utf8mb4 and Collation utf8mb4_unicode_ci.
   • Click Save.

Using the gcloud Command-Line Tool:

1. • Run the following command:

     To create a new database with a specific character set and collation:

   gcloud sql databases create [DATABASE_NAME] --instance=[INSTANCE_NAME] --charset=utf8mb4 --collation=utf8mb4_unicode_ci
   

   • Run the following command:

     To modify an existing database:

gcloud sql databases patch [DATABASE_NAME] --instance=[INSTANCE_NAME] --charset=utf8mb4 --collation=utf8mb4_unicode_ci

After setting the character set and collation for the database, ensure that your application's database connection settings are also correctly configured to use utf8mb4 and utf8mb4_unicode_ci.

If you are connecting to the database using a client library or a database management tool, you can execute the SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'; command after establishing the connection to ensure that the session uses the correct character set and collation.

[john_hannah]

So the database is currently set to utf8mb4 and utf8mb4_unicode_ci for collation. If you connect through the Google Cloud Console, the collation_connection is set to utf8mb4_unicode_ci; however, if you connect to the CloudSQL instance through the proxy, it still shows the utf8mb4_0900_ai_ci. 

[ms4446]

Yes, that is correct. When you connect to a Cloud SQL instance through the proxy, the proxy forwards the client handshake to the database instance. This means that the database instance can use the information in the handshake to determine the client's preferred character set and collation.

If the client handshake specifies a different character set or collation than the database instance is configured to use, the database instance will use the client's preferred character set and collation. This is why you are seeing the utf8mb4_0900_ai_ci collation when you connect through the proxy, even though the database is configured to use the utf8mb4_unicode_ci collation.

To ensure that all connections to the database use the utf8mb4_unicode_ci collation, you can configure your database client to explicitly set the collation for each connection.

After connecting to the database through the Cloud SQL Proxy, execute the following SQL command:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

This command should be executed each time a new connection is established through the proxy.

It sets the character set and collation for the current connection to utf8mb4 and utf8mb4_unicode_ci, respectively.

By ensuring that the collation is explicitly set for each connection, you can avoid issues related to the default collation used by the Cloud SQL Proxy and ensure that all connections to the database use the utf8mb4_unicode_ci collation.